Can't revert from snapshot

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » Can't revert from snapshot

Can't revert from snapshot

Rate Topic

Display Mode

Topic Options

Author

Message

dan-572483

Posted Friday, October 12, 2012 1:08 PM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 10:54 PM
Points: 330, Visits: 948

I wanted to remove user data from a database to make a new version of the database with no user data, then restore the database to the original state. To save the time it takes to run a backup of a 600GB DB, I create a database snapshot which I could revert to later. My steps were:

1) Create a snapshot of database A
2) Run stored procedure to drop 500+ GB of userdata
3) Run DBCC SHRINKDATABASE to shrink the data files
4) Run Full Backup job
5) Run ALTER DATABASE commands to regrow DB files to their previous size
5) Run RESTORE DATABASE <database name> FROM DATABASE_SNAPSHOT = '<Snapshot name>'

This produces the result:
"Msg 3137, Level 16, State 1, Line 2
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 2
RESTORE DATABASE is terminating abnormally."

There are no other snapshots and no Read Only filegroups. Recovery mode is SIMPLE. What went wrong? Did I do too much stuff to the DB?

Post #1372332

dan-572483

Posted Friday, October 12, 2012 2:47 PM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 10:54 PM
Points: 330, Visits: 948

Lesson learned: If you are doing massive changes to a large database that you'll want to reverse, use a full backup, not a snapshot.

http://support.microsoft.com/kb/2002606

Dan

Post #1372364

IgorMi

Posted Monday, October 15, 2012 5:01 PM

SSCommitted

Group: General Forum Members
Last Login: Today @ 3:52 AM
Points: 1,878, Visits: 1,449

Hi,

A database snapshot is not a backup, it is just an "empty" database that holds the changes in the source database pages and during time it grows as more transactions commit.

Here are some links: http://www.sqlservercentral.com/articles/Disaster+Recovery+(DR)/2598/
http://blog.sqlauthority.com/2010/04/05/sql-server-2008-introduction-to-snapshot-database-restore-from-snapshot/

Regards
IgorMi

Post #1372992

MissTippsInOz

Posted Tuesday, October 16, 2012 12:07 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, April 29, 2013 10:39 PM
Points: 286, Visits: 519

dan-572483 (10/12/2012)

Lesson learned: If you are doing massive changes to a large database that you'll want to reverse, use a full backup, not a snapshot.

http://support.microsoft.com/kb/2002606

Dan

The article is very interesting but it doesn't seem to refer to the same issue that you were experiencing. If you have decided to use a full backup as your rollback process then fair enough but, if it were me, I wouldn't be relying on that KB article as the resolution to your problem.

Clare
_________________________________________________________________________________________________________________
Measure twice; cut once (and have a good saw)

Hey, just a thought.....did you check Books Online yet?

Post #1373043

« Prev Topic | Next Topic »

Permissions