September 8, 2010 at 6:08 pm
Hi,
We have SQL Server 2005 EE x64 with SP3 and have 16 GB RAM
The Content database size is 60 GB and we are using native SQL Server backup method (t-sql backup script) to perform Full, differential and log backups.
The Full backup is taking around one hour to finish.
The Content database is growing very fast and in another 6 months it's going to be 1TB
and if we consider the backup time as 1 min for 1 GB then to backup 1 TB database it will take 16.6 hours.
So I'm looking for a backup tool which can improve this backup time..please advice
Thanks
September 9, 2010 at 5:23 am
Hi pshaship,
You’ve posted this on a site sponsored by Red Gate Software, so I’m sure no-one will be surprised to see a reply from a product manager at Red Gate!
As I’m sure many people will tell you, there are various 3rd party SQL Server backup tools that will address your problem regarding the time taken to take a backup, by compressing the backup. This not only saves you storage space & costs, but because of the reduced I/O the backups also run much faster. Red Gate offers two products for SQL Server backup, with different features and approaches depending on your needs.
A useful by-product of compressing your backup, is that the restore process is also much faster (again, due to reduced I/O). You can also make the restore process require minimal additional storage space, by using another Red Gate product: SQL Virtual Restore. This allows you to attach a backup to a SQL Server, such that it’s then a live, fully functional database. The restore process requires almost no storage space, and runs faster than a traditional restore from a compressed backup file. This virtually restored database can then be used as the source for various restore processes, including 3rd party SharePoint tools such as Metalogix’s Selective Restore Manager.
Hope this helps.
Colin.
September 9, 2010 at 7:44 am
Not to rain on Colins parade, but the benefit that you get from compression of a Sharepoint content database backup may not be very much, because compression is highly dependant on the actual content. For example, if your database contains mostly compressed image files, like PDF files, there will be very little benefit from compression. Other types of content will compress better, so the only way to know is to try it. The major backup utilities allow free trails, so you should try them before buying to see if you get any benefit.
If a backup compression utility is not much help, you need to look at where the bottleneck is. It might be that the disks that your database is on are slow, so that you can only increase backup speed by placing the database on more and/or faster disks. You can test this bottleneck by performing a test backup to the nul: device, so that the only IO is the IO to read the database. If that backup time is too slow, then you need to look at improving the IO capacity of the drives where the database is located.
If the bottleneck is in writing the backup file to disk, then you may need to spread the backups over more and/or faster disks. If you have multiple disk arrays available, then you have the option with the backup command to have multiple backup files to spread the IO load over multiple arrays.
September 10, 2010 at 9:03 am
I am, and have been for a long time, a BIG proponent of Hyperbac (which is now owned by Red-Gate). This was way before they had the ability to mount compressed backups as LIVE databases, which is a new and amazingly useful feature.
If you contact Angus dt Chudleigh at red-gate dt com and tell him TheSQLGuru sent you he will take care of you. And I will get a small fee to use for my daughter's college fund! 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply