It is always on ease having a backup and recovery strategies in place but few times it also important to recover a database to appoint which doesn’t require a full database restore or in shot no hard efforts. While working with one of my previous organization I was asked to do something which actually made me interested in using Database snapshot feature.
Scenario: A New modules was designed and developed for finance. Once the Module will get deployed in production excel data files need to be uploaded. At the backed few SP will execute which will do processing of data and finance related data will be updated /flag (inactive).
I have been asked to design a recovery strategy for the Database which recovers the database before the time of deployment and without downtime. I thought on all recovery models and at last decided to user database snapshot.
Database snapshot give me a flexibility to revert back to the time when the snapshot was created. As the snapshot database is read-only we can rum comparison between OLPT database and database snapshot.
Combing back to the original question, let’s say Deployment has to start at 1:00 AM, will stop the customer /user access to the application and database so that no transaction should take place. Once the application and database is inaccessible to the customer run select * from sys.sysprocesses to check what all open transaction are in place other then background processes.
Create the database Snapshot.
( NAME = SPri1_dat,
( NAME = SPri2_dat,
( NAME = SGrp1Fi1_dat,
( NAME = SGrp1Fi2_dat,
( NAME = SGrp2Fi1_dat,
( NAME = SGrp2Fi2_dat,
In case you think after the deployment there is some issue with the module or data and you need to revert back. Restore the snapshot which will revert the changes to the database till the point of snapshot.
Here is an example
Revert the database to a database snapshot (No connection should exists to the source database to apply the snapshot.)
Now to verify that database is reverted successfully, check the records