Scripted restore to another Db

doddgdoddg Posts: 3
edited May 25, 2011 7:11AM in SQL Backup Previous Versions
Hi to all,

new to the forum and also SQL Backup so please excuse any newbie errors :)

I use Backup 6 for daily backups of our live Db and would like to setup a job that restores the previous days backup to another Db (on the same server) that can be used for statistical reporting.

I don't know if this is possible within Backup 6, or if it must be done with a script. Any help greatly appreciated.

Thanks,

Graham
· Share on Twitter

Comments

  • Eddie DEddie D Posts: 1,803 Rose Gold 5
    Hi Graham

    Thank you for your post into the forum.

    What you are seeking can be achieved, please use the example script below and modify it to suit your needs:

    DECLARE @errorcode INT
    DECLARE @sqlerrorcode INT
    EXECUTE master..sqlbackup N'-SQL "RESTORE DATABASE [<database_name>] FROM DISK = ''<path to backup file>.sqb'' WITH RECOVERY, MOVE ''<database_name>_Data'' TO ''<path to database mdf file>.MDF'', MOVE ''<database_name>_Log'' TO ''<path to database ldf file>.LDF''"'
    IF (@errorcode >= 500) OR (@sqlerrorcode <> 0)
    BEGIN
    RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @errorcode, @sqlerrorcode)
    END

    Further information on the Restore command can be found in the SQL Backup help information for the Restore Command using this LINK:

    Many Thanks
    Eddie
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
    · Share on Twitter
  • doddgdoddg Posts: 3
    eddie davis wrote:
    Hi Graham

    Thank you for your post into the forum.

    What you are seeking can be achieved, please use the example script below and modify it to suit your needs:

    DECLARE @errorcode INT
    DECLARE @sqlerrorcode INT

    ---- snip snip

    Many Thanks
    Eddie

    Hi Eddie,

    thanks for the script.

    One question, can I build this into the backup script (similar to what I do with the Enterprise Manager). Then I can schedule the backup to run, and as long as the backup is successful start the second step to restore the backup to the archive database.

    Regards,

    Graham
    · Share on Twitter
  • Eddie DEddie D Posts: 1,803 Rose Gold 5
    Thank you for your reply.

    The choice is yours, you can create a new SQL Server Agent Job and add the scripted code into a new job step. Configure the job to start after the backup job. Or add a second job step to an existing job and perform the restore on completion of the previous step.

    Many Thanks
    Eddie
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
    · Share on Twitter
Sign In or Register to comment.