Solved

Windows Server Backup causing SQL Timeouts

Posted on 2013-11-12
5
519 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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Create your own, high-performance VM backup appliance by installing NAKIVO Backup & Replication directly onto a Synology NAS!
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This tutorial will walk an individual through locating and launching the BEUtility application and how to execute it on the appropriate database. Log onto the server running the Backup Exec database. In a larger environment, this would generally be …
This tutorial will walk an individual through the process of configuring basic necessities in order to use the 2010 version of Data Protection Manager. These include storage, agents, and protection jobs. Launch Data Protection Manager from the deskt…

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now