Archive

Archive for September, 2008

SQLServer 2008 Database Snapshot on Partitioned Database

September 5, 2008 Leave a comment

This topic describes the database snapshot feature for partitioned databases, which was new in Microsoft SQL Server 2005. Database snapshots are available only in SQL Server 2005 Enterprise Edition and later versions. All recovery models support database snapshots.

 

I have a partitioned database with few Files and FileGroups.

We have 24 Secondry Files accociated with 6 FileGroups and one Primary FileGroup.

SecondryFiles

 

Each filegroup consist of 4 secondry Data files .

FileGroups

Creating a database snapshot that has files and file groups, AdventureWork_001_snapshot , on AdventureWorks database. 

All file should be specified while creating a database snapshot of database having files and file group otherwise you will receive the below error:

Msg 5127, Level 16, State 1, Line 4

All files must be specified for database snapshot creation. Missing the file “Logical File Name”.

Script to Create Database Snapshot for Partitioned Database

 

GO

–Create Partitioned Database Snapshot

BEGIN

CREATE DATABASE AdventureWork_001_snapshot ON

( NAME = AdventureWorks2008_Data, FILENAME = 
‘D:\SQLServer 2008\SnapshotDB\AdventureWorks_Data.ss’),
( NAME = SFJan_2008, FILENAME = 
‘D:\SQLServer 2008\SnapshotDB\SFJan_2008.ss’),
( NAME = SFFeb_2008, FILENAME = 
‘D:\SQLServer 2008\SnapshotDB\SFFeb_2008.ss’),
( NAME = SFMar_2008, FILENAME = 
‘D:\SQLServer 2008\SnapshotDB\SFMar_2008.ss’),
( NAME = SFApr_2008, FILENAME = 
‘D:\SQLServer 2008\SnapshotDB\SFApr_2008.ss’),
( NAME = SFMay_2008, FILENAME =
‘D:\SQLServer 2008\SnapshotDB\SFMay_2008.ss’),
( NAME = SFJun_2008, FILENAME = 
‘D:\SQLServer 2008\SnapshotDB\SFJun_2008.ss’),
( NAME = SFJuly_2008, FILENAME = 
‘D:\SQLServer 2008\SnapshotDB\SFJuly_2008.ss’),
( NAME = SFAug_2008, FILENAME = 
‘D:\SQLServer 2008\SnapshotDB\SFAug_2008.ss’),
( NAME = SFSep_2008, FILENAME = 
‘D:\SQLServer 2008\SnapshotDB\SFSep_2008.ss’),
( NAME = SFOct_2008, FILENAME = 
‘D:\SQLServer 2008\SnapshotDB\SFOct_2008.ss’),
( NAME = SFNov_2008, FILENAME = 
‘D:\SQLServer 2008\SnapshotDB\SFNov_2008.ss’),
( NAME = SFDec_2008, FILENAME = 
‘D:\SQLServer 2008\SnapshotDB\SFDec_2008.ss’),
( NAME = SFJan_2009, FILENAME = 
‘D:\SQLServer 2008\SnapshotDB\SFJan_2009.ss’),
( NAME = SFFeb_2009, FILENAME = 
‘D:\SQLServer 2008\SnapshotDB\SFFeb_2009.ss’),
( NAME = SFMar_2009, FILENAME = 
‘D:\SQLServer 2008\SnapshotDB\SFMar_2009.ss’),
( NAME = SFApr_2009, FILENAME = 
‘D:\SQLServer 2008\SnapshotDB\SFApr_2009.ss’),
( NAME = SFMay_2009, FILENAME = 
‘D:\SQLServer 2008\SnapshotDB\SFMay_2009.ss’),
( NAME = SFJun_2009, FILENAME = 
‘D:\SQLServer 2008\SnapshotDB\SFJun_2009.ss’),
( NAME = SFJuly_2009, FILENAME = 
‘D:\SQLServer 2008\SnapshotDB\SFJuly_2009.ss’),
( NAME = SFAug_2009, FILENAME = 
‘D:\SQLServer 2008\SnapshotDB\SFAug_2009.ss’),
( NAME = SFSep_2009, FILENAME = 
‘D:\SQLServer 2008\SnapshotDB\SFSep_2009.ss’),
( NAME = SFOct_2009, FILENAME = 
‘D:\SQLServer 2008\SnapshotDB\SFOct_2009.ss’),
( NAME = SFNov_2009, FILENAME = 
‘D:\SQLServer 2008\SnapshotDB\SFNov_2009.ss’),
( NAME = SFDec_2009, FILENAME = 
‘D:\SQLServer 2008\SnapshotDB\SFDec_2009.ss’)

AS SNAPSHOT OF AdventureWorks

END

 

Database Snasphot gets created once the above script is executed.

Databse snaspshot will have all the files and filegroups.