Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Can't revert from snapshot Expand / Collapse
Author
Message
Posted Friday, October 12, 2012 1:08 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 4:51 PM
Points: 558, Visits: 1,651
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
Posted Friday, October 12, 2012 2:47 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 4:51 PM
Points: 558, Visits: 1,651
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
Posted Monday, October 15, 2012 5:01 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:51 PM
Points: 3,115, Visits: 3,237
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




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1372992
Posted Tuesday, October 16, 2012 12:07 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:39 PM
Points: 286, Visits: 582
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 »

Add to briefcase

Permissions Expand / Collapse