Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server 2005 Backup Product Options


By:   |   Read Comments (2)   |   Related Tips: More > Backup

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


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.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, June 02, 2010 - 11:03:49 AM - killmenot Back To Top

 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 01, 2008 - 2:06:19 PM - clay Back To Top

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.


Learn more about SQL Server tools