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

Web DevProductivity

Keeping on top of website updates

Learn what website updates are, what they entail, why they are important, and how we can help

Freelancing

Getting the most out of your agency

Here are some tips based on years of working with, for, and as an agency on how to get the most out of any work you do.