Simple restore using T-SQL

Restoring your database is simple using T-SQL.

I begin by using ‘RESTORE FILELISTONLY’ to get information about the files contained in the backup set. It returns a result set with information like ‘LogicalName’, ‘PhysicalName’, ‘FileGroupName’ and ‘Size’ (in 8K pages), among other things. You’ll use this information if you’re renaming or moving the .MDF or .LDF files contained in the backup set:

RESTORE FILELISTONLY
FROM DISK = 'D:\MSSQL10_50.SANDBOX_2K8R2\MSSQL\BACKUP\TestDB\TestDB.bak'

The output will be something like this:

To restore a backup over the existing database, use:

RESTORE DATABASE [TestDB]
-- location and name of the backup set file
FROM DISK = 'D:\MSSQL10_50.SANDBOX_2K8R2\MSSQL\BACKUP\TestDB\TestDB.bak' 
WITH RECOVERY,
REPLACE

The REPLACE parameter instructs the engine to overwrite the existing data and log files. Use this cautiously. If the REPLACE command is not used and the database has changes later than what is stored in the backup set, you’ll receive an error:

“Msg 3159, Level 16, State 1, Line 1
The tail of the log for the database “TestDB” has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.”

If the data and log directory are different from the backup set, use:

RESTORE DATABASE [TestDB]
-- location and name of the backup set file
FROM DISK = 'D:\MSSQL.1\MSSQL\BACKUP\TestDB\TestDB.bak' 
WITH RECOVERY,
-- location and name of the data file
MOVE 'TestDB' to 'D:\MSSQL.1\MSSQL\TestDB.mdf', 
-- location and name of the log file
MOVE 'TestDB_log' to 'D:\MSSQL.1\MSSQL\TestDB_log.ldf'

For the above example, I’ve copied a .bak file from my named instance server SANDBOX_2K8R2 to a default instance server. The location of the SQL Server data directory is different on the two servers, so we need to use the MOVE parameter with the RESTORE command.

Be careful with the naming of the data and log files. Although the extensions have no meaning to the SQL Server engine, the convention is to use .mdf for the master data file (and .ndf for any others) and .ldf for the log files. Any other convention could get confusing.