SQL Server- Use Database Snapshots for Code Deploys to Recover a Single Table or Entire Database

I have found that database snapshots are under-utilized and wanted to show an example of how efficient it is to use them prior to a code deploy for the purpose of recovering a single table or reverting an entire database from a snapshot in the event of unexpected functionality within an application.

First, we will start with creating our initial data structures for the purpose of demoing a database snapshot for recovery during a code deploy:

Sample Database Code:

CREATE DATABASE [DBSnapshot_Demo] ON  PRIMARY
( NAME = N’DBSnapshot_Demo’, FILENAME = N’C:2012 InstanceSQL DataDBSnapshot_Demo.mdf’ , SIZE = 4096KB , FILEGROWTH = 51200KB )
LOG ON
( NAME = N’DBSnapshot_Demo_log’, FILENAME = N’C:2012 InstanceSQL
LogDBSnapshot_Demo_log.ldf’ , SIZE = 1024KB , FILEGROWTH = 51200KB )
GO

Code Table and Data:

USE [DBSnapshot_Demo]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ContactInfo](

       [ID] [int] IDENTITY(1,1) NOT NULL,
       [Date] [nchar](100) NOT NULL,
       [Name] [nchar](100) NOT NULL,
       [Address] [nchar](100) NOT NULL,
       [City] [nchar](100) NOT NULL,
       [State] [nchar](10) NOT NULL,
       [Zip] [nchar](10) NOT NULL,
       [Country] [nchar](100) NOT NULL,
       [PhoneNumber] [nchar](10) NOT NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [PK_ContactInfo_ID] ON [dbo].[ContactInfo]
(

       [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

INSERT INTO [dbo].[ContactInfo] ([Date],[Name],[Address],[City],[State],[Zip],[Country],[PhoneNumber])
VALUES (getdate(), ‘Tim L’, ‘2000 Ericson Dr.’, ‘Warrendale’, ‘PA’, ‘15086’, ‘USA’, ‘4125551000′),
(getdate(), ‘John H’, ‘2000 Ericson Dr.’, ‘Warrendale’, ‘PA’, ‘15086’, ‘USA’, ‘4125551001’),
(getdate(), ‘Corey B’, ‘2000 Ericson Dr.’, ‘Warrendale’, ‘PA’, ‘15086’, ‘USA’, ‘4125551002’),
(getdate(), ‘Kon M’, ‘2000 Ericson Dr.’, ‘Warrendale’, ‘PA’, ‘15086’, ‘USA’, ‘4125551003’)

Now that we have our database and data, we can do a code deploy to demonstrate the use of snapshot for rollback functionality. First, we need to create the snapshot of DBSnapshot_Demo database:

CREATE DATABASE DBSnapshot_Demo_snap
ON
(
        NAME = DBSnapshot_Demo, — the logical file name of the source database datafile
        FILENAME = ‘C:2012 InstanceSQL DataDBSnapshot_Demo_snap.ss’ –the path and name of the snapshot
 )
AS SNAPSHOT OF DBSnapshot_Demo –source database to create snapshot of

 

The snapshot has now been created for the DBSnapshot_Demo database:

 

 

Now I need to update the record for Kon  M to indicate a change in office location to 1000 Ericson Dr.

 

 

I accidentally updated all records to indicate a change in address to 1000 Ericson Dr. by executing the following:

 

update [ContactInfo]
   set Address = ‘1000 Ericson Dr.’                                                                                   
   where Address = ‘2000 Ericson Dr.’

 

I meant to execute the following to only update the record for Kon M:

update [ContactInfo]
  set Address = ‘1000 Ericson Dr.’                                                                                   
  where Address = ‘2000 Ericson Dr.’
  and Name like ‘Kon%’  

 

This is where taking a database snapshot will help recover the data, as a result of bad code.

The first option is to use a join off of the database snapshot to roll back the previous version of the table:

 

UPDATE t1
  SET t1.Address = t2.Address
  FROM dbo.contactinfo AS t1
  INNER JOIN DBSnapshot_Demo_snap.dbo.ContactInfo AS t2
  ON t1.ID = t2.ID

 

The other option is to revert the entire DBSnapshot_Demo database from the DBSnapshot_Demo_snap database snapshot:

 

USE master
GO                        
RESTORE DATABASE DBSnapshot_Demo
FROM DATABASE_SNAPSHOT=‘DBSnapshot_Demo_snap’

 

Remember to drop the snapshot after the work is complete. A database snapshot will cause additional overhead to write to the database from where the snapshot was taken. 

 

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name =
N’DBSnapshot_Demo_snap’
GO
USE [master]
GO
DROP DATABASE [DBSnapshot_Demo_snap]
GO

 

Thanks for reading. 

Leave a Reply

Your email address will not be published. Required fields are marked *