Improve company productivity with a Business Account.Sign Up

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 655
  • Last Modified:

Windows Server Backup causing SQL Timeouts

We are running a Win 2008 Server SP2 32bit web server with MS SQL 2008. This server serves dozens of client websites. All website data and SQL DBs are housed on a data volume separate from the OS volume.

Yesterday I setup Windows Server Backup to backup the OS and Data volumes at 3am (about 65GB total). Then this morning I got hit with client calls saying their sites were down. Upon checking I found SQL server timeout errors on all websites with SQL databases. So, I interrupted the backup and all of the SQL connections went back to normal.

As a quick test I tried it again by initiating a one-time backup of the data volume. As soon as it started running the SQL connections began timing out and also got a couple of named pipe errors. As soon as I cancel the backup process SQL goes back to normal.

So, my question is, WTF? Isn't Windows Server Backup designed to backup live servers in real-time? Am I expecting too much from Microsoft here?

The server has plenty of resources. Upon looking at the resource monitor while backup is running, CPU, RAM, Disk & Network usage are all in the 5-20% range.

Any assistance toward a solution would be greatly appreciated.
  • 2
3 Solutions
Jim P.Commented:
Isn't Windows Server Backup designed to backup live servers in real-time?

The simple answer is no.

The long answer is that you need to exclude the mdf (SQL Main data files) the ldf (Log data files) and any ndf (exteNded data files) from the backups. Those files are opened by the SQL Server Service on the SQL Service startup. Those files are exclusively locked. Nothing else can really read or written them at the OS level.

The typical scenario is that you use a SQL Server Agent job to dump the DB(s) to a .bak file and then the backup SW grabs the .bak file. If you are using SQL Server Express, you can get a free workaround agent from Lazy Coding.

And I won't give my opinion on the Windows Server Backup software.
David ToddSenior DBACommented:
Ditto for what Jimpen said.

One caveat, I suggest adding a small text file to each leaf directory, and back up that text file. Filename to say something like - for backup. Contents to say: To make sure that directory tree is recreated on full restore of machine backup.

Then when you come to restore the database backups, the directory tree has been recreated for you.

Second caveat: I recommend after reading it somewhere else, that you create an offline copy of system data files. On full restore from tape, you can copy those files to the correct location, and SQL should start. Okay, most of the databases are missing, but you can fix that by restoring those databases one at a time. Restore Master and MSDB and Model first of course!

worthyking1Author Commented:
Okay, so it appears that Windows Server Backup is not the right tool then because even its "custom" setup only allows the selection of disks and not inclusion/exclusion of directories on those disks.

So, what backup software should I be using that can automatically and seamlessly handle the task of backing up our data?

For example, on our Linux server the built-in backup tool in WHM backs up all websites, all account data, all MySQL DBs, and all system data every night without issue, without hiccups, nothing. Is there something similar for Win2008?
Jim P.Commented:
I can't speak to the Linux file and OS but I believe that MySQL service only locks the MySQL DBs into read only mode. So that means a backup software, or even a DOS prompt can always make a copy of the file. The same with Firebird/Interbase. That isn't true of SQL Server.

I still haven't found a backup software that I really trust to take care of the backup of the OS and the SQL Server databases, especially if the SQL DB's aren't relatively quiescent. In addition the B/U SW companies usually charge extra for their SQL Backup Agent.

I generally never bother with backing up the system databases. I have built a series of scripts that create all the maintenance functions (including backups) that I need. And I also have a portion that runs the sp_help_revlogin sp every 8 hours and dumps the logins to disk.

So my recovery process is to (re)install SQL, restore the DBs, then recreate the logins from the revlogin output file, and recreate the maint jobs from my scripts.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now