SQLServer 2008 Database Snapshot on Partitioned Database

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 2008SnapshotDBAdventureWorks_Data.ss’),
( NAME = SFJan_2008, FILENAME = 
‘D:SQLServer 2008SnapshotDBSFJan_2008.ss’),
( NAME = SFFeb_2008, FILENAME = 
‘D:SQLServer 2008SnapshotDBSFFeb_2008.ss’),
( NAME = SFMar_2008, FILENAME = 
‘D:SQLServer 2008SnapshotDBSFMar_2008.ss’),
( NAME = SFApr_2008, FILENAME = 
‘D:SQLServer 2008SnapshotDBSFApr_2008.ss’),
( NAME = SFMay_2008, FILENAME =
‘D:SQLServer 2008SnapshotDBSFMay_2008.ss’),
( NAME = SFJun_2008, FILENAME = 
‘D:SQLServer 2008SnapshotDBSFJun_2008.ss’),
( NAME = SFJuly_2008, FILENAME = 
‘D:SQLServer 2008SnapshotDBSFJuly_2008.ss’),
( NAME = SFAug_2008, FILENAME = 
‘D:SQLServer 2008SnapshotDBSFAug_2008.ss’),
( NAME = SFSep_2008, FILENAME = 
‘D:SQLServer 2008SnapshotDBSFSep_2008.ss’),
( NAME = SFOct_2008, FILENAME = 
‘D:SQLServer 2008SnapshotDBSFOct_2008.ss’),
( NAME = SFNov_2008, FILENAME = 
‘D:SQLServer 2008SnapshotDBSFNov_2008.ss’),
( NAME = SFDec_2008, FILENAME = 
‘D:SQLServer 2008SnapshotDBSFDec_2008.ss’),
( NAME = SFJan_2009, FILENAME = 
‘D:SQLServer 2008SnapshotDBSFJan_2009.ss’),
( NAME = SFFeb_2009, FILENAME = 
‘D:SQLServer 2008SnapshotDBSFFeb_2009.ss’),
( NAME = SFMar_2009, FILENAME = 
‘D:SQLServer 2008SnapshotDBSFMar_2009.ss’),
( NAME = SFApr_2009, FILENAME = 
‘D:SQLServer 2008SnapshotDBSFApr_2009.ss’),
( NAME = SFMay_2009, FILENAME = 
‘D:SQLServer 2008SnapshotDBSFMay_2009.ss’),
( NAME = SFJun_2009, FILENAME = 
‘D:SQLServer 2008SnapshotDBSFJun_2009.ss’),
( NAME = SFJuly_2009, FILENAME = 
‘D:SQLServer 2008SnapshotDBSFJuly_2009.ss’),
( NAME = SFAug_2009, FILENAME = 
‘D:SQLServer 2008SnapshotDBSFAug_2009.ss’),
( NAME = SFSep_2009, FILENAME = 
‘D:SQLServer 2008SnapshotDBSFSep_2009.ss’),
( NAME = SFOct_2009, FILENAME = 
‘D:SQLServer 2008SnapshotDBSFOct_2009.ss’),
( NAME = SFNov_2009, FILENAME = 
‘D:SQLServer 2008SnapshotDBSFNov_2009.ss’),
( NAME = SFDec_2009, FILENAME = 
‘D:SQLServer 2008SnapshotDBSFDec_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.

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *