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 = , = , SIZE = 4096KB , FILEGROWTH = 51200KB )
N'DBSnapshot_Demo'
FILENAME
N'C:2012 InstanceSQL DataDBSnapshot_Demo.mdf'
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
) [PRIMARY]
ON
GO
CREATE CLUSTERED INDEX
[PK_ContactInfo_ID] ON [dbo].[ContactInfo]
(
       [ID]
ASC
) = = = = = ) [PRIMARY]
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
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 DATABASEDBSnapshot_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 OFDBSnapshot_Demo--source database to create snapshot of

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]
= '1000 Ericson Dr.'
set Address
= '2000 Ericson Dr.'
where Address
  Name
and
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
  t1. = t2.
SET
Address
Address
  dbo.contactinfo AS t1
FROM
DBSnapshot_Demo_snap.dbo.ContactInfo t2
INNER JOIN
AS
t1.ID = t2.ID
ON

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. @database_name =
sp_delete_database_backuphistory
N'DBSnapshot_Demo_snap'
GO
USE
[master]
GO
DROP DATABASE
[DBSnapshot_Demo_snap]
GO

Thanks for reading.