How to restore a database when you have a SQL Server backup file?

Oftentimes, when I get a database backup file, I usually perform a quick database restore without any hesitation. This usually works fine, but sometimes I get an annoying error message without any clue. Actually, SQL Server provides some commands that can do some basic check against the backup file before restoring. Here are some steps I usually do and it helps a lot, so I would like to share with you all.

1. First of all, I would like to check whether it is a valid backup via running RESTORE VERIFYONLY command.

RESTORE VERIFYONLY
FROM DISK= N’D:\Workspace\Wordpress\2011-11-22\AdventureWorks2008R2_Full.bak’;
GO
If the verify succeeds, it will show “The backup set on file 1 is valid.” which indicates the backup file is valid and we can perform the actual database restore later. If any error generated, it may states it is not a actual SQL Server database backup file or the file was corrupted and we should not go any further.

2. After verify check process and it is a valid backup file.  Now, I would like to get the header of the backup file via running RESTORE HEADERONLY T-SQL command. Don’t take a file with .bak extension as database backup and .trn extension as transactional-log backup for granted. Actually, file extension is really no matter, we can use any file extension for SQL Server backups, or even any file on Windows OS. Therefore, I highly recommend check it use RESTORE HEADERONLY:

RESTORE HEADERONLY
FROM DISK= N’D:\Workspace\Wordpress\2011-11-22\AdventureWorks2008R2_Full.bak’;
GO

There are lots of useful backup/database properties in the result, and here are some interesting columns

RESTOREHEADERONLY

  • BackupType: indicates what kind of backup in the backup file. 1 = FULL database backup, 2 = T-Log backup, 5 = differential backup.
  • Compressed: indicates whether the backup is compressed or not when performing backup, 1 = Compressed, 0 = Not compressed.
  • Database Version: indicates the SQL Server version where the original database exists and backed up. 661 = SQL Server 2008 R2, 655 = SQL Server 2008, 611/612 = SQL Server 2005, 539 = SQL Server 2000, 515 = SQL Server 7.0.
  • Compatibility Level: indicates the database compatibility when the backup was performed. The compatibility has no indirect relation with database version, a SQL Server 2008 R2 (version 661) can has 100, 90, 80 as compatibility level.
  • SoftwareVersionMajor.SoftwareVersionMinor.SoftwareVersionBuild: indicates the build # of the SQL Server instance where original database exists. For example, 10.50.2500 is the build # of SQL Server 2008 R2 SP1. Please check a very detailed reference at here.
  • IsCopyOnly: indicates whether CopyOnly option used during the database backup. Use the CopyOnly won’t interrupt the existing backup/restore sequences. It is recommended to use this option if you want to get a test copy of the database.
  • RecoveryModel: indicates the recovery model of the original database when the backup was performed, will be one of SIMPLE, BULK-LOGGED, FULL.

3. If the above 1 and 2 are passed, I am confident to know that I can perform the actual database restore. The restore type depends on the backup type displayed in the step 2. At this point, I would like to get the file list contained in the backup using RESTORE FILELISTONLY command and then perform the last restore process.

RESTORE FILELISTONLY
FROM DISK= N’D:\Workspace\Wordpress\2011-11-22\AdventureWorks2008R2_Full.bak’;
GO

In this test, I get the following:

RESTOREFILELISTONLY

So, I can see how may files in the backup, and when I restore the database I can use appropriate MOVE clause to move each logical file to existing folder. Although, the MOVE clause is optimal when full database restore, the corresponding to its original file location display in the above screenshot. However, if the corresponding file location is not exists, or the permission is not properly configured, you may get errors. Therefore, I would recommend using MOVE clause when do this. For more information about restore locaton, you may check another blog post Where the database data and log files will be restored?.

That’s it, hope it helps.

Advertisements

About Alex Feng

SQL Server DBA in Alibaba Cloud Computing
This entry was posted in Backup & Restore, Database Engine and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s