Backing up to Secondary Server with Automatic Standby

redg8

Hi,
I have 2 x SQL 2005 Servers (Primary + Secondary) both running SQL Backup 5.
I want to have the Secondary SQL Server have a standby copy of the Primary Database restored on a schedule.

How can I do this? It would be very helpful for DRP.

Regards
Jay

petey

You could explore the use of log shipping to maintain a standby copy of the primary database. The SQL Backup GUI offers a log shipping wizard to set things up.

Or if your database is using the simple recovery model, you could set up a scheduled job on the primary server to back up the database periodically, and another job on the secondary server to restore the database. The COPYTO and MOVETO options may be of use here. See the SQL Backup help file for details, in the topics 'The BACKUP command' and 'The RESTORE command'.

jerryhung

With Log Shipping, you can set it up in SSMS (right click on a DB, follow the wizard), and auto restore on 1-to-N Secondary servers

Note: Secondary servers can only be in either RECOVERY mode (no use other than hot standby), or STANDBY mode (read-only, so you can select on it, use it for reporting purpose, but cannot perform write operation)

It'd be interesting to test SQL Backup's Log Shipping feature (didn't even know about it until I did the survey yesterday)

btw, download RedGate's Log Shipping monitor tool, it's pretty cool

redg8

Ok I have setup the SQL Backup to send a copy to a network location. Can you setup this network location to purge after a certain amount of days, like you can with the primary location of the *.sqb files? Or do you need to setup a VBscript to do this. If so, where do I get a script like this?
Thanks in advance. J

jerryhung

redg8 wrote:

Ok I have setup the SQL Backup to send a copy to a network location. Can you setup this network location to purge after a certain amount of days, like you can with the primary location of the *.sqb files? Or do you need to setup a VBscript to do this. If so, where do I get a script like this?
Thanks in advance. J

Last time I tried to find it, I don't see this option in GUI, but I think it's there in the command line option

However, you can either write a VBS, or simpler, setup a SQL Maintaenance Plan with Cleanup Task, deleting *.sqb extension files after X days, and schedule the task

petey

EXEC master..sqlbackup '-sql "BACKUP LOG AdventureWorks TO ... WITH COPYTO = [\\share\files], FILEOPTIONS = 1, ERASEFILES = 7" '

will copy log backups to \\share\files, and erases files older than 7 days, in both the local and remote folders.

EXEC master..sqlbackup '-sql "BACKUP LOG AdventureWorks TO ... WITH COPYTO = [\\share\files], FILEOPTIONS = 1, ERASEFILES = 7, ERASEFILES_REMOTE =  5" '

will copy log backups to \\share\files, erase local files older than 7 days, and remote files older than 5 days. Note that ERASEFILES_REMOTE is only available in 5.3.

qafoo

petey wrote:

Note that ERASEFILES_REMOTE is only available in 5.3.

Where in 5.3 is this option GUI or cmdline??

I can't see it in my version 5.3.0.178.

Found the answer.

Matthew Flatt

The ERASEFILES_REMOTE option is only available in the command line at this point.

More information can be found in the help file under :

The SQL Backup Toolkit>Toolkit Syntax>The BACKUP Command