9 out of 14 rated this helpful - Rate this topic

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

SQL Server 2005

Any user who can create a database can create a database snapshot. The only way to create a snapshot is to use Transact-SQL.

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

To create a database snapshot

Based on the current size of the source database, 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\MSSQL.1\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\mssql.1\mssql\data\SPri1dat_1200.ss'),
( NAME = SPri2_dat, FILENAME = 
'C:\Program Files\Microsoft SQL Server\mssql.1\mssql\data\SPri2dt_1200.ss'),
( NAME = SGrp1Fi1_dat, FILENAME = 
'C:\Program Files\Microsoft SQL Server\mssql.1\mssql\data\SG1Fi1dt_1200.ss'),
( NAME = SGrp1Fi2_dat, FILENAME = 
'C:\Program Files\Microsoft SQL Server\mssql.1\mssql\data\SG1Fi2dt_1200.ss'),
( NAME = SGrp2Fi1_dat, FILENAME = 
'C:\Program Files\Microsoft SQL Server\mssql.1\mssql\data\SG2Fi1dt_1200.ss'),
( NAME = SGrp2Fi2_dat, FILENAME = 
'C:\Program Files\Microsoft SQL Server\mssql.1\mssql\data\SG2Fi2dt_1200.ss')
AS SNAPSHOT OF Sales
GO

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD

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

A. Creating a snapshot on the AdventureWorks database

B. Creating a snapshot on the Sales database

Tasks

Other Resources

Help and Information

Community Additions