Daniel Calbimonte

SQL Server Database Snapshots

October 30, 2014 by

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:

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

  1. We use the word AS SNAPSHOT OF DATABASE_NAME to specify the name of the database that requires a snapshot.
  2. 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:

Adwentureworks snapshot
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:

Read only message is shown - No row was updated
Figure 2

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

Use T-SQL query to update tables in the database snapshot
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.

Database Snapshot properties
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:

Adventureworks2012 database properties
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

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:

Generating the CREATE PROCEDURE from the Snapshot Database
Figure 6

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

Replace With

Once replaced press F5 to execute the script:

Executed 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:

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

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.

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

    Views of the AdventureWorks database
    Figure 8

  2. Then we will drop all the views:

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

    All the views are removed
    Figure 9

  3. 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.

  4. Finally, restore the AdventureWorks Database from the snapshot:

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

Views should be back
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:

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

Database Snapshot created
Figure 11

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

Creating New Job
Figure 12

Specify a Name and optionally a description of the job:

Specifying Job name and description
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):

Choosing Step name and T-SQL type
Figure 14

Finally, schedule the job every month:

Scheduling New 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.

See more

For SQL Server recovery, consider ApexSQL Recover, a tool that recovers deleted and truncated data, objects and data lost due to drop operations, restores deleted and reads online BLOBs as files

References


Daniel Calbimonte

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 is also a writer for SQL Server training material for certification exams.

View all posts by Daniel Calbimonte
Daniel Calbimonte
SQL Recovery

About 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 is also a writer for SQL Server training material for certification exams. View all posts by Daniel Calbimonte

7,362 Views