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
To add a bit and summarize other responses:
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:
Hope this helps!
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/
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.
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?
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.
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.
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)
Use trace flag (3605,3213) will backup detail in error log.
DBCC TRACEOFF(3605, –1)
DBCC TRACEOFF(3213, –1)
To estimate and check how fast you can read the data from a database or Filegroup there is a special option you can use to backup to: DISK = 'NUL'
Use 1222,1204 will keep record deadlock in errorlog.