Can I create Snapshot like this?

Question

Can I create Snapshot like this?

Data Sheet

SSC-Addicted

Points: 445
More actions
April 5, 2007 at 9:37 am

#116550

Hi there,
I hope somebody can assist me out, I am trying to create a SQL job of creating Snapshot every couple of hours of a day for a database(DemoDB),
I would like to automate it so that each time the Snapshot name should be diffirent than before (i.e DemoDB_2007_4_11.snap, DemoDB_2007_4_13.snap etc)
However when I run the following script, I get the error on "CREATE DATABASE @SnapshotDBName" , I beleive I cannot use DB name as variable.

DECLARE
@SnapshotDBNamePart VARCHAR(10),
@SnapshotDBNameString
VARCHAR(20),
@SnapshotDBNameExtention
CHAR(5),
@SnapshotDBName
VARCHAR(50),
@SnapshotDBNameWithFileExtention
VARCHAR(50)
SET
@SnapshotDBName = 'DemoDB_'
SELECT
@SnapshotDBNameString = CONVERT(VARCHAR(4),YEAR(getdate())) + '_' + CONVERT(VARCHAR(2),MONTH(getdate())) + '_' + CONVERT(VARCHAR(2),DATEPART(hh,getdate()))
SET
@SnapshotDBNameExtention = '.snap'
SET
@SnapshotDBName= @SnapshotDBName + @SnapshotDBNameString
SET
@SnapshotDBNameWithFileExtention = @SnapshotDBName + @SnapshotDBNameString + @SnapshotDBNameExtention

BEGIN
CREATE
DATABASE @SnapshotDBName
ON
(NAME = N'DemoDB' , FILENAME = N'F:\Microsoft SQL Server\Snapshots\DemoDB\' + @SnapshotDBNameWithFileExtention + '')
AS
SNAPSHOT OF DemoDB;
END

how can I pass this step?
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 1

Hello,

You can't assign a variable to the base "CREATE" statement. You need to construct a dynamic SQL query to have the desired result as follows:

Declare @StrSQL varchar(8000)

SET @StrSQL = 'CREATE DATABASE ' + @SnapshotDBName + ' ON(NAME = N''DemoDB'', FILENAME = N''F:\Microsoft SQL Server\Snapshots\DemoDB\'' + @SnapshotDBNameWithFileExtension + '''') AS SNAPSHOT OF DemoDB'

Hope this helps.

Thanks

Lucky

Data Sheet SSC-Addicted Points: 445 More actions · Answer 2

Thanks lucky,

I tried but now I got this error:

DECLARE

@SnapshotDBNamePart VARCHAR

(10),

@SnapshotDBNameString VARCHAR

(20),

@SnapshotDBNameExtention

CHAR(5),

@SnapshotDBName VARCHAR

(50),

@SnapshotDBNameWithFileExtention VARCHAR

(50),

@SnapshotFinalQuery

NVARCHAR(2000)

SET

@SnapshotDBNamePart = 'DemoDB_'

SELECT

@SnapshotDBNameString = CONVERT(VARCHAR(4),YEAR(getdate())) + '_' + CONVERT(VARCHAR(2),MONTH(getdate())) + '_' + CONVERT(VARCHAR(2),DATEPART(hh,getdate()))

SET

@SnapshotDBNameExtention = '.snap'

SET

@SnapshotDBName= @SnapshotDBNamePart + @SnapshotDBNameString

SET

@SnapshotDBNameWithFileExtention = @SnapshotDBName + @SnapshotDBNameExtention

--print @SnapshotDBNameWithFileExtention

SET

@SnapshotFinalQuery =

'CREATE DATABASE @SnapshotDBName

ON(NAME = N''DemoDB'' , FILENAME = N''F:\Microsoft SQL Server\Snapshots\DemoDB\'' + @SnapshotDBNameWithFileExtention + '''')

AS SNAPSHOT OF DemoDB;'

print

@SnapshotFinalQuery

BEGIN

EXEC

sp_executesql @SnapshotFinalQuery

SET

@SnapshotDBNamePart = ''

SET

@SnapshotDBNameExtention = ''

SET

@SnapshotDBName = ''

SET

@SnapshotDBNameWithFileExtention = ''

SET

@SnapshotFinalQuery = ''

END

Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near '@SnapshotDBName'.

any more clue?

Luke79 SSCrazy Points: 2571 More actions · Answer 3

try this instead:

declare @SnapshotDBName sysname

Amit Jethva SSCarpal Tunnel Points: 4707 More actions · Answer 4

SET @SnapshotFinalQuery =

'CREATE DATABASE ' + @SnapshotDBName + '

ON(NAME = N''DemoDB'' , FILENAME = N''F:\Microsoft SQL Server\Snapshots\DemoDB\'' + @SnapshotDBNameWithFileExtention + '''')

AS SNAPSHOT OF DemoDB;'

print @SnapshotFinalQuery

BEGIN

-- Amit

"There is no 'patch' for stupidity."

Download the Updated SQL Server 2005 Books Online.