Microsoft SQL Server 2012 Bible

Using Database Snapshots

You can create, query, or drop database snapshots just as with regular databases. The main difference is that you can create them only from an existing database as a point-in-time, read-only database. After you create a snapshot, it can be queried the same way as regular databases. You cannot perform updates, inserts, or deletions on the data or make schema changes (adding or removing tables and columns).

You can use snapshots to revert a database to a specific point in time using similar syntax to what you would use to RESTORE the database from a database backup.

The following sections describe database snapshot operations in more detail.

Creating a Database Snapshot

You can create database snapshots only using the Transact-SQL command CREATE DATABASE <. . .> ON <. . .> AS SNAPSHOT OF <. . .>.

Note

SQL Server Management Studio does not have any graphical interface to create database snapshots. You must use Transact-SQL.

Following are the step-by-step instructions to create a database snapshot of the AdventureWorks2012 sample database:

1. Find the information about the files in the source database. Use the following command to find the information about the files in the AdventureWorks2012 database.

USE AdventureWorks2012;
GO
EXECUTE sp_helpfile;
GO

The results of this query are shown in Figure 24.2.

Figure 24.2 Database file information.

The ...