solving sql server problems for millions of dbas and developers since 2006

SQL Server 2005 Backup Product Options

By: Jeremy Kadlec | Read Comments (2) | Print

Jeremy is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com and SQL Server MVP since 2009.

Related Tips: More

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.

Related Tips: More | Become a paid author

Last Update: 6/23/2006

Share:

Connect with MSSQLTips.com

Comments and Feedback:

Monday, September 01, 2008 - 2:06:19 PM - clay

Read The Tip

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.

Wednesday, June 02, 2010 - 11:03:49 AM - killmenot	Read The Tip
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.

Sponsor Information

Find and fix SQL Server problems before they happen - SQL diagnostic manager now with predictive analysis!

SQL Monitor – For database professionals who need results on Day One. Try it online.

SQL Servers keeping you up at night? Contact the Edgewood SQL Server consultants for a Health Check.

Find and Fix SQL issues with Foglight Performance Analysis. Get a free copy.

Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now.

Free Learning - Introduction to SQL Azure Delivered by Herve Roggero on Wednesday, June 13 @ 3:00 PM EST