Home > Database Snapshot > Reverting Database using Database Snapshot

Reverting Database using Database Snapshot

 

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.

 

CREATE
DATABASE DBSO_snapshot ON

( NAME = SPri1_dat,
FILENAME
=
‘K:\snapshotdb\SPri1dat_1200.ss’),

( NAME = SPri2_dat,
FILENAME
=
‘K:\snapshotdb\SPri2dt_1200.ss’),

( NAME = SGrp1Fi1_dat,
FILENAME
=
‘K:\snapshotdb\SG1Fi1dt_1200.ss’),

( NAME = SGrp1Fi2_dat,
FILENAME
=
‘K:\snapshotdb\SG1Fi2dt_1200.ss’),

( NAME = SGrp2Fi1_dat,
FILENAME
=
‘K:\snapshotdb\SG2Fi1dt_1200.ss’),

( NAME = SGrp2Fi2_dat,
FILENAME
=
‘K:\snapshotdb\SG2Fi2dt_1200.ss’)

AS
SNAPSHOT
OF DBSO

GO

 

 

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

 

 

 

 

 

 

Categories: Database Snapshot
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: