Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Windows Server Backup causing SQL Timeouts

Posted on 2013-11-12
Medium Priority
Last Modified: 2014-03-14
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.
Question by:worthyking1
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 1002 total points
ID: 39643642
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.
LVL 35

Assisted Solution

by:David Todd
David Todd earned 498 total points
ID: 39643669
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!


Author Comment

ID: 39644580
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?
LVL 38

Accepted Solution

Jim P. earned 1002 total points
ID: 39644872
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.

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Server 2003 introduced persistent Volume Shadow Copies and made 2003 a must-do upgrade.  Since then, it's been a must-implement feature for all servers doing any kind of file sharing.
Want to know how to use Exchange Server Eseutil command? Go through this article as it gives you the know-how.
This tutorial will give a short introduction and overview of Backup Exec 2012 and how to navigate and perform basic functions. Click on the Backup Exec button in the upper left corner. From here, are global settings for the application such as conne…
This tutorial will walk an individual through the steps necessary to enable the VMware\Hyper-V licensed feature of Backup Exec 2012. In addition, how to add a VMware server and configure a backup job. The first step is to acquire the necessary licen…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question