Restore Sql Script

[Dannie Heinzen]

Restoresthe entire database, beginning with a full database backup, which may be followed by restoring a differential database backup (and log backups). For more information, see Complete Database Restores - Simple Recovery Model or Complete Database Restores - Full Recovery Model.

Restores a file or filegroup in a multi-filegroup database. 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. For more information, see File Restores - Full Recovery Model and File Restores - Simple Recovery Model.

Restores the database in stages, beginning with the primary filegroup and one or more secondary filegroups. A piecemeal restore begins with a RESTORE DATABASE using the PARTIAL option and specifying one or more secondary filegroups to be restored. For more information, see Piecemeal Restores - SQL Server.

Under the full or bulk-logged recovery model, restoring log backups is required to reach the desired recovery point. For more information about restoring log backups, see Apply Transaction Log Backups - SQL Server.

Where online restore is supported, if the database is online, file restores and page restores are automatically online restores and, also, restores of secondary filegroup after the initial stage of a piecemeal restore.

For a database using the full or bulk-logged recovery model, in most cases you must back up the tail of the log before restoring the database. Restoring a database without first backing up the tail of the log results in an error, unless the RESTORE DATABASE statement contains either the WITH REPLACE or the WITH STOPAT clause, which must specify a time or transaction that occurred after the end of the data backup. For more information about tail-log backups, see Tail-Log Backups.

RECOVERY (the default) indicates that rollback should be performed after rollforward is completed for the current backup. No further backups can be restored. Select this option once you have restored all of the necessary backups.

Recovering the database requires that the entire set of data being restored (the rollforward set) is consistent with the database. If the rollforward set has not been rolled forward far enough to be consistent with the database and RECOVERY is specified, the Database Engine issues an error. For more information about the recovery process, see Restore and Recovery Overview (SQL Server).

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. For information about the new default path, see File Locations for Default and Named Instances of SQL Server.

After you restore an earlier version database to SQL Server, 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. 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. Note also that when the upgrade option is set to import, the associated full-text indexes are rebuilt if a full-text catalog is not available. To change the setting of the upgrade_option server property, use sp_fulltext_service.

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). When a database has been upgraded from an earlier version, the DMK should be regenerated to use the newer AES algorithm. For more information about regenerating the DMK, see ALTER MASTER KEY. The time required to regenerate the DMK key to upgrade to AES depends upon the number of objects protected by the DMK. Regenerating the DMK key to upgrade to AES is only necessary once, and has no impact on future regenerations as part of a key rotation strategy.

During an offline restore, if the specified database is in use, RESTORE forces the users off after a short delay. For online restore of a non-primary filegroup, the database can stay in use except when the filegroup being restored is being taken offline. Any data in the specified database is replaced by the restored data.

Restoring a database clears the plan cache for the database being restored. Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance.

URL is the format used to specify the location and the file name for Microsoft Azure Blob Storage or S3-compatible object storage. Although Azure Blob Storage is a service, the implementation is similar to disk and tape to allow for a consistent and seamless restore experience for all devices.

Backup and restore to S3-compatible object storage was introduced in SQL Server 2022 (16.x). For more information about restoring from S3-compatible object storage, see SQL Server backup and restore with S3-compatible object storage. Also review the option for SQL Server backup to URL for S3-compatible object storage.

Using the WITH RESTRICTED_USER option, however, overrides this behavior for the user access option setting. This setting is always set following a RESTORE statement, which includes the WITH RESTRICTED_USER option.

To restore a database that is encrypted, you must have access to the certificate or asymmetric key that was used to encrypt the database. 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. For more information, see SQL Server Certificates and Asymmetric Keys.

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.

The RESTORE statement also can be used to perform restores to alternate locations, differential restores, file and filegroup restores, and differential file and filegroup restores of full-text data. In addition, RESTORE can restore full-text files only, as well as with database data.

Full-text catalogs imported from SQL Server 2005 (9.x) are still treated as database files. For these, the SQL Server 2005 (9.x) procedure for backing up full-text catalogs remains applicable, except that pausing and resuming during the backup operation are no longer necessary. For more information, see Backing Up and Restoring Full-Text Catalogs.

When restoring a database to SQL Server 2022 (16.x) from a previous version, it is recommended to execute sp_updatestats on the database, setting the proper metadata for the statistics auto drop feature. For more information, see statistics auto drop option.

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. By default, a big data cluster does not include an endpoint that enables a connection to the instance. You must expose this endpoint manually.

SQL Server includes backup and restore history tables that track the backup and restore activity for each server instance. When a restore is performed, the backup history tables are also modified. For information on these tables, see Backup History and Header Information.

REPLACE should be used rarely and only after careful consideration. Restore normally prevents accidentally overwriting a database with a different database. 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. This is an important safeguard.

With the REPLACE option, restore allows you to overwrite an existing database with whatever database is in the backup set, even if the specified database name differs from the database name recorded in the backup set. This can result in accidentally overwriting a database by a different database.

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. Arbitrary data loss is possible if existing files are overwritten, although the restored database is complete.

Undoing the effects of a restore is not possible; however, you can negate the effects of the data copy and rollforward by starting over on a per-file basis. To start over, restore the desired file and perform the rollforward again. For example, if you accidentally restored too many log backups and overshot your intended stopping point, you would have to restart the sequence.

A revert database operation (specified using the DATABASE_SNAPSHOT option) 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. Only the snapshot to which you are reverting can currently exist. The revert operation then rebuilds the log (therefore, you cannot later roll forward a reverted database to the point of user error).

3a8082e126