SQL Server 2005 Backup Product Options

By:   |   Comments (2)   |   Related: More > 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
  • Ability to backup to disk or tape directly with the native tool set
  • Ships directly with SQL Server so it is no additional cost
  • Built into the native SQL Server Management tools
  • Ability to password protect the backups
  • GUI and T-SQL commands to perform backup and restore operations
  • The backups are in clear text and can be read by a text editor
  • Backup time is slow
  • Storage requirements are high
Native SQL Server 2005 Backup command

Backup Agents for SQL Server

Product Pros Cons
Computer Associates - BrightStor
  • Single interface to manage multiple types of backups i.e. file system, IIS, Exchange, SQL, desktops, etc.
  • Ability to integrate with native and specialized SQL Sever backup products
  • Hardware level compression is possible when backing up to a tape device

  • Typically backup directly to tape which can have a longer recovery than recovery from disk
  • Additional licensing costs
  • Need to re-write the backup and recovery procedures
  • Learning a new process to perform backup and recovery
  • Another piece to manage for disaster recovery

IBM - Tivoli Data Protection for Microsoft SQL Server
Symantec - Backup Exec
UltraBac

Specialized SQL Backups

Product Pros Cons
Idera - SQLsafe
  • Definable ROI for the investment via time, disk and tape savings
  • Ability to compress and encrypt the backups
  • Single GUI to manage backups across many SQL Servers
  • T-SQL or command line interfaces to manage the code†
  • Ability to backup to disk or tape directly
  • Backup via SQL Server's virtual device interface (VDI) which was developed and is supported by Microsoft

  • Additional licensing costs from SQL Server engine
  • Need to re-write the backup and recovery procedures to use these tools
  • Learning a new process to perform backup and recovery
  • Another piece to manage for disaster recovery

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.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jeremy Kadlec 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.

View all my tips



Comments For This Article




Wednesday, June 2, 2010 - 11:03:49 AM - killmenot Back To Top (5638)

 You forgot to mention about another Specialized SQL Backups tool - SQL Backup and FTP (http://sqlbackupandftp.com) It's a small and powerful tool. Try it out.


Monday, September 1, 2008 - 2:06:19 PM - clay Back To Top (1729)

Another option is MSSQL Compressed Backup, which is an open source backup to standard compression formats, such as zip files.  Restoring is easy as uncompressing the file and using native tools to restore the database. For performance reasons, it can also restore direct from a compressed file.

I should add the disclaimer that this is an application I wrote.