Solved

Windows Server Backup causing SQL Timeouts

Posted on 2013-11-12
5
543 Views
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.
0
Comment
Question by:worthyking1
  • 2
5 Comments
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 334 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.
0
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 166 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!

HTH
  David
0
 
LVL 6

Author Comment

by:worthyking1
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?
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 334 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.
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

OfficeMate Freezes on login or does not load after login credentials are input.
In this article we will learn how to backup a VMware farm using Nakivo Backup & Replication. In this tutorial we will install the software on a Windows 2012 R2 Server.
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 process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…

856 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