October 12, 2012 at 1:08 pm
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?
October 12, 2012 at 2:47 pm
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
October 15, 2012 at 5:01 pm
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/
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com
October 16, 2012 at 12:07 am
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply