Database Snapshots in SQL Server

Database Snapshot functionality is available only in Enterprise Edition of SQL Server.
The syntax for creating Database Snapshot is as follows (you need to specify the logical name of every database file of the source database):

CREATE DATABASE snapshot_name
NAME = logical_file_name_of_source_DB,
FILENAME = 'os_file_name_of_snapshot'
) [ ,...n ]
AS SNAPSHOT OF source_database_name

Here is the code for creating a Snapshot Database named AdventureWorks_ss on AdwentureWorks database which logical file name is AdventureWorks_Data:

CREATE DATABASE AdventureWorks_ss ON
( NAME = AdventureWorks_Data, FILENAME =
'c:\' )
AS SNAPSHOT OF AdventureWorks;

The snapshot uses one or more sparse files, which is an empty file that contains no user data and has not yet been allocated disk space for user data. As more and more pages are updated in the source database, the size of the file grows. When a snapshot is taken, the sparse file takes up little disk space:

To restore data from AdventureWorks_ss Snapshot Database to AdventureWorks database use this simple code:

USE master
FROM DATABASE_SNAPSHOT = 'AdventureWorks_ss';

To drop the AdventureWorks_ss Snapshot Database:

DROP DATABASE AdventureWorks_ss;

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">