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

VS CodeWeb Dev

Select multiple lines in VS Code

How to select multiple lines to edit at once within VS Code

VS Code Web Dev

Move a line up or down with VS Code

A quick tip on moving lines up or down a file using your keyboard in VS Code