Generate Script to Create Database Snapshot
Dejan Sunderic
Database Snapshots have to be created using Transact-SQL statement that references all file groups of existing database and converts them to sparse files:
CREATE DATABASE piw60SLDW_200603 ON
( NAME = N'iwDW_Data', FILENAME = N'G:\Sql\piw60SLDW_200603.ssh' ),
( NAME = N'FG2000', FILENAME = N'g:\Sql\piw60SLDW_FG2000_200603.ssh'),
( NAME = N'FG2001', FILENAME = N'g:\Sql\piw60SLDW_FG2001_200603.ssh' ),
( NAME = N'FG2002', FILENAME = N'G:\Sql\piw60SLDW_FG2002_200603.ssh' ),
( NAME = N'FG2003', FILENAME = N'G:\Sql\piw60SLDW_FG2003_200603.ssh' ),
( NAME = N'FG2004', FILENAME = N'G:\Sql\piw60SLDW_FG2004_200603.ssh' ),
( NAME = N'FG2005', FILENAME = N'G:\Sql\piw60SLDW_FG2005_200603.ssh' ),
( NAME = N'FG2006', FILENAME = N'g:\Sql\piw60SLDW_FG2006_200603.ss'h ),
( NAME = N'FGArchive', FILENAME = N'G:\Sql\piw60SLDW_FGArchive_200603.ssh' ),
( NAME = N'iwInput_data', FILENAME = N'G:\Sql\piw60SLDW_iwInput_data_200603.ssh' ),
( NAME = N'iwSys_Data', FILENAME = N'G:\Sql\piw60SLDW_iwSys_Data_200603.ssh' ),
( NAME = N'iwDW_Secondary_Data', FILENAME = N'G:\Sql\piw60SLDW_Secondary_Data_200603.ssh' )
AS SNAPSHOT OF piw60SLDW
GO
Naturally, after creating the fist one, I got lazy and I create a stored procedure that would automate that process:
alter PROCEDURE dbo.ap_Snapshot_Generate
@db sysname,
@SnapshotId sysname,
@SnapshotExt sysname = '.ssh',
@sql nvarchar(max) output
/*
declare @s nvarchar(max)
exec ap_Snapshot_Generate N'piw60SLDW', N'_200604', '.ssh',@s output
select @s
*/
as
SET nocount on
SET xact_abort on
DECLARE @fg sysname,
@physical_name sysname,
@countFG int,
@i int,
@physical_name_new sysname
SET @sql = 'CREATE DATABASE '+ @db + @SnapshotId + ' ON
'
SET @i = 1
SELECT name, physical_name, identity(int, 1,1) id
INTO #fg
FROM sys.database_files
WHERE 1=0
DECLARE @s nvarchar(max)
SET @s = '
insert #fg (name, physical_name)
SELECT name, physical_name
FROM ' + @db + '.sys.database_files
where type_desc <> ''log'' '
EXEC sp_executesql @s
SELECT @countFG = Max(id)
FROM #fg
WHILE @i <= @countFG
BEGIN
SELECT @fg = name,
@physical_name = physical_name
FROM #fg
WHERE id = @i
SET @physical_name_new = Left(@physical_name, Len(@physical_name) - 4) + @snapshotID + @SnapshotExt
SET @sql = @sql + ' ( NAME = N''' + @fg + ''', FILENAME = N''' + @physical_name_new + ''' ),
'
SET @i = @i + 1
end
-- remove last comma
SET @sql = Left(@sql, Len(@sql) -3)
SET @sql = @sql + '
AS SNAPSHOT OF ' + @db
return
The procedure is useful as a tool that generates script for you, but more importantly, it illustrates how to dynamical create a script based on SQL Server 2005 metadata.
Copyright ©2006 Dejan Sunderic