Can I create Snapshot like this?

  • 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

  • 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

  • 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?

  • try this instead:

    declare @SnapshotDBName sysname

  • 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.

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

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