Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

How can I estimate the time of a full backup for a 30gb database?

In this moment it takes about 50 minutes. Can we improve that?

Thanks

share|improve this question

7 Answers 7

To add a bit and summarize other responses:

  • Using compression will speed up the backup (which is read some then write some - reduce the 'write some' and you speed things up) but at the expense of some extra CPU. You can combine backup compression with resource governor in 2008 to limit this. Using compression also speeds up restores (read some then write some again) - which can be fantastic for reducing downtime in a disaster recovery situation.
  • Not using the same drive for database files and backups. Apart from the contention issues (which will vary depending on the I/O subsystem), this is a disaster in the making. The worst case of data-loss I ever saw was when a 3rd-party tech accidentally formatted the drive that had the database AND the only backups on it
  • Using a striped backup set. If you can backup the database to multiple backup files, then the I/Os are round-robined across the backup files. If you have the backup files on separate storage, you can effect massive performance gains.
  • Tune some of the more advanced parameters like BLOCKSIZE, MAXTRANSFERSIZE, BUFFERCOUNT

The fastest backups I've ever seen are at a customer of ours, Bwin, in Vienna. They can backup 2TB in 36 minutes. See my blog post about this at High-end backup compression numbers.

Have a look at this SQLCAT PDF, specifically:

  • Section 4, Page 71: Tuning the Performance of Backup Compression in SQL Server 2008
  • Section 1, Page 15: Tuning Backup Compression Part 2

Hope this helps!

share|improve this answer

Have you cleaned out your MSDB lately? Good article by Brent Ozar as to what ramifications this has on your db's and backups

http://www.brentozar.com/archive/2009/05/brents-backup-bottleneck-msdb/

share|improve this answer

What version of SQL server are you running? 2008 can do backups with compression that can dramatically increase backup speed. One of my clients backups went from an hour to 10 mins once we turned on compression in the backup job.

share|improve this answer
    
it's a 2005 sql server! –  Orit Jun 25 '09 at 17:03

Is your database physical file (.MDF/.NDF) on the same disk as you're taking your backup? If so the disk is trying to read the database and write the backup simultaneously. Backing up to a separate independent disk should help.

It's also possible that there's a problem with the underlying disk(s) - get a storage engineer to check the integrity of the disk(s).

Another thing to check is that your backup may be being blocked by another process. Execute the command SP_WHO2 ACTIVE when your backup has started and check the BlkBy column. If it contains a number - this is the process ID of a process which is blocking your backup, if the column is blank then it's not blocked.

How fast are your restores?

share|improve this answer
    
I do the backup with the db online... I can't be offline 50 mins –  Orit Jun 25 '09 at 17:05
    
Hi Orit, I'm not suggesting you do an offline backup - do an online backup, but ensure your target backup location is to a different disk to where your database is located. –  SuperCoolMoss Jun 25 '09 at 21:18

The best way to improve it is through the use of a third-party backup utility - for instance, LiteSpeed from Quest, or SQLBackup from Red Gate.

That said, how often are you running full backups? Have you implemented differential and tran log backups?

50 minutes seems a bit long for 30GB, but if your disk array is under stress, I could see it taking that long.

share|improve this answer

Not that I'd recommend it, if you turn off the verification option, it will cut the time dramatically. I would only do it on backups that are copied to standby machines and restored so you'd know you have a bad backup.

share|improve this answer

I would definetely go for Paul's Randal solution. However I would check if msdb backup history is big like SQLChicken said. And If you were on SQL 2008 I would use backup compression as it is kick ass. I don't know if Instant File Initialization would help in this. But would also try it. (testing makes perfection)

share|improve this answer

Your Answer

 
discard

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