Backing up to Secondary Server with Automatic Standby

redg8redg8 Posts: 3
edited September 11, 2008 12:20PM in SQL Backup Previous Versions
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

Comments

  • peteypetey Posts: 2,358 New member
    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'.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 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
    Jerry Hung
    DBA, MCITP
  • 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
  • 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
    Jerry Hung
    DBA, MCITP
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 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.
    Life is plain and simple
  • 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
    Matthew Flatt
    Redgate Foundry
Sign In or Register to comment.