Post reply

packaging a backup/restore script within a stored procedure

  • June 20, 2014 at 3:30 pm

    #308473

    I'm not new to SQL at all, but I'm completely new to backup/restore TSQL

    I have the following script, which backs up 2 databases and restores them under different database names.

    the script runs fine as-is, but I cannot seem to package it within a create stored procedure statement.

    here's the script. if I attempt to wrap create procedure <name> AS begin....end around it, the statement simply executes.

    --backup LGTY_QA_01, restore to LGTY_DV_01

    BACKUP DATABASE LGTY_QA_01

    TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\LGTY_QA_01.bak'

    WITH FORMAT, CHECKSUM

    GO

    ALTER DATABASE LGTY_DV_01 SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    GO

    --USE master

    EXEC sp_detach_db 'LGTY_DV_01', 'true'

    RESTORE DATABASE LGTY_DV_01

    FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\LGTY_QA_01.bak'

    WITH MOVE 'LGTY_QA_01_dat'

    TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\LGTY_DV_01.mdf',

    MOVE 'LGTY_QA_01_log'

    TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\LGTY_DV_01.log',

    REPLACE

    GO

    ALTER DATABASE LGTY_DV_01 SET MULTI_USER

    GO

    --backup LGTY_QA_02, restore to LGTY_DV_02

    BACKUP DATABASE LGTY_QA_02

    TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\LGTY_QA_02.bak'

    WITH FORMAT, CHECKSUM

    GO

    ALTER DATABASE LGTY_DV_02 SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    GO

    EXEC sp_detach_db 'LGTY_DV_02', 'true'

    RESTORE DATABASE LGTY_DV_02

    FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\LGTY_QA_02.bak'

    WITH MOVE 'LGTY_QA_02_dat'

    TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\LGTY_DV_02.mdf',

    MOVE 'LGTY_QA_02_log'

    TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\LGTY_DV_02.log',

    REPLACE

    GO

    ALTER DATABASE LGTY_DV_02 SET MULTI_USER

    GO

  • June 24, 2014 at 3:24 pm

    #1724484

    You can't have a

    GO

    within a stored procedure. The first GO ends the stored procedure definition.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 2 posts - 1 through 2 (of 2 total)

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