|
See Also: Main_Page - Database Administration - Backup & Recovery
Restoring Full and Differential Database Backups with Transact-SQL
Recall that a database may not be used while it is being restored. If you attempt restoring any kind of backup (full, differential or transaction log) and the database is in use you will receive an error:
Server: Msg 3101, Level 16, State 1, Line 1 Exclusive access could not be obtained because the database is in use. Server: Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.
| Option | Explanation | | { database_name | @database_name_var } | Database name to be restored | | < backup_device > [ ,...n ] | Backup device from which database is to be restored | | RESTRICTED_USER | Only db_owner, dbcreator, and sysadmins may have access to the newly restored database. | | FILE = { file_number | @file_number } | Identifies the backup set number on the media that database is to be restored from | | PASSWORD = { password | @password_variable } | Password must be specified if one was supplied when backup was taken | | MEDIANAME = { media_name | @media_name_variable } | If provided media name must match the media name on the backup device | | MEDIAPASSWORD = { mediapassword | @mediapassword_variable } | If password was set for media set then MEDIAPASSWORD must be supplied and must match the original password. | | MOVE 'logical_file_name' TO 'operating_system_file_name'[ ,...n ] | Allows you to restore the files to a different path and file name than in the backup | | KEEP_REPLICATION | Keeps replication settings on a restored database. Used for restoring a database that is published and is log-shipped. This option does not allow restoring without recovery | NORECOVERY | RECOVERY |
STANDBY = undo_file_name | NORECOVERY does not recover the database. When this option is used database is not operational until further backups are restored using RECOVERY option. RECOVERY option specifies that no further differential or transaction log backups are to be applied to this database. After recovery is complete database becomes operational. STADBY option leaves database in read-only mode while further transaction log backups are restored. Undo file name is required for undoing recovery effects. SQL Server overwrites the file if it already exists. If NORECOVERY | RECOVERY | STANDBY option is omitted default is RECOVERY | | NOREWIND | REWIND | NOREWIND prevents all applications from using the tape until SQL Server issues BACKUP or RESTORE command with REWIND. Therefore SQL Server retains the control of the tape. REWIND advises SQL Server to rewind the tape and release control of the tape These options are only used for tape devices | | NOUNLOAD | UNLOAD | UNLOAD ejects the tape when restore is complete. NOUNLOAD does not eject the tape. These options are only used for tape devices | | REPLACE | Overwrites the existing database that has the same name as specified in RESTORE DATABASE statement. If REPLACE is not specified then RESTORE command checks for existence of the database prior to restoring it. | | RESTART | Restarts RESTORE operation for restores from tape that span multiple tape volumes | | STATS [ = percentage ] | Returns progress percentage of RESTORE operation. |
For example, the following query restores pubs database and makes it operational (WITH RECOVERY):
RESTORE DATABASE pubs FROM DISK = 'e:\pubs.dat' WITH RECOVERY
Output will look similar to the following:
Processed 240 pages for database 'pubs', file 'pubs' on file 1. Processed 16 pages for database 'pubs', file 'pubs2' on file 1. Processed 1 pages for database 'pubs', file 'pubs_log' on file 1. RESTORE DATABASE successfully processed 257 pages in 0.540 seconds (3.885 MB/sec).
The following statement will overwrite the existing database with the database of same name contained in the backup set:
RESTORE DATABASE pubs FROM DISK = 'e:\pubs.dat' WITH RECOVERY, REPLACE
We can also restore a database from a backup located on a network share. The following statement restores pubs from a network:
RESTORE DATABASE pubs FROM DISK = '\\remote_server\share_name\pubs.bak' WITH REPLACE, STATS = 20
You can allow additional differential and transaction log backups to be applied to the database by executing the following command:
RESTORE DATABASE pubs FROM DISK = 'e:\pubs.dat' WITH NORECOVERY
At this point no one can access the database for read-write purposes.
Later, if you change your mind about applying other backups you can recover the database using the following:
RESTORE DATABASE pubs WITH RECOVERY
At this point database becomes operational.
Suppose your original database has files on E:\ drive but you wish to restore it on a different drive at the destination server. You can utilize the WITH MOVE command to accomplish this as follows:
RESTORE DATABASE pubs FROM DISK = 'e:\pubs.bak' WITH MOVE 'pubs' TO 'c:\pubs.mdf', MOVE 'pubs2' TO 'c:\pubs2.ndf', MOVE 'pubs_log' TO 'c:\pubs_log.ldf'
Output will look similar to the following:
Processed 240 pages for database 'pubs', file 'pubs' on file 1. Processed 16 pages for database 'pubs', file 'pubs2' on file 1. Processed 1 pages for database 'pubs', file 'pubs_log' on file 1. RESTORE DATABASE successfully processed 257 pages in 0.525 seconds (3.996 MB/sec).
The syntax for restoring differential backups is the same as for restoring full backups. However, the full backup must be restored first. For instance the following statement restores pubs database from a differential backup:
/* first restore the full backup */ RESTORE DATABASE pubs FROM DISK = 'e:\pubs.bak' WITH NORECOVERY /* next apply the differential backup */ RESTORE DATABASE pubs FROM DISK = 'e:\pubs_differential.bak' WITH RECOVERY
Informational RESTORE Commands
In addition to the RESTORE DATABASE statement, you should be familiar with a few other RESTORE commands that can help greatly if you're dealing with numerous database backups stored on various media. These statements do not perform the RESTORE operation; rather they provide meta-data information about databases and database files contained on backup media.
RESTORE FILELISTONLY retrieves the list of database and log files in the backup. For example:
RESTORE FILELISTONLY FROM DISK = 'e:\pubs.bak'
Results:
LogicalName PhysicalName Type FileGroupName Size MaxSize pubs c:\pubs.mdf D PRIMARY 2031616 35184372080640 pubs2 c:\pubs2.ndf D Pubs_secondary 1048576 35184372080640 pubs_log c:\pubs_log.ldf L NULL 786432 35184372080640
This command shows us the logical and physical names of files, file types (D for database and L for Log), file group names, actual and maximum sizes for each file in bytes. With that information we could construct a RESTORE statement including the WITH MOVE clause to move the physical files to a different location.
RESTORE HEADERONLY returns header information about all backup sets on the supplied backup device. Although this command returns a wealth of information, typically you will only examine a small portion of it. For instance, it might be beneficial to know a backed up database's collation, type of backup, first and last Log Sequence Numbers and database compatibility level.
For example:
RESTORE HEADERONLY FROM DISK = 'e:\pubs.bak'
This command will return the following information:
| Column | Explanation | | BackupName | Name of the backup set | | BackupDescription | Backup set description | | BackupType | 1 = Database
2 = Transaction Log
4 = File
5 = Differential Database
6 = Differential File | | ExpirationDate | Backup set expiration date | | Compressed | Whether backup set is compressed or not | | Position | Position of the backup set in the volume | | DeviceType | Device type used for backup: DISK
2 = Logical
102 = Physical
TAPE
5 = Logical
105 = Physical
PIPE
6 = Logical
106 = Physical
Virtual Device
7 = Logical
107 = Physical | | UserName | User that performed the backup | | ServerName | SQL Server name that wrote the backup | | DatabaseName | Database backed up | | DatabaseVersion | Internal database version from which backup was created | | DatabaseCreationDate | Database creation date and time | | BackupSize | Backup size in bytes | | Data | Data | | FirstLSN | Log Sequence Number (LSN) of the first transaction within the backup. NULL for file backups | | LastLSN | LSN of the last transaction within the backup. NULL for file backups | | CheckpointLSN | LSN of the most recent CHECKPOINT at the time of backup creation | | DatabaseBackupLSN | LSN of the most recent database backup | | DifferentialBaseLSN | LSN of the most recent differential backup | | BackupStartDate | Date and time backup operation began | | BackupFinishDate | Date and time backup operation completed | | SortOrder | Server sort order for database backups only | | CodePage | Code page or character set used by the server | | UnicodeLocaleID | Unicode Locale ID used by the server | | UnicodeComparisonStyle | Unicode comparison style for additional control over sorting Unicode data | | CompatibilityLevel | Database compatibility level for the backed up database | | SoftwareVendorID | Software Vendor identifier. For SQL Server this number is 4608 | | SoftwareVersionMajor | Major version number of software that created the backup | | SoftwareVersionMinor | Minor version number of software that created the backup | | SoftwareVersionBuild | Build number of the software that created the backup | | MachineName | Name of the computer that performed the backup | | Flags | X1 is returned if bulk-logged data is captured in this backup | | BindingID | Database binding ID | | RecoveryForkID | Recovery Fork GUID for the current backup | | Collation | Database Collation |
RESTORE LABELONLY is a quick way to find out what the backup media contains. This command returns a single row of output with following information:
| Column | Explanation | | MediaName | Name of the media | | MediaSetID | GUID of the media set. This column is NULL if there is only a single media family in the media set | | FamilyCount | Number of media families in the media set | | FamilySequenceNumber | Sequence number of the current media family | | MediaFamilyID | GUID of the media family | | MediaSequenceNumber | Sequence number of the media in the media family | | MediaLabelPresent | Whether the media description contains:
1 = Microsoft Tape Format media label
0 = Media description | | MediaDescription | Free text media description | | SoftwareName | Name of software that wrote the backup label | | SoftwareVendorID | Vendor ID of software vendor that wrote the backup | | MediaDate | Date and time when backup label was written |
|