Back Up Over Network

Microsoft in its wisdom assumes that backups will be held on the local server e.g.

• Data on Drive D:
• Logs on Drive E:
• Backups on Drive F:

What happens if the local server fails?  Ooops. 

We back up to disk and then to tape (belt and braces) - we've had a few less drastic problems than server failure, mainly because we have run out of space on the local machine because some databases have grown a bit bigger than was initially intended.  It's a bit fiddly to back up to tape from 30 different servers too.  So to resolve these problems we decided to back up over the network (BUON) to a central media server.    This makes the process of backing up to tape faster and more reliable.  Our server bods reckon they saved about 900Gb of space on local servers.  And just in case there is a problem with the central media server, we can fall back to an alternative.


This blog is about SQL Server 2005 and 2008.  The technique is a bit different for SQL Server 2000 - let me know if you want this and I'll send you my notes.  But you're moving off 2000 now, aren't you?

Here's how I did it:

Set up a maintenance plan called BUON ServerName System Maintenance Plan (or whatever you like, it's just a name).  I like to set up one plan for System databases and another for User databases. 

a)      Schedule plan for 19:00 daily (for us, most people are finished by 18:00 and the tape backup occurs at 02:00 so this should give it plenty of time, even if the first attempt fails and it has to take another backup).  Don't forget to set up a schedule, otherwise your colleagues will mock you. 

b)      Check Integrity.  There's a nice new thing in SQL Server 2008 that lets the maintenance plan ignore a database if you have taken it offline - tick it.  If you take a database offline, then the maintenance plan carries on happily without whingeing at you that the database is offline.  More for user databases than system, admittedly. 



c)      Reorganise Index; ignore if offline

d)   Backup with destination \\acbriap024\SQL_Backups\<servername>

Here's the meat in the sandwich.  ACBRIAP024 is the primary media server, and SQL_Backups is a shared drive on that machine.  I set up a directory on that share named after the SQL Server instance I want to back up. 

System databases get a .sys.bak extension, User databases get usr.bak. 

And I always tick the Verify Backup Integrity box - it takes a bit longer because it runs a "Restore Database with Verify Only" command after taking the backup, but it's a useful precaution. 

e)      Maintenance Cleanup - clear out backups older than 2-3 days

f)      If step (d) fails, here's the fall back position - backup with destination
\\acbriap023\SQL_Backups\<servername>
This is the secondary media server - the belt and braces machine. 

g)      Maintenance Cleanup

Here's what it looks like:

Notice the red line? Red for failure - if you right click on the line, you can specify whether you want something to happen if the previous step succeeds (green) or fails (red) or just completes regardless (blue). In this case, if the backup to the primary server works, it just tidies up the primary server by deleting old backups and all is complete. If the primary backup fails, then it goes to the secondary backup.

If you want more detail on the individual tasks in a maintenance plan, check out this estimable free download from Brad McGehee.  Brad lives the dream and administers databases in Hawaii, so he must be right!