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

Freelancing

Why we have ads

We run ads on the site, this article talks about how we try and make them not terrible and why we monetise in the first place

Accessibility

Writing accessible hashtags

Write accessible hashtags by camel casing them. Because ThisReadsBetter thanthisdoes.