Categories
Databases

Creating a SQL Server backup plan

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

Categories
Databases

Bulks inserts and SSIS

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.

The final SSIS package
Categories
Databases

Truncating a Database Table with a Foreign Key

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);

 

Categories
Databases

Kill All Active Database Connections

USE Master

ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE 

ALTER DATABASE YourDatabase SET MULTI_USER

See Kill All Active Connections To A Database

Categories
Databases

MS SQL and MD5

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'