Perform a database restore with options
In this tutorial, we will learn how to perform a database restore with options.
About Restore Scenarios
SQL Server supports a variety of restore scenarios:
- Firstly, Complete database restore. Restores the entire database, beginning with a full database backup, which may be followed by restoring a differential database backup (and log backups).
- Secondly, File restore. Restores a file or filegroup in a multi-filegroup database. Note that under the simple recovery model, the file must belong to a read-only filegroup. After a full file restore, a differential file backup can be restored.
- Then, Page restore. Restores individual pages. Page restore is available only under the full and bulk-logged recovery models.
- After that, Piecemeal restore. Restores the database in stages, beginning with the primary filegroup and one or more secondary filegroups. However, a piecemeal restore begins with a RESTORE DATABASE using the PARTIAL option and specifying one or more secondary filegroups to be restored.
- Next, Recovery only. Recovers data that is already consistent with the database and needs only to be made available.
- Lastly, Transaction log restore. Under the full or bulk-logged recovery model, restoring log backups is required to reach the desired recovery point.
Compatibility Support
Backups of master, model and msdb that were created by using an earlier version of SQL Server cannot be restored by SQL Server 2019 (15.x).
- Firstly, each version of SQL Server uses a different default path than earlier versions. Therefore, to restore a database that was created in the default location for earlier version backups, you must use the MOVE option.
- Then, after you restore an earlier version database to SQL Server 2019 (15.x), the database is automatically upgraded. Typically, the database becomes available immediately. However, if a SQL Server 2005 (9.x) database has full-text indexes, the upgrade process either imports, resets, or rebuilds them, depending on the setting of the upgrade_option server property. Further, if the upgrade option is set to import (upgrade_option = 2) or rebuild (upgrade_option = 0), the full-text indexes will be unavailable during the upgrade. Depending on the amount of data being indexed, importing can take several hours, and rebuilding can take up to ten times longer.
- Lastly, when a database is first attached or restored to a new instance of SQL Server, a copy of the database master key (encrypted by the service master key) is not yet stored in the server. You must use the OPEN MASTER KEY statement to decrypt the database master key (DMK). Once the DMK has been decrypted, you have the option of enabling automatic decryption in the future by using the ALTER MASTER KEY REGENERATE statement to provision the server with a copy of the DMK, encrypted with the service master key (SMK).
Interoperability
- Firstly, Database Settings and Restoring. During a restore, most of the database options that are settable using ALTER DATABASE are reset to the values in force at the time of the end of backup.
- Secondly, Restoring an Encrypted Database. To restore a database that is encrypted, you must have access to the certificate or asymmetric key that was used to encrypt the database. However, without the certificate or asymmetric key, the database cannot be restored. As a result, the certificate that is used to encrypt the database encryption key must be retained as long as the backup is needed.
- Thirdly, Restoring a Database Enabled for vardecimal Storage. Backup and restore work correctly with the vardecimal storage format.
- Next, Restore Full-Text Data. Full-text data is restored together with other database data during a complete restore. Using the regular RESTORE DATABASE database_name FROM backup_device syntax, the full-text files are restored as part of the database file restore.
- Lastly, SQL Server Big Data Clusters. Certain operations, including configuring server (instance level) settings, or manually adding a database to an availability group, require a connection to the SQL Server Instance. Operations like sp_configure, RESTORE DATABASE, or any DDL command in a database belonging to an availability group require a connection to the SQL Server instance.
REPLACE Option Impact
REPLACE should be used rarely and only after careful consideration. Restore normally prevents accidentally overwriting a database with a different database. However, if the database specified in a RESTORE statement already exists on the current server and the specified database family GUID differs from the database family GUID recorded in the backup set, the database is not restored. Further, the REPLACE option overrides several important safety checks that restore normally performs. The overridden checks are as follows:
- Firstly, Restoring over an existing database with a backup taken of another database. With the REPLACE option, restore allows you to overwrite an existing database with whatever database is in the backup set. And, even if the specified database name differs from the database name recorded in the backup set.
- Secondly, Restoring over a database using the full or bulk-logged recovery model where a tail-log backup has not been taken and the STOPAT option is not used. With the REPLACE option, you can lose committed work, because the log written most recently has not been backed up.
- Lastly, Overwriting existing files. For example, a mistake could allow overwriting files of the wrong type, such as .xls files, or that are being used by another database that is not online.
Reverting a Database to a Database Snapshot
A revert database operation takes a full source database back in time by reverting it to the time of a database snapshot. That is, overwriting the source database with data from the point in time maintained in the specified database snapshot. However, data loss is confined to updates to the database since the snapshot’s creation. The metadata of a reverted database is the same as the metadata at the time of snapshot creation. However, reverting to a snapshot drops all the full-text catalogs.
Further, reverting from a database snapshot is not intended for media recovery. Unlike a regular backup set, the database snapshot is an incomplete copy of the database files. If either the database or the database snapshot is corrupted, reverting from a snapshot is likely to be impossible. Furthermore, even when possible, reverting in the event of corruption is unlikely to correct the problem.
Restrictions on Reverting
Reverting is unsupported under the following conditions:
- Firstly, the source database contains any read-only or compressed filegroups.
- Secondly, any files are offline that were online when the snapshot was created.
- Lastly, more than one snapshot of the database currently exists.
Security
A backup operation may optionally specify passwords for a media set, a backup set, or both. When a password has been defined on a media set or backup set, you must specify the correct password or passwords in the RESTORE statement. Also, these passwords prevent unauthorized restore operations and unauthorized appends of backup sets to media using SQL Server tools. However, password-protected media can be overwritten by the BACKUP statement’s FORMAT option.
Examples
All the examples assume that a full database backup has been performed.
A. Restoring a full database
The following example restores a full database backup from the AdventureWorksBackups logical backup device.
SQL
RESTORE DATABASE AdventureWorks2012
FROM AdventureWorks2012Backups;
B. Restoring full and differential database backups
The following example restores a full database backup followed by a differential backup from the Z:\SQLServerBackups\AdventureWorks2012.bak backup device, which contains both backups. However, the full database backup to be restored is the sixth backup set on the device (FILE = 6), and the differential database backup is the ninth backup set on the device (FILE = 9). As soon as the differential backup is recovered, the database is recovered.
SQL
RESTORE DATABASE AdventureWorks2012
FROM DISK = ‘Z:\SQLServerBackups\AdventureWorks2012.bak’
WITH FILE = 6
NORECOVERY;
RESTORE DATABASE AdventureWorks2012
FROM DISK = ‘Z:\SQLServerBackups\AdventureWorks2012.bak’
WITH FILE = 9
RECOVERY;
C. Restoring a database using RESTART syntax
The following example uses the RESTART option to restart a RESTORE operation interrupted by a server power failure.
SQL
— This database RESTORE halted prematurely due to power failure.
RESTORE DATABASE AdventureWorks2012
FROM AdventureWorksBackups;
— Here is the RESTORE RESTART operation.
RESTORE DATABASE AdventureWorks2012
FROM AdventureWorksBackups WITH RESTART;
D. Restoring a database and move files
The following example restores a full database and transaction log and moves the restored database into the C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data directory.
SQL
RESTORE DATABASE AdventureWorks2012
FROM AdventureWorksBackups
WITH NORECOVERY,
MOVE ‘AdventureWorks2012_Data’ TO
‘C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\NewAdvWorks.mdf’,
MOVE ‘AdventureWorks2012_Log’
TO ‘C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\NewAdvWorks.ldf’;
RESTORE LOG AdventureWorks2012
FROM AdventureWorksBackups
WITH RECOVERY;
E. Copying a database using BACKUP and RESTORE
The following example uses both the BACKUP and RESTORE statements to make a copy of the AdventureWorks2012 database. However, the MOVE statement causes the data and log file to be restored to the specified locations. The RESTORE FILELISTONLY statement is used to determine the number and names of the files in the database being restored. Lastly, the new copy of the database is named TestDB.
SQL
BACKUP DATABASE AdventureWorks2012
TO AdventureWorksBackups ;
RESTORE FILELISTONLY
FROM AdventureWorksBackups ;
RESTORE DATABASE TestDB
FROM AdventureWorksBackups
WITH MOVE ‘AdventureWorks2012_Data’ TO ‘C:\MySQLServer\testdb.mdf’,
MOVE ‘AdventureWorks2012_Log’ TO ‘C:\MySQLServer\testdb.ldf’;
GO
Reference: Microsoft Documentation