I was doing a database code deployment the other night and the first step in the release plan was “Take a full database backup in case we have to roll back the deployment”. The database in question was about 100 GB in size, so not huge, but not small either. On our current hardware a full backup takes anywhere from 15-20 minutes on average. That’s 15-20 minutes longer than it needed to be because I knew there was a better way to offer that same rollback protection. The database snapshot.
Database snapshots are a feature that was introduced with SQL Server 2005 that allows you to create a transactionally consistent picture of your database at the moment the snapshot was taken. Unfortunately it’s an Enterprise Edition feature only so if you are on anything other than Enterprise Edition you wont be able to use snapshots. I still think it’s a feature worth knowing about.
Snapshots are super easy to manage and they have very little performance impact on the underlying database. And the best part is you always have your standard database backups to fall back on in case you need to. You have fulls, diffs and tlogs and could always be used as a second form of protection, but there’s no reason to take another full backup during a code deployment. Why waste 20 minutes waiting for a backup before you begin your code deployment when you have snapshots available to you?
CREATE DATABASE AdventureWorks2012_SnapShot
ON
(
NAME = 'AdventureWorks2012_Data',
FILENAME ='C:\SQL\MSSQL11.INST1\MSSQL\DATA\AdventureWorks2012_Data.ss'
)
AS SNAPSHOT OF AdventureWorks2012 ;
You will now find your snapshot under Database Snapshots in SSMS
RESTORE DATABASE AdventureWorks2012
FROM DATABASE_SNAPSHOT = 'AdventureWorks2012_SnapShot' ;
DROP DATABASE [AdventureWorks2012_SnapShot] ;
<li>Maintaining historical data for report generation.</li>
<li>Using a mirror database that you are maintaining for availability purposes to offload reporting.</li>
<li>Safeguarding data against administrative error.</li>
<li>In the event of a user error on a source database, you can revert the source database to the state it was in when a given database snapshot was created. Data loss is confined to updates to the database since the snapshot's creation.</li>