Backing up to Secondary Server with Automatic Standby

• 
  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'.

  petey July 29, 2008 23:09

  - 0 +
• 
  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

  jerryhung July 30, 2008 10:37

  - 0 +
• 
  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 July 31, 2008 19:12

  - 0 +
• 

  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

  jerryhung August 01, 2008 09:13

  - 0 +
• 

  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.

  petey August 01, 2008 10:15

  - 0 +
• 

  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.

  qafoo September 10, 2008 16:51

  - 0 +
• 
  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 September 11, 2008 12:20

  - 0 +

Add comment

Please sign in to leave a comment.