Perhaps the most important tasks that occur on every SQL Server is running backups and restores. Backup copies of your database give you the security that if any issues surface with your production database you have a complete copy to fall back on. In most cases the restore process is done for non-production critical means such as refreshing development\test environments or refreshing a reporting environment. But in the most critical mode you are restoring these backup copies to replace or fix a production environment.
Based on the importance of creating backups and the critical need for restoring backups to correct a production problem, time is of the essence. Backups are an online operation but they do use system resources. Restores, however, require exclusive access to the database, so in a failure state this is an even more critical task.
In light of the time factor to complete these tasks, there are several things that can be done on both the backup side and the restore side to improve the speed of these operations.
Hardware
Backup and restore time is affected by your hardware as well as the configuration of that hardware. From a hardware perspective here are a few things you should consider to improve performance.
Native Backups
Another area affecting the time it takes to complete backups is when and how the backups are run.
Native Restores
From a restore perspective most of the items mentioned above apply to restores as well. Here are a few additional tips:
Third Party Software
One key time-saving function is using a backup compression tool built specifically for SQL Server. There are several on the market and these tools provide the biggest gains with the least amount of effort.
Based on a test by Idera and various hardware vendors, Idera was ab
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
le to achieve backup rates of 4.5 terabytes per hour and restore rates in excess of 2.3 terabytes per hour by using SQLsafe. Take a look at this link for additional info on setting new performance records. This is probably the extreme for most SQL environments, both from the cost to configure the hardware to the need to backup a 4.5 terabyte database. But the fact is that it is possible by configuring the total solution in both hardware and software.
Summary
As you can see there are several different things that can be done to increase the throughput of your backup and restore processing. Some of these items are pretty simple fixes while others require reconfiguring your hardware, purchasing new hardware or purchasing tools that can help increase the speed.
Based on the speed of the Idera tests to achieve 4.5 terabytes in an hour, using a third party backup compression tool seems like the simplest and easiest way to go. I don't think there are many databases that fall into this realm of database size, so based on the test most full backups could be completed in under an hour. Here is a report of the largest SQL Server databases, as you can see there are still not many databases that cross the terabyte threshold. From the report, the number almost tripled in two years and I am sure the number will triple again if not more in the next two years.
By using a combination of all these options you will be able to achieve faster backup and restore times, but with just about everything there will always be some kind of limitation.
More on SearchSQLServer.com
Expert webcast: SQL Server Backup and Restore Fundamentals
Guide: SQL Server Backup and Recovery Learning Guide
Tip: Online restore feature in SQL Server 2005
[TABLE]
