Huge db backup taking too long. help me reduce
-
Hello everybody,
We have a db that is currently 782,000 MB in size. It takes forever to backup. It's set to simple recovery model. Correct me if I'm wrong, but it's my understanding that if I were using full recovery model, I could stage backups by file group, but this option is not available when the db is in simple recovery model. How can I speed up the backups of this db? Are there any strategies to break the backup into managable chunks?
Thanks,
Bob McC
-
Yes but you have to be carefull of bulk inserts and such. As well your log file will be a big issue as it grows and has to be retained to support restore. I would look at possibly increasing the number of devices you backup to and span backup volumes on multiple devices before I go thru filegroups when talking speed. Also look at the speed of your backup device and you might want to look at some of the companies that make backup tools like Lightspeed which can compress the backup as well as speed it up a bit thru their method.
-
Well, the company I work for isn't small. What that means is there is a certain amount of bureacracy involved when talking about increasing the number of devices I backup to, etc. I was hoping for a conventional SQL Server solution. Currently our company uses NetBackup.
-
I do my 800 GB backup in < 2 hours using LiteSpeed and the backup file size is < 150GB.
You can convince your managers to spend some money for buying LiteSpeed or some other software which better than sql backups and you save money on disk as well as Time.
If you want only native sql solution then you follow Antares686 solution... which is..
Use multiple files for backups and make your are creating these multiple files on the same drive which will not improve performance.
If you are backing up to network make sure you have GIGA bit network adaptors and high end disks etc...
MohammedU
Microsoft SQL Server MVP -
That's not a huge database, quite small in fact. I had a 300+ GB one in FULL recovery mode and it only took 4 hours to backup.
How are you doing backups?
To disk?
To tape?
Across a network?
Are you using SQL Server backup commands or a third-party software (such as Veritas Backup Exec)?
-SQLBill
-
Just noticed you said you are using NetBackup.
Does that have a SQL Server agent? Or are you just backing up the .mdf/.ldf files? If you are just backing up those files - I hate to tell you - you don't have a backup. Those files can only be backed up when they are offline (not in use).
If you are backing up straight to tape, that can take awhile, especially if the tape drive is not locally connected.
We backup to local disk and then have NetBackup copy our backup files to tape every night.
-SQLBill
-
check out red gate's sql backup (talked about lots here)...AMAZING!!!
-
Could you have differential backup?
-
The fastest and easiest way to speed up your backups would be to get SQL LiteSpeed, RedGate Backup, or one of their competitors, and create compressed backups. Your mileage may vary, but I have gotten an average of 75% reduction of size and time with backups from LiteSpeed.
I backup my databases to a network share. On a good day it would take 2.5 hours to backup my largest database (150GB), often it would be 5 hours or more (probably due to activity from other servers). Before getting LiteSpeed I tried striped backups (backup to multiple files in parallel). Using four backup files on separate local drives (that were not SQL data drives), the same 150GB backup took about 7 minutes with the standard BACKUP command. It took over an hour afterward to move the files over the network, but this was still much faster than before. The MOVE command seems to get data across the network much faster than SQL BACKUP can manage. Another advantage is that the backup is over and done with in 7 minutes, for the hour that it takes to move the file there is very little impact on the server. Less bandwidth available, but no locking or other query interference.
Even after getting SQL LiteSpeed I still used the local striped backup + MOVE approach for my larger databases. The backup takes longer because it has to perform the compression, but the smaller files transfer much faster. Here are the results I got for total time to backup all databases on my largest server, about 320GB total (150GB largest database):
Historical average of standard BACKUP to network share: 9 hours
standard BACKUP to multiple local drives followed by MOVE: 3.5 hours
LiteSpeed backup to multiple local drives followed by MOVE: < 2 hours
Striped backups are very easy, all it takes is adding a few more filenames to the BACKUP command. The disadvantage is you have to script it all yourself, there is nothing in the maintenance plan wizard to help you. You have to script the backup, the move, the deletion of old backups, and if you want to be able to use backup history for a restore you have to update the physical file names to show the network share instead of the local drive.
-
I'm so glad I started this thread. This has been a real eye opener. Right now a full backup can take up to 3 days! I'm not kidding. And we've been told it's because our database is just so huge.
~sigh~
-
I'm not sure what you're asking. Yes, we do differential backups too. They take between 45 minutes and 8 hrs.
-
One of the compressed-backup vendors (I don't recall which, probably either LiteSpeed or RedGate) has a benchmark of backing up 1TB in < 2hr. They probably were using hardware that you and I can only dream about, and I doubt they used the standard maintenance plan wizard, but you should look into it.
Most of these vendors have free trial versions. There are differences in cost, features, and performance but if you look around you can find comparative reviews.
-
You may also want to lok at 'striping' your backup target files. I have a 300 Gb database in simple recovery mode being backed up to 4 files. The execution time is a bit less than 45 minutes. Oh, I almost forgot that this server has 25+ other databases with about another 250 Gb of database backups occuring at the same time as well !!!
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Hello,
If you have multiple tape drives available, you can multi-stream (aka stripe) the backups in NetBackup so that they run in parallel. This can cut down the amount of time required to perform the backups, but beware....
If you stripe the backups, you'll also need all of the stripes at the same time in order to restore. Which means that if you use ALL of your tape drives, the SQL database will be only the restore job you can run until it completes. This might not be a good thing if you are in a disaster recovery situation that requires a bunch of restores going on at the same time.
Ah, but I digress... Look at the SQL agent for details on how to use multiple streams.
jg
-
So, as I asked....do you backup to disk and then to tape? Are you backing up across a network?
The best practice is to use SQL Server Backup commands (either via SQL Server itself, or a third-party backup tool that uses a SQL Server agent to do the backup) and backup to disk, then copy the backup file to tape.
Copying straight across a network to tape CAN be very slow no matter what size your database is.
-SQLBill
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply