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

SEO

Google follows URLs in text

Today I learned that Google follows URLs even when they are plain text

Web Dev

Check in with your database

It pays to take a step back and look at how your database is set up every so often. You will often find quick wins and hidden tech debt.