How to: Create a Database Snapshot (Transact-SQL)

MSDN Library
Servers and Enterprise Development
SQL Server
SQL Server 2008
Product Documentation
SQL Server 2008 Books Online
Database Engine
Development
Designing and Implementing Structured Storage
Designing and Implementing Structured Storage How-to Topics
Databases How-to Topics
Database Snapshots How-to Topics

How to: View a Database Snapshot (SQL Server Management Studio)
How to: Drop a Database Snapshot (Transact-SQL)
How to: Revert a Database to a Database Snapshot (Transact-SQL)

How to: Create a Database Snapshot (Transact-SQL)
SQL Server 2005

SQL Server 2008 Books Online (October 2009)

How to: Create a Database Snapshot (Transact-SQL)

The only way to create a database snapshot is to use Transact-SQL. Any user who can create a database can create a database snapshot; however, to create a snapshot of a mirror database, you must be a member of the sysadmin fixed server role.

Note:
For considerations on naming database snapshots, timing their creation, and limiting their number, see Creating a Database Snapshot.

To create a database snapshot

Ensure that you have sufficient disk space to hold the database snapshot. The maximum size of a database snapshot is the size of the source database at snapshot creation.
Issue a CREATE DATABASE statement on the files using the AS SNAPSHOT OF clause. Creating a snapshot requires specifying the logical name of every database file of the source database. For a formal description of the syntax for creating a database snapshot, see CREATE DATABASE (Transact-SQL).

Note:
When you create a database snapshot, log files, offline files, restoring files, and defunct files are not allowed in the CREATE DATABASE statement.

Note:
When you create a database snapshot, log files, offline files, restoring files, and defunct files are not allowed in the CREATE DATABASE statement.

Example

This section contains examples of creating a database snapshot.

A. Creating a snapshot on the AdventureWorks database

This example creates a database snapshot on the AdventureWorks database. The snapshot name, AdventureWorks_dbss_1800, and the file name of its sparse file, AdventureWorks_data_1800.ss, indicate the creation time, 6 P.M (1800 hours).

CREATE DATABASE AdventureWorks_dbss1800 ON
( NAME = AdventureWorks_Data, FILENAME = 
'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\AdventureWorks_data_1800.ss' )
AS SNAPSHOT OF AdventureWorks;
GO

Note:
The .ss extension used in the examples is arbitrary.

B. Creating a snapshot on the Sales database

This example creates a database snapshot, sales_snapshot1200, on the Sales database. This database was created in the example, "Creating a database that has filegroups," in CREATE DATABASE (Transact-SQL).

--Creating sales_snapshot1200 as snapshot of the
--Sales database:
CREATE DATABASE sales_snapshot1200 ON
( NAME = SPri1_dat, FILENAME = 
'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\data\SPri1dat_1200.ss'),
( NAME = SPri2_dat, FILENAME = 
'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\data\SPri2dt_1200.ss'),
( NAME = SGrp1Fi1_dat, FILENAME = 
'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\mssql\data\SG1Fi1dt_1200.ss'),
( NAME = SGrp1Fi2_dat, FILENAME = 
'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\data\SG1Fi2dt_1200.ss'),
( NAME = SGrp2Fi1_dat, FILENAME = 
'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\data\SG2Fi1dt_1200.ss'),
( NAME = SGrp2Fi2_dat, FILENAME = 
'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\data\SG2Fi2dt_1200.ss')
AS SNAPSHOT OF Sales
GO

Community Content

Only System administrators can create a snapshot of non-recovered mirrored database.

Added by:Chandan_SQLDBA

Please note that only a user that is a member of sysadmin fixed server role, can create a snapshot of the non-recovered mirror database on the mirror server.

Ramu Konidena
Microsoft SQL Server Support.

Databse Snapshot Features is available only in SQL Enterprise Edition not in Standard Edition

Chandan Gupta
SQL_DBA
IBM Technologies

Only available for Enterprise Edition

Added by:Uwe Ricken

Please note that creation of snapshots will only be available in the Enterprise Edition of SQL Server 2005 / 2008.

Uwe Ricken
SQL DBA
db Berater GmbH (Germany)

Luan.Moreno

Added by:Thomas Lee

This article solve all my problems

Congratulations
It's so good to participate the TECH NET

A. Creating a snapshot on the AdventureWorks database

B. Creating a snapshot on the Sales database

Tasks

Other Resources

Help and Information