Create Snapshot / Restore database

It's a bit hard to figure out the creation and restore of a database with the snapshot machanism.
It's nothing magic if you know, how to set the parameters.

The syntax:

Code

USE [Tools]

if object_id('ProcessSnapshot', 'P') is not null
drop procedure ProcessSnapshot
GO

--==============================================================
-- Author: Joachim Mutter
-- Date: 23.06.2008
-- Changed:
-- Description
-- This function creates/restores/deletes a snapshot of a
-- database.
-- Parameters
-- @DBName varchar(128)
-- This is the database names
-- ,@Name AS VARCHAR(128)
-- This is the logical name, could be retrieved by sp-helpdb 'DBName' or via properties dialog
-- ,@Filepath AS VARCHAR(128)
-- The path of the phyical file location, with no postfixed backslash
-- ,@mode as smallint
-- 1 : Create snapshot
-- 2 : Restore database
-- 3 : Drop snapshot
-- ,@szDate as varchar(20)
-- If given, use this as Snapshot name exte3nsion, if not, create it dynamically
--
-- Call it with the following paramteres
-- EXEC tools.dbo.ProcessSnapshot
-- 'HTS-Analysis-DB'
-- ,'HTS-Analysis-DB-Test'
-- ,'F:\Daten\SQLServer\DATA'
-- , 1
-- , 20080622
--==============================================================
CREATE PROCEDURE ProcessSnapshot(
@DBName varchar(128)
,@Name AS VARCHAR(128)
,@Filepath AS VARCHAR(128)
,@mode as SMALLINT
,@szDate AS VARCHAR(20) = null
)AS
BEGIN

DECLARE @sqlcmd NVARCHAR(4000)

IF @szDate IS null
SET @szDate = right('0000' + convert(varchar(4), year(getdate())),4) + right('00' + convert(varchar(2), month(getdate())),2) + right('00' + convert(varchar(2), day(getdate())),2)

IF @mode = 1 BEGIN
PRINT 'Create SNAPSHOT : [' + @DBName + '_' + @szDate + ']'
SET @sqlcmd = '
CREATE DATABASE [' + @DBName + '_' + @szDate + '] ON
( NAME = N''' + @Name + ''', FILENAME = N''' + @filepath + '\' + @DBName + '_' + @szDate + '.ss'' )
AS SNAPSHOT OF [HTS-Analysis-DB]
'
EXECUTE sp_executesql @sqlCmd
END ELSE IF @Mode = 2 BEGIN
PRINT 'Restore SNAPSHOT : [' + @DBName + '_' + @szDate + ']'
SET @sqlCmd = '
DECLARE @OFF_DB sysname;
DECLARE @Mode INT;

SET @OFF_DB = ''' + @DBName + '''

Begin
declare @database sysname
,@Login sysname
,@Host sysname
,@OsUser sysname
,@SP_ID smallint
,@CRLF varchar(2)
,@SQL_Stmt nVarchar(500)
,@NotifyMsg nVarchar(500)

declare cr_processes cursor for
select spid,
db_name(dbid) as DbName,
loginame as Login,
hostname as Host,
nt_username as OSUser
from master..sysprocesses
where spid >=50 AND spid <> @@SPID

-- close/kill all connections for this database
open cr_processes
while 1 = 1 begin
fetch cr_processes INTO @SP_ID, @database, @Login, @Host, @OSUser
if @@FETCH_STATUS <> 0 break

IF lower(@database) = lower(@OFF_DB) BEGIN
select @SQL_Stmt = N''kill '' + cast( @SP_ID as varchar(10))
execute sp_executesql @SQL_Stmt
END
end
close cr_processes
deallocate cr_processes
End
'
EXECUTE sp_executesql @sqlCmd
SET @sqlcmd = 'restore database [' + @DBName + '] from Database_snapshot= ''' + @DBName + '_' + @szDate + ''''
EXECUTE sp_executesql @sqlCmd
END ELSE IF @Mode = 3 BEGIN
PRINT 'Delete SNAPSHOT : [' + @DBName + '_' + @szDate + ']'
SET @sqlcmd = 'DROP DATABASE [' + @DBName + '_' + @szDate + ']'
EXECUTE sp_executesql @sqlCmd
END
END


Submitted by	Category	Review Cycle	.	Public
Joachim Mutter/sysarc on 06/23/2008 at 10:57 AM	SQLServer\Code