Edit

Share via

Revert a Database to a Database Snapshot

  • Article

Applies to: SQL Server

If data in an online database becomes damaged, in some cases, reverting the database to a database snapshot that predates the damage might be an appropriate alternative to restoring the database from a backup. For example, reverting a database might be useful for reverse a recent serious user error, such as a dropped table. However, all changes made after the snapshot was created are lost.

Before You Begin

Limitations and Restrictions

Reverting is unsupported under the following conditions:

  • There are multiple snapshots for the database. For reverting, there must only be one snapshot for the database, to which you plan to revert.

  • Any read-only or compressed filegroups exist in the database.

  • Any files are now offline but were online when the snapshot was created.

Before reverting a database, consider the following limitations:

  • Reverting is not intended for media recovery. A database snapshot is an incomplete copy of the database files, so if either the database or the database snapshot is corrupted, reverting from a snapshot is likely to be impossible. Furthermore, even when it is possible, reverting in the event of corruption is unlikely to correct the problem. Therefore, taking regular backups and testing your restore plan are essential to protect a database. For more information, see Back Up and Restore of SQL Server Databases.

    Note

    If you need to be able to restore the source database to the point in time at which you created a database snapshot, use the full recovery model and implement a backup policy that enables you to do that.

  • The original source database is overwritten by the reverted database, so any updates to the database since the snapshot's creation are lost.

  • The revert operation also overwrites the old log file and rebuilds the log. Consequently, you cannot roll the reverted database forward to the point of user error. Therefore, we recommend that you back up the log before reverting a database.

    Note

    Although you cannot restore the original log to roll forward the database, the information in the original log file can be useful for reconstructing lost data.

  • Reverting breaks the log backup chain. Therefore, before you can take log backups of the reverted database, you must first take a full database backup or file backup. We recommend a full database backup.

  • During a revert operation, both the snapshot and the source database are unavailable. The source database and snapshot are both marked "In restore." If an error occurs during the revert operation, when the database starts up again, the revert operation will try to finish reverting.

  • The metadata of a reverted database is the same as the metadata at the time of the snapshot.

  • Reverting drops all the full-text catalogs.

Prerequisites

Ensure that the source database and database snapshot meet the following prerequisites:

Security

Permissions

Any user who has RESTORE DATABASE permissions on the source database can revert it to its state when a database snapshot was created.

How to Revert a Database to a Database Snapshot (Using Transact-SQL)

To revert a database to a database snapshot

Note

For an example of this procedure, see Examples (Transact-SQL), later in this section.

  1. Identify the database snapshot to which you want to revert the database. You can view the snapshots on a database in SQL Server Management Studio (see View a Database Snapshot (SQL Server)). Also, you can identify the source database of a view from the source_database_id column of the sys.databases (Transact-SQL) catalog view.

  2. Drop any other database snapshots.

    For information on dropping snapshots, see Drop a Database Snapshot (Transact-SQL). If the database uses the full recovery model, before reverting, you should back up the log. For more information, see Back Up a Transaction Log (SQL Server) or Back Up the Transaction Log When the Database Is Damaged (SQL Server).

  3. Perform the revert operation.

    A revert operation requires RESTORE DATABASE permissions on the source database. To revert the database, use the following Transact-SQL statement:

    RESTORE DATABASE database_name FROM DATABASE_SNAPSHOT =database_snapshot_name

    Where database_name is the source database and database_snapshot_name is the name of the snapshot to which you want to revert the database. Notice that in this statement, you must specify a snapshot name rather than a backup device.

    For more information, see RESTORE (Transact-SQL).

    Note

    During the revert operation, both the snapshot and the source database are unavailable. The source database and snapshot are both marked as "In restore." If an error occurs during the revert operation, it will try to finish reverting when the database starts up again.

  4. If the database owner changed since creation of the database snapshot, you may want to update the database owner of the reverted database.

    Note

    The reverted database retains the permissions and configuration (such as database owner and recovery model) of the database snapshot.

  5. Start the database.

  6. Optionally, back up the reverted database, especially if it uses the full (or bulk-logged) recovery model. To back up a database, see Create a Full Database Backup (SQL Server).

Examples (Transact-SQL)

This section contains the following examples of reverting a database to a database snapshot:

A. Reverting a snapshot on the AdventureWorks database

This example assumes that only one snapshot currently exists on the AdventureWorks2022 database. For the example that creates the snapshot to which the database is reverted here, see Create a Database Snapshot (Transact-SQL).

USE master;  
-- Reverting AdventureWorks to AdventureWorks_dbss1800  
RESTORE DATABASE AdventureWorks from   
DATABASE_SNAPSHOT = 'AdventureWorks_dbss1800';  
GO

B. Reverting a snapshot on the Sales database

This example assumes that two snapshots currently exist on the Sales database: sales_snapshot0600 and sales_snapshot1200. The example deletes the older of the snapshots and reverts the database to the more recent snapshot.

For the code for creating the sample database and snapshots on which this example depends, see:

--Test to see if sales_snapshot0600 exists and if it   
-- does, delete it.  
IF EXISTS (SELECT database_id FROM sys.databases  
    WHERE NAME='sales_snapshot0600')  
    DROP DATABASE SalesSnapshot0600;  
GO  
-- Reverting Sales to sales_snapshot1200  
USE master;  
RESTORE DATABASE Sales FROM DATABASE_SNAPSHOT = 'sales_snapshot1200';  
GO

Related Tasks

See Also

Database Snapshots (SQL Server)
RESTORE (Transact-SQL)
sys.databases (Transact-SQL)
Database Mirroring and Database Snapshots (SQL Server)