By: Jeremy Kadlec | Comments (2) | Related: > Backup
Problem
For DBAs, backups are one of the top operational items on their mind. Why is that? Why do backups get so much attention at every company? The simple answer is that backups are the core to all disaster recovery solutions. For some companies backups are the first and last line of defense and for others backups are the absolute last line of defense and are used when the remainder of the high availability options are not feasible. Couple this with the critical role that SQL Server databases play in the organization and backups are not only important to the DBAs and IT, but to the entire organization at critical points in time.
Solution
With the criticality of SQL Server backups, it is necessary to ensure you have the right solution because many viable options exist. At a high level, there are three categories of SQL Server backup and recovery products in the SQL Server market.- First are the native backup and recovery capabilities that ship with SQL Server
- Second are products that have a single interface for multiple types of backups with agents specifically for SQL Server
- Third are specialized backup and recovery products specifically designed to benefit SQL Server
What is common among these solutions are the underlying system tables that are used to support the database backup and recovery process. The common thread is the VDI (virtual device interface) from Microsoft that standardizes the backup and recovery processing. So if you are using or testing various options, these technologies will read and write to the same system tables to have a consistent record set. Check out the three backup and recovery options in the market with the associated products.
|
Native SQL Server Backup Options | ||
| Product | Pros | Cons |
| SQL Server 2005 - Management Studio |
|
|
| Native SQL Server 2005 Backup command | ||
|
Backup Agents for SQL Server | ||
| Product | Pros | Cons |
| Computer Associates - BrightStor |
|
|
| IBM - Tivoli Data Protection for Microsoft SQL Server | ||
| Symantec - Backup Exec | ||
| UltraBac | ||
|
Specialized SQL Backups | ||
| Product | Pros | Cons |
| Idera - SQLsafe |
|
|
| Quest - LiteSpeed for SQL Server | ||
| Red-Gate - SQL Backup | ||
| SonaSafe for SQL Server | ||
Next Steps
- Determine your database backup and recovery needs.
- Determine your budget for using any of these products.
- If you do not have any budget see if the ROI is compelling and accurately matches your environment so that the product pays for itself in a short time period.
- Since trial versions are available for these products, sign-up for the trial and see how these products really work in your environment. Be sure to test not only the backup process, but also the recovery process. Including sending tapes off-site and working through the recovery.
- If these products are beneficial to your environment, speak with your team and show them the results with your databases. Then make the decision to move forward with the backup and recovery solution.
- Update your code and documentation to reflect these new technologies.
- Test the backup and recovery process on a regular basis.
About the author
Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
