Share via

  • Article

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

Database snapshots are not redundant storage, and as a result do not provide any protection against disk errors or other types of corruption. However, if a user error occurs in an online database, you can revert the database to a database snapshot that predates the error.

Important

Taking regular backups and testing your restore plan are essential to protect a database. If you must restore the source database to the point in time at which you created a database snapshot, implement a backup policy that enables you to do that.

Reverting overwrites the original source database with the reverted database. Reverting to a snapshot drops all the full-text catalogs.

Important

Before reverting, you should familiarize yourself with the information in Reverting to a Database Snapshot.

To revert a database to a database snapshot

  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 How to: View a Database Snapshot (SQL Server Management Studio)). 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 How to: 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 How to: Back Up a Transaction Log (SQL Server Management Studio) or How to: Back Up the Transaction Log When the Database Is Damaged (Transact-SQL).

  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. For information on backing up the database, see How to: Back Up a Database (SQL Server Management Studio) or How to: Create a Full Database Backup (Transact-SQL).

Example

This section contains examples of reverting a database to a database snapshot.

A. Reverting a snapshot on the AdventureWorks2008R2 database

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

USE master;
-- Reverting AdventureWorks2008R2 to AdventureWorks2008R2_dbss1800
RESTORE DATABASE AdventureWorks2008R2 from 
DATABASE_SNAPSHOT = 'AdventureWorks2008R2_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 dbid 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