Microsoft® SQL Server® 2012 Bible

643

Chapter 24: Database Snapshots

24

Dropping a Database Snapshot

Dropping a database snapshot is similar to dropping a regular database. The only thing dif-

ferent is that the users do not need to be terminated before dropping a database snapshot.

They will be automatically terminated when you drop the snapshot.

The following example drops the database snapshot named

AdventureWorks2012_Snapshot.

DROP DATABASE AdventureWorks2012_Snapshot;

The preceding Transact-SQL command drops the database snapshot and deletes the files in

the database snapshot. You can also drop the database snapshot from Management Studio

by right-clicking the database snapshot and selecting Delete.

Rolling Back a Database Snapshot

Database snapshots come in handy when testing code releases to ensure that a consistent

database is available if a rollback is needed. Rolling back a database snapshot is done

via the RESTORE DATABASE statement; however, instead of specifying a disk or tape to

restore, a DATABASE_SNAPSHOT is specified instead. The following example reverts the

AdventureWorks2012 database to the AdventureWorks2012_Snapshot database

snapshot.

USE master;

GO

RESTORE DATABASE AdventureWorks2012

FROM DATABASE_SNAPSHOT = 'AdventureWorks2012_Snapshot';

GO

Reverting to a database snapshot rebuilds the transaction log and breaks the log backup chain. This means that you

cannot perform point-in-time restores in the period from the last log backup to the time when you reverted to the

database snapshot. If you want to perform point-in-time restores in the future, take a full or differential backup and

then start taking log backups again.

If the source database has multiple database snapshots and you attempt to revert the data-

base to one of the snapshots, you receive error 3137 as shown here:

Msg 3137, Level 16, State 4, Line 1

Database cannot be reverted. Either the primary or the snapshot names

are improperly specified, all other snapshots have not been dropped,

or there are missing files.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

c24.indd 643c24.indd 643 7/31/2012 9:25:50 AM7/31/2012 9:25:50 AM

http://www.it-ebooks.info