Importing a backup file from another MS SQL machine

How to safely import a backup file with no loss of data

Today I had to do an import from a .BAK file created on a completely different machine than what I had to restore it onto.  It took me more than 5 minutes to find the complete solution online so I will post the code I ran here in the hope that someone else benefits from it.

ALTER DATABASE yourDatabase SET single_user WITH ROLLBACK IMMEDIATE
GO
USE master
GO
RESTORE FILELISTONLY
FROM DISK = N'c:\path\to\backup.BAK'
RESTORE DATABASE yourDatabase
FROM DISK = N'c:\path\to\backup.BAK'
WITH RECOVERY,
MOVE 'yourDatabase_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\data\yourDatabase_Data.mdf',
MOVE 'yourDatabase_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\data\yourDatabase_Log.ldf', REPLACE
GO
ALTER DATABASE yourDatabase SET MULTI_USER
GO

What this does is first of all kicks everyone off your database, if it is in use the restore script will not run correctly. Then it runs the update, since the location of the files will have changes you need those MOVE commands in there. Finally it sets you database back to being multi user again so people can connect and use your updated tables.

Recent posts View all

Productivity

Prolonging your laptop's battery

Some short and long term solutions to extending your laptop's battery life

Productivity

Working when travelling by plane/train

Some tips and tricks for maximising working whilst travelling