Using Windows Server Backup to backup a server...

• jasona.work

  SSC Guru

  Points: 50192

  More actions

  November 12, 2012 at 9:33 am

  #270764

  I'm looking for a moderately id10t-proof method for our clients to back up their servers with as little management as possible. These clients do not have DBAs, and the feeling I'm getting from my bosses is that they don't want us to be their DBAs either. The bosses (and by extension the clients) want something that "just works" and "just works" for SQL Express. So scheduled jobs in SQL Agent aren't an option (although using Windows Task Scheduler and batch files would allow scheduled jobs.)

  So I have been looking at the following backup system for clients to use:

  Once-a-day Windows Server Backup to an external or network backup media. Most likely if this pans out as a solution, the DB that would be on the SQL Server would be flipped to Simple recovery, sacrificing the ability to do point-in-time recovery (No, that I am aware of there has been NO discussion with clients about SLAs, etc. I'll give the whole gory story after the important bits)

  Now I've set up a test system, Server 2008 R2, SQL 2008 R2 SP1 with a small test DB on it. After running the WSB, SQL reports that the DBs were backed up, so it looks like WSB is "SQL aware" enough to trigger a VSS snapshot. So it looks like this should work as a method to either restore a failed server, or a failed DB (with a loss of data since the backup was taken.) Is there anyone using this to backup their servers? If so, have you run into any problems? As I said, if this is the method that the bosses choose and presuming that they don't want to / won't do separate DB / TLog backups, then I will be telling them to switch the DBs to Simple Recovery so we don't need to worry about the TLog growing uncontrollably.

  Now, the gory details:

  My employer is rolling out an Electronic Health Records (EHR) system. They have been (and will continue to) also selling a Practice Management application (used for electronic billing, appointment scheduling) which is NOT SQL-based. While we have provided clients with a batch file for backing up their PM app, clients are expected to handle the backups themselves (running it on a regular basis, having the backup tested either by their own tech or by mailing it in to our office) My bosses are looking for something similar for the SQL-based EHR system. MY preference would be to either use WSB once a week to make it easy to get a replacement server set up (WSB lets you do bare-metal restores) and then daily full and hourly TLog backups. Sadly, I don't think I'll be able to sway management to my way of thinking (not that I won't try.) Yes, I realize that with my method, once the WSB is restored, we'd have to IMMEDIATELY go in and drop / take offline the DBs so we could then get started on restoring from the DB backups.

  Thanks,

  Jason

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738227

  More actions

  November 12, 2012 at 10:29 am

  #1558232

  You could do this. My understanding of Windows Backup in the modern OSes is that it is VSS aware and you get a real SQL backup.

  However the problem is granularity. You don't get an easy way to recover a single database that I'm aware of. Someone would have to understand how to get the correct file, and then it would recovery to just that backup. Logs would still need to be managed, or you'd run in simple mode, which means potentially lots of lost data.

  Plenty of people can talk about accepting some data loss, but when they get faced with losing a day's worth of work, they won't be happy.

  I might suggest using simple, standardized scripts for native SQL backups with hourly logs. Windows Backup can still get these files, but you also have a local recovery point if necessary. Make sure you have maintenance cleanup to remove old files, but this is what I'd recommend.

  It's not much effort, and I'm not sure why management would care. The scheduler does the work, not the client or manager. To me it doesn't even make sense to present this option to management. They want simple backups for clients, give it to them. Don't explain the log backup process.