Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 6 down vote favorite

The situation: I have a 1.3 TB Database (SQL 2005) that I am currently backing up to 2 network shares on separate machines and on one local USB. (Don't ask why - long story involving a DBA that quit last night) The backup started 12 hours ago and for the first 2 hrs it was showing an estimated total runtime of 8 hrs. For the last two hours it is showing an estimated remaining time of 90 minutes. It seems to be doing 1% per hor right now leaving another 8 hours to finish.

Perfmon is showing 6MB/s going out on the network and 6MB/s going in on an otherwise idle machine (share 1). Share 2 is not idle, so there is noise on the network but no significant trafic. USB drive is at 0 bytes a second for a while now.

Any Ideas of how to revive the two "forgotten" backup targets to get this backup finished before tonight?

P.S:ETA and percent completed coming from sys.dm_exec_requests

Backup command is:

BACKUP DATABASE [XXXXXXXXXX] TO  
DISK = N'D:\backup\XXXXXXXXXX_20121019_T3_A',  
DISK = N'\\server3\XXXXXXXXXXBak\XXXXXXXXXX_20121019_T3_B', 
DISK = N'\\server2\XXXXXXXXXXBak\XXXXXXXXXX_20121019_T3_C'
WITH NOFORMAT, NOINIT,  NAME = N'XXXXXXXXXX-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10, CHECKSUM

The drive that it is currently stil writing to is the one that we usually backup to. That usually takes 60 hours writing to a single file.

share|improve this question
2  
Are you doing it with 3 separate backup commands or one? What's the backup command? Are you using any third party tools? – Brent Ozar Oct 20 '12 at 13:17
3  
Use the Perfmon counters at brentozar.com/go/perfmon to monitor each drive's Physical Sec/Write counter. You'll need to monitor this on the local USB drive, plus remotely connect to \\server2 and \\server3 and look at their counters. – Brent Ozar Oct 20 '12 at 13:25
2  
Also, just going forward - I wouldn't chain backups together like this. You're getting one backup split into three parts - extremely dangerous, because if any one machine fails, the backup set is useless. Did you mean to mirror it to three places instead? When using 3 targets with different speeds, I'm not a fan of that either. Everything gets bottlenecked by the slowest write target. – Brent Ozar Oct 20 '12 at 13:27
1  
Well, before the DBA quit, he deleted all existing backups. So I was looking for the fastest way to get a fresh one. 24 hours is still better than 60. However we were supposed to also do an upgrade this weekend. - This is clearly not the backup strategy to go forward with, just and quick-fix. – Sebastian Meine Oct 20 '12 at 13:30
3  
you said it started fast, which to me might mean the first location is the fastest. Can you backup the entire database to that location only? might be quicker! BACKUP DATABASE [XXXXXXXXXX] TO DISK = N'D:\backup\XXXXXXXXXX_20121019_T3_A' – Max Vernon Oct 20 '12 at 14:19
show 5 more comments

1 Answer

up vote 1 down vote

Do you have a fast spindle or spindle set that is not part of the DB itself to back up to?

Or even a tape drive local to SQL ?

If backup space is snug, a tool like redgate sql backup will compress a backup. A 1TB database might be 100-300mb compressed. The backup is much faster also, because the amount being written is so much smaller (our backups of 0.5TB went from an hour to 19 mins).

The key is to have sufficient space, either local on the box or local on a fast LAN, so you can do a straight, single shot backup. The target should be a spindle set that is not used for SQL data or logs, or any other high IO operation (as the backup itself is heavy IO).

Additionally: If possible, take the db to single user, or read only state before running the backup. In some cases this can improve performance (by eliminating concurrent IO to the db itself during the backup).

share|improve this answer
Thanks for your answer. However, my question was not a general backup best practice question but rather a question about how to speed the backup that was running at the time the question was asked (3 month ago). That backup finished by now, so I don't need to speed it up further. – Sebastian Meine Jan 23 at 17:15
@SebastianMeine Ouch. A "real time" question. I missed that aspect! – samsmith Jan 23 at 22:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.