SQL Server 2008 Books Online (November 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.

ms175876.note(en-us,SQL.100).gifNote:
For considerations on naming database snapshots, timing their creation, and limiting their number, see Creating a Database Snapshot.

To create a database snapshot

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

  2. 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).

    ms175876.note(en-us,SQL.100).gifNote:
    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
ms175876.note(en-us,SQL.100).gifNote:
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
See Also

Tasks

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

Other Resources

CREATE DATABASE (Transact-SQL)
Tutorials

Help and Information

Getting SQL Server 2008 Assistance
Tags :


Community Content

Tai Yee - MSFT
Only System administrators can create a snapshot of non-recovered mirrored database.
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

Uwe Ricken
Only available for Enterprise Edition
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)
Tags :

Thomas Lee
Luan.Moreno
This article solve all my problems

Congratulations
It's so good to participate the TECH NET

Tags : compliement

Ramu_K
OS Errors 1450 and 665 are reported for database data files
Please be aware of the following KB when creating a database snapshot:

OS Errors 1450 and 665 are reported for database data files http://support.microsoft.com/?kbid=2002606

These errors happen when:
  • You create a database snapshot on a large database. After this you perform numerous data modification operations or maintenance operations in the source database.
  • You create a database snapshot on a mirror database
  • You execute DBCC CHECKDB family of commands to check the consistency of a large database and you also perform a large number of data changes in that database.
Tags :

Page view tracker