O'Reilly logo

Microsoft SQL Server 2012 Bible by Aaron Nelson, Jose Chinchilla, Patrick LeBlanc, Jorge Segarra, Adam Jorgensen

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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.

24.2
The ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required