SQL Server Database Snapshots

Introduction

MS SQL Server 2005 and later versions include the Database Snapshot feature to have snapshot of the database for reports, as a copy in different periods.

The Database Snapshot can be created multiple times and it can only be created using the T-SQL.

In this article, we will show how to create a Database Snapshot, how to see the snapshot created in the SQL Server Management Studio (SSMS), how to recover objects dropped or data removed using the snapshot.

Finally, we will learn how to create Snapshots automatically for reporting purposes.

Requirements

SQL Server Enterprise or Evaluation Edition is required
We are using SQL Server 2014, but earlier versions can be used
The Adventureworks Database is required
The Adventurewoks database has to be online

Getting started

To create a database snapshot, we need to use the T-SQL. It is the only way to do it. You cannot create a Database Snapshot in the SSMS.

The syntax is the following:

CREATE DATABASE AdventureWorks_snapshot ON

( NAME = AdventureWorks2012_Data,

--Name of the snapshot file

FILENAME =

'C:\script2\AdventureWorks_data_1800.ss' )

--It is a Snapshot of the adventureworks2012 database

AS SNAPSHOT OF [AdventureWorks2012];

As you can see, the syntax is similar to a normal database creation except for two things:

We use the word AS SNAPSHOT OF DATABASE_NAME to specify the name of the database that requires a snapshot.
By default, it is better to specify the extension of the snapshot datafile as .ss (which means SnapShot.

If everything is OK, you will be able to see the snapshot created in the SSMS:

Figure 1

The snapshots have read-only tables. If you try to update or delete the data you will not be able to do it. You will receive a read only message:

Figure 2

You can also use the T-SQL to try to update tables in the database snapshot with the same results:

Figure 3

The database snapshots files have a similar size that a normal database, but a smaller size on disk. This is because each time that the original database changes, the snapshot grows. This reduces a little bit the database performance because of the synchronization.

Figure 4

The file size of the Snapshot Database is just 3.31 MB. The Adventureworks2012 database has the same size and size in disk as shown in the picture 5:

Figure 5

The size on this of the source database is equal to 205 MB.

Using snapshots to recover objects

If by mistake (or any other circumstance) a user drops a stored procedure, a view or a table or any object, you can recover the database object using the snapshot.

Example

Imagine that an evil person drops the dbo.uspGetBillOfMaterials stored procedure in the Database

Drop procedure dbo.uspGetBillOfMaterials

Now, imagine that you are a smart person and you recover the stored procedure using the stored procedure from the Database Snapshot. To do this, generate the CREATE PROCEDURE from the Snapshot Database:

Figure 6

In the code generated, just replace the first part like this:

Replace

1	USE [AdventureWorks_snapshot]

With

1	USE [AdventureWorks2012]

Once replaced press F5 to execute the script:

Figure 7

If you follow all the steps, you will have your stored procedure restored. The same concept is applicable to any Database Object. You can easily recreate specific objects from the Snapshot Database to the source Database.

Restore the data from a table

In this new demo, we will truncate all the data from the table Person.Password and restore the information from the Database Snapshot snapshot.

First, truncate all the data from a table:

1	truncate table [Person].[Password]

Secondly, restore the information from the Snapshot Database table to the source Database:

USE [AdventureWorks2012]

INSERT INTO [Person].Password

Select

From [AdventureWorks_snapshot].[Person].Password

We just inserted the information from the snapshot table to the empty Person.Table table from the Adventureworks2012 database.

Restore the entire database from the snapshot

If all the objects and data were dropped, it would possible to recover all the information from the snapshot to the original database.

In this new example, we will delete all the views from the AdventureWorks database and recover all the information from the snapshot.

First we will have all the views of the AdventureWorks database:

Figure 8
Then we will drop all the views:

1
2
3
4
5

DECLARE @sql VARCHAR(MAX)='';
SELECT @sql=@sql+'DROP VIEW ['+name +'];' FROM sys.views;
EXEC(@sql);

As you can see in the Figure 9, all the views were removed:

Figure 9
Now, revert your database. You may have problems to restore the database because there are multiple connection available. If that is the case, set the AdventureWorks database in a single user mode to close the other connections.

1
2
3
4
5
6
7
8

USE master;
GO
ALTER DATABASE AdventureWorks2012
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
Finally, restore the AdventureWorks Database from the snapshot:

1
2
3
4
5

RESTORE DATABASE AdventureWorks2012 from

DATABASE_SNAPSHOT = 'AdventureWorks_snapshot';

If everything is OK, you will be able to see your dropped views again:

Figure 10

Create Database Snapshots every month

Finally, we will show how to create Database snapshots every month.

Here you have the T-SQL code to create a Database with the current month number:

DECLARE @SQL VARCHAR(MAX)='';

--GET THE MONTH NUMBER IN THE @MONTH VARIABLE

DECLARE @MONTH VARCHAR(2)= MONTH(GETDATE())

--CONCATENATE THE SNAPSHOT DATABASE AND THE MONTH NUMBER

SELECT @SQL ='CREATE DATABASE ADVENTUREWORKS_SNAPSHOT_'+@MONTH+

' ON (NAME=AdventureWorks2012_Data, FILENAME=''C:\scripts\adventure.ss'')

AS SNAPSHOT OF Adventureworks2012'

EXECUTE (@SQL)

The code creates a Database Snapshot with the current number of the month.

Figure 11

To create snapshots of the database, it is necessary to schedule a Job each month:

Figure 12

Specify a Name and optionally a description of the job:

Figure 13

In the steps page create a new page. Specify a Name and select the T-SQL Type (which is the first option by default):

Figure 14

Finally, schedule the job every month:

Figure 15

As you can see, it is very simple to automate tasks and work with Snapshot Databases.

Conclusion

As you can see, Snapshots is a simple way to create copies of your information to partially restore the information. The Snapshots cannot replace the traditional backups, because it depends on the source database. If the source database is corrupted, the Snapshot will not be able to restore the database because there is a dependency between them. However, you can restore data and objects from the snapshot. This is very useful if the database is big and we want to restore some few objects or some specific rows.

Author
Recent Posts

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases.

He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams.

He also helps with translating SQLShack articles to Spanish

View all posts by Daniel Calbimonte

Latest posts by Daniel Calbimonte (see all)

PostgreSQL tutorial to create a user - November 12, 2023
PostgreSQL Tutorial for beginners - April 6, 2023
PSQL stored procedures overview and examples - February 14, 2023