SQL SERVER – Restore Database Backup using SQL Script (T-SQL)

In this blog post we are going to learn how to restore database backup using T-SQL script. We have already database which we will use to take a backup first and right after that we will use it to restore to the server. Taking backup is an easy thing, but I have seen many times when a user tries to restore the database, it throws an error.

Step 1: Retrieve the logical file name of the database from backup.

RESTORE FILELISTONLY
FROM DISK = 'D:\BackUp\YourBaackUpFile.bak'
GO

Step 2: Use the values in the LogicalName Column in following Step.

----Make Database to single user Mode
ALTER DATABASE YourDB
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

----Restore Database
RESTORE DATABASE YourDB
FROM DISK = 'D:\BackUp\YourBaackUpFile.bak'
WITH MOVE 'YourMDFLogicalName' TO 'D:\Data\YourMDFFile.mdf',
MOVE 'YourLDFLogicalName' TO 'D:\Data\YourLDFFile.ldf'

/*If there is no error in statement before database will be in multiuser
mode.
If error occurs please execute following command it will convert
database in multi user.*/
ALTER DATABASE YourDB SET MULTI_USER
GO

Watch a 60 second video on this subject

Look inside the backup before you restore it

FILELISTONLY in step 1 shows you the files. There is a companion command that shows you what the backup actually is, and it takes a second.

RESTORE HEADERONLY
FROM DISK = 'D:\BackUp\YourBaackUpFile.bak';

Check DatabaseName, BackupFinishDate and BackupType. I have watched people restore last month’s backup over this month’s data because nobody looked at the date first. Ten seconds here saves a very bad afternoon.

The errors people actually hit

Error 3154, the backup set holds a backup of a database other than the existing database. You are restoring over a database that already exists and came from somewhere else. Add WITH REPLACE if you are sure. I wrote this one up separately in FIX: Error 3154.

Exclusive access could not be obtained. Somebody is connected to the database. That is exactly what the SINGLE_USER line in step 2 is for. If it still hangs, the ALTER is queueing behind an open transaction, so find the session first.

Directory lookup failed. The paths inside the backup do not exist on this server. That is what MOVE is for, and it is why step 1 comes first.

The database is in use, and SINGLE_USER will not take. Set it back to multi user, kick the connections, then try again. Never leave a database stuck in single user mode, because the one connection it allows might not be yours next time.

Restoring a chain of backups

The script above restores one full backup and opens the database. That is the whole job if a full backup is all you have. If you also have differential or log backups and want the data right up to the failure, every restore except the last one needs NORECOVERY.

RESTORE DATABASE YourDB
FROM DISK = 'D:\BackUp\Full.bak'
WITH NORECOVERY, REPLACE;

RESTORE DATABASE YourDB
FROM DISK = 'D:\BackUp\Diff.bak'
WITH NORECOVERY;

RESTORE LOG YourDB
FROM DISK = 'D:\BackUp\Log1.trn'
WITH RECOVERY;

NORECOVERY means stay closed, more is coming. RECOVERY on the last one means we are done, open the database. Get this the wrong way round and the database opens too early, and you have to start the whole restore again from the full backup.

Two small things that help

Add STATS = 10 to a large restore and SQL Server prints progress every ten percent, so you know it is working rather than stuck.

RESTORE DATABASE YourDB
FROM DISK = 'D:\BackUp\YourBaackUpFile.bak'
WITH MOVE 'YourMDFLogicalName' TO 'D:\Data\YourMDFFile.mdf',
     MOVE 'YourLDFLogicalName' TO 'D:\Data\YourLDFFile.ldf',
     STATS = 10;

And when you are restoring a production backup onto a test server, restore it under a different database name. Nothing gets overwritten, and you can throw it away when you are finished.

Let me know what you think of this blog post and if you use the T-SQL scripts displayed in this blog post, just let me know if it requires any improvement.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.