Creating database snapshot - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Creating database snapshot

Author

Topic

InfraDBA
Starting Member

38 Posts

Posted - 2011-06-13 : 08:37:15

I want to create a dynamic SQL in order to take snapshot of all the databases present in the database.I am unable to select the name of the files.The code snippet I am using is as below.

et @SQLCMD='CREATE DATABASE '+@DBName_SNP+' ON
(NAME=''System_Data'', FILENAME=''F:\DATA\ SNP\'+@DBName_SNP+'_System_Data.mdf''),
(NAME=''Data2'', FILENAME=''F:\DATA\ODS SNP\'+@DBName_SNP+'_Data.mdf''),
(NAME=''Data_Index'', FILENAME=''F:\DATA\ SNP\'+@DBName_SNP+'_Index.mdf'')
AS SNAPSHOT OF '+@DBName_Source
EXEC(@SQLCMD)

Is there a way?

mikgri
Starting Member

39 Posts

Posted - 2011-06-14 : 15:27:09

Try this:

declare @SQLCMD nvarchar(1000), @DBName_SNP nvarchar(128), @DBName_Source nvarchar(128)
set @DBName_Source='DatabaseName'
set @DBName_SNP=@DBName_Source+'_SNP'

-- Drop snapshot database if it already exists
set @SQLCMD='IF EXISTS (
SELECT name
FROM sys.databases
WHERE name = '''+@DBName_SNP+'''
)
DROP DATABASE '+@DBName_SNP+';'
Exec @SQLCMD

-- Create the snapshot database
set @SQLCMD='CREATE DATABASE DBName_SNP ON
( NAME = '+@DBName_Source+', FILENAME =
''F:\Data\SNP\'+@DBName_SNP+'.ss'' )
AS SNAPSHOT OF '+@DBName_Source+';'
Exec @SQLCMD

mikgri
Starting Member

39 Posts

Posted - 2011-06-14 : 15:42:59

Replace "Exec @SQLCMD" in above code with "exec sp_executesql @SQLCMD"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources