This is something that is generally out of my area of responsibility but has to be done every now and then: creating and configuring a maintenance backup plan for a SQL database server.
This post from Kyle Laffoon outlines the steps needed to create a plan that executes full and differential backups, as well as looking after transaction logs and cleaning up after itself.
Create a maintenance backup plan in SQL Server 2008 R2 using the wizard
SQL Server Integration Services (SSIS) really isn’t my thing. I like to get my hands dirty with code. But every now and then (not often enough for me to remember anything of what I’ve previously done) I have to bite the bullet and open an SSIS package in Visual Studio.
Today has been one of those days.
The goal of the package was simple: to import the contents of two CSV files from a network share into two database tables on a SQL Server. After a day of struggling to reason why I was getting various “Access is denied” and “The file … does not exist” error messages, I finally discovered this blog post that helped to clear the mist.
Bingo! I now have a package with two Sequence Containers with green execution results.
To remove all data from a table and reset its primary key you can normally run the following SQL command:
TRUNCATE TABLE MyTable;
However, if you try this on a table with one or more foreign keys, you’re going to get an error message. Normally, the way around this is to drop the constraints, truncate the table, and recreate the constraints – which is a lot of fuss.
The following does the same job, but without the fuss.
DELETE FROM MyTable; DBCC CHECKIDENT (MyTable, RESEED, 0);
USE Master ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE ALTER DATABASE YourDatabase SET MULTI_USER
SQL Server doesn’t appear to have a nice MD5 function like MySQL does (shame), but there is still a way to do it.
DECLARE @value VARCHAR(255)
SET @value = "This is an MD5 encoded string'
SELECT SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5', @value)), 3, 32)
This was useful for resetting the admin password within an instance of Moodle that had recently been migrated to SQL Server.
UPDATE [moodle].[dbo].[mdl_user] SET [password] = SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5', 'newpassword')), 3, 32) WHERE [username] = 'admin'