usp_createsnapshot

--USE database_for_deploy
GO
IF 'usp_createsnapshot' NOT IN (SELECT NAME FROM sys.objects WHERE TYPE = 'P')
BEGIN
 EXEC ('
 CREATE PROCEDURE usp_createsnapshot AS
 SELECT ''This is a stub procedure, implementation of it will be created by an ALTER statement''
 ')
 PRINT 'Procedure usp_createsnapshot does not exists, creating a stub procedure before executing create script...'
END
GO
-- =============================================
-- Author:  Mark Broadbent
-- Create date: 13/02/2007
-- Last updated date: 14/02/2010
-- Description: Procedure usp_createsnapshot creates an automatic snapshot on specified database
-- Version: 0.9
-- Dependencies: NONE
-- =============================================
ALTER PROC [dbo].[usp_createsnapshot]
	@dbname sysname = '', --required option (database to create snapshot on)
	@snapshotname sysname ='' OUTPUT, --generated snapshot name, output parameter
	@help bit = 0 --optional option (view help)
AS
SET NOCOUNT ON

IF @help = 1
BEGIN
 PRINT 'Help Specified.'
 GOTO printoptions
END

IF @dbname NOT IN (SELECT name FROM master.sys.databases) OR LEN(@dbname) = 0
BEGIN
 PRINT 'Database [' + @dbname + '] specified cannot be found.'
 GOTO printoptions
END

IF @dbname IN ('master','model','tempdb')
BEGIN
 PRINT 'snapshot creation on database [' + @dbname + '] is not allowed.'
 GOTO printoptions
END

DECLARE @now DATETIME
DECLARE @ssname SYSNAME
DECLARE @cmd VARCHAR(MAX)
DECLARE @uniqueid sysname
SET @now = GETDATE()

--create a unique name that we will use in the snapshot name
--and within each datafile to avoid conflict with others
SET @uniqueid = REPLACE(STR(DATEPART(yyyy, @now),4)
	+ STR(DATEPART(mm, @now),2) + STR(DATEPART(dd, @now),2)
	+ STR(DATEPART(hh, @now),2) + STR(DATEPART(mi, @now),2)
	+ STR(DATEPART(ss, @now),2), ' ', '0')

--dbname becomes __SX
SET @ssname = @dbname + '_' + @uniqueid + '_SX'

SET @cmd = 'CREATE DATABASE [' + @ssname + '] ON '
--loop through datafiles and assign a unique name
SELECT @cmd = @cmd +CHAR(10)+'(NAME = ''' + RTRIM(name)
	+ ''', FILENAME = ''' + RTRIM(physical_name)
	+ @uniqueid + '_sx''),'
	FROM sys.master_files WHERE type <> 1 -- ignore logfile since snapshots do not create one
	AND database_id = db_id(@dbname)
SET @cmd =  LEFT(@cmd,LEN(@cmd)-1) --take away extra trailing comma left from SELECT file assignment
SET @cmd = @cmd + CHAR(10) + ' AS SNAPSHOT OF [' + @dbname +']' --complete statement
PRINT @cmd
EXEC (@cmd)
SET @snapshotname = @ssname
RETURN
printoptions:
SET @snapshotname = NULL
PRINT
'
	@dbname sysname = '''', --required option (database to create snapshot on)
	@snapshotname sysname ='''' OUTPUT, --generated snapshot name, output parameter
	@help bit = 0 --optional option (view help)
======================================================
'
GO
SET ANSI_PADDING OFF
PRINT '======================================================
IF 'usp_createsnapshot' IN (SELECT NAME FROM sysobjects WHERE TYPE = 'P')
BEGIN
 PRINT 'Procedure usp_createsnapshot successfully altered...'
END
GO

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