What are the challenges you face when working across database platforms? Take the survey
Options

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

Comments

  • Options
    Eddie DEddie D Posts: 1,789 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
  • Options
    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
  • Options
    Eddie DEddie D Posts: 1,789 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
Sign In or Register to comment.