Scripted restore to another Db

doddg

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

Eddie D

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

doddg

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

Eddie D

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