Technical Article

Create Snapshot Dynamicly

,

this procedure performe two knids of operations:

either drop the old database snapshots and recreate them

--EXEC ADB.dbo.CreateSnapshot_SP
@SnapShotPath='G:\VSSBC\SnapShots\'

or create a new snapshot considering maultible data file.

--EXEC ADB.dbo.CreateSnapshot_SP
@SnapShotPath='G:\VSSBC\SnapShots\',@DatabaseName='Testdatabase',@Operation =0

 

 

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[CreateSnapshot_SP]
 (
 @SnapShotPath VARCHAR(60),
 @DatabaseName VARCHAR(50) = NULL,
 @Operation INT = 1
 )
AS 
 BEGIN
 DECLARE @SnapShotName VARCHAR(50),
 @drop VARCHAR(200),
 @create VARCHAR(500),
 @path VARCHAR(400),
 @path2 VARCHAR(60)
 IF ( @Operation = 1 ) 
 BEGIN

 DECLARE Ycur CURSOR
 FOR SELECT b.name AS origenal_database_name,
 a.name AS snapshot_name
 FROM sys.databases AS a
 INNER JOIN sys.databases AS b ON a.source_database_id = b.database_id
 WHERE ISNULL(a.source_database_id, 00000) <> 00000

 OPEN Ycur
 FETCH NEXT FROM Ycur INTO @DatabaseName, @SnapShotName
 WHILE( @@FETCH_STATUS = 0 )
 BEGIN
 SET @drop = 'drop database ' + @SnapShotName
 exec ( @drop )
     
                DECLARE mon CURSOR FOR 
 SELECT NAME
 FROM sys.master_files
 WHERE data_space_id <> 0
 AND database_id = ( SELECT database_id
 FROM sys.databases
 WHERE NAME LIKE @DatabaseName
 )
 OPEN mon
 FETCH NEXT FROM mon INTO @path2
 SET @path=''
 WHILE(@@FETCH_STATUS=0)
 BEGIN
                    SET @path =' ( NAME=[' + @path2 + '],FILENAME='''
                        + @SnapShotPath + 'SnapShot' + @path2
                        + '.ss'') ,'+ @path
                    
                    
                    FETCH NEXT FROM mon INTO @path2    
                    
                END            
                         
                    SET @create = 'CREATE DATABASE ' + @DatabaseName
                        + 'SnapShot on '+
                        CONVERT(VARCHAR(400),SUBSTRING(@path,1,len(@path)-1))+' AS SNAPSHOT OF ' + @DatabaseName
                    exec ( @create )
                CLOSE mon
                DEALLOCATE mon
 END

 CLOSE Ycur
 DEALLOCATE Ycur
 END


 IF ( @Operation = 0 ) 
 BEGIN
            
                DECLARE mon CURSOR FOR 
 SELECT NAME
 FROM sys.master_files
 WHERE data_space_id <> 0
 AND database_id = ( SELECT database_id
 FROM sys.databases
 WHERE NAME LIKE @DatabaseName
 )
 OPEN mon
 FETCH NEXT FROM mon INTO @path2
 SET @path=''
 WHILE(@@FETCH_STATUS=0)
 BEGIN
                    SET @path =' ( NAME=[' + @path2 + '],FILENAME='''
                        + @SnapShotPath + 'SnapShot' + @path2
                        + '.ss'') ,'+ @path
                    
                    
                    FETCH NEXT FROM mon INTO @path2    
                    
                END            
                         
                    SET @create = 'CREATE DATABASE ' + @DatabaseName
                        + 'SnapShot on '+
                        CONVERT(VARCHAR(400),SUBSTRING(@path,1,len(@path)-1))+' AS SNAPSHOT OF ' + @DatabaseName
                    exec ( @create )
                CLOSE mon
                DEALLOCATE mon
 END
 END

Rate

2.5 (2)

Share

Share

Rate

2.5 (2)