Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
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.
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
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)
SQL Server Management Studio Tutorial