Backing up to Secondary Server with Automatic Standby

Comments

• petey Redgate › Posts: 2,358 New member

  July 29, 2008 11:09PM

  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

  0 · Share on Twitter
• jerryhung Posts: 138

  July 30, 2008 10:37AM

  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

  0 · Share on Twitter
• redg8 Posts: 3

  July 31, 2008 7:12PM

  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

  0 · Share on Twitter
• jerryhung Posts: 138

  August 1, 2008 9:13AM

  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

  0 · Share on Twitter
• petey Redgate › Posts: 2,358 New member

  August 1, 2008 10:15AM

  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

  0 · Share on Twitter
• qafoo Posts: 7

  September 10, 2008 4:51PM

  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

  0 · Share on Twitter
• Matthew Flatt Redgate › Posts: 255 Bronze 5

  September 11, 2008 12:20PM

  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

  0 · Share on Twitter