MSDN – Database Snapshots

http://msdn.microsoft.com/en-us/library/ms175876.aspx

Read-only, static view of a database. Multiple snapshots of the same database can co-exist and reside on the same server instance. Snapshots can be used for reporting purpose.

How ot create:

  1. Ensure you have enough disk space
  2. Example using AdventureWorks2008R2:

    CREATE DATABASE AdventureWorks2008R2_dbss1800 ON
    ( NAME = AdventureWorks2008R2_Data, FILENAME =

    ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\AdventureWorks2008R2_data_1800.ss‘ )

    AS SNAPSHOT OF AdventureWorks2008R2;

    GO

  3. Example using Sales database:

CREATE DATABASE sales_snapshot1200 ON

( NAME = SPri1_dat, FILENAME =

‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\data\SPri1dat_1200.ss’),

( NAME = SPri2_dat, FILENAME =

‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\data\SPri2dt_1200.ss’),

( NAME = SGrp1Fi1_dat, FILENAME =

‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\mssql\data\SG1Fi1dt_1200.ss’),

( NAME = SGrp1Fi2_dat, FILENAME =

‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\data\SG1Fi2dt_1200.ss’),

( NAME = SGrp2Fi1_dat, FILENAME =

‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\data\SG2Fi1dt_1200.ss’),

( NAME = SGrp2Fi2_dat, FILENAME =

‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\data\SG2Fi2dt_1200.ss’)

AS SNAPSHOT OF Sales

GO

 

Advertisements
Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: