Introduction

This article describes a proprietary third-party SQL Server backup tool, namely Red Gate's SQL Backup. I am not an employee of Red Gate, but I have been using the tool for several years, like it, and recommend it to my clients. I state this in order to set the correct expectations for this article. It is not a comparative review of backup products, nor is it a critical review of SQL Backup. I wrote it because a client asked for documented reasons why I was recommending that he purchase the tool, and I couldn't find a document that quite fitted the purpose. Hence, this is my attempt to define:

• Why you might need a third-party backup tool
• The important features of a backup and restore strategy
• How SQL Backup assists with implementing such a strategy
• How you might use SQL Backup as part of your enterprise database maintenance plan

Why use a third-party backup tool?

SQL Server's own backup and restore facilities are probably one of the most robust of any in the industry. Once a backup regime is set up correctly, little more is required other than routine checks. So why would you consider purchasing a third-party backup tool?

The reason is that the native Backup and Restore features of SQL Server meet just a part of the requirement for maintaining a live database application. SQL Backup, and other comparable backup third-party tools, aim to provide the extra features that are important for implementing a full backup strategy.

The more demanding the application environment, the more attractive are these extra features, such as backup compression and encryption, a means of monitoring backup and restore activities, ability to set up scheduled transaction log shipping, and so on.

Creating a backup strategy

A backup strategy is just part of the maintenance requirement of a production database. There are many other demands on the DBA's time such as ensuring the effective running of the application, monitoring its performance, resource requirements, blocking, security and integrity, and recording all changes. The backup routine must, therefore, be robust, efficient and very visible. Generally, the creation of the maintenance strategy backup regime will involve decisions on:

• Backup type
• Backup frequency
• How long files are retained
• Whether, and how, backups are to be copied offsite
• The security precautions for the backed-up data
• Whether backups are archived onwards onto tape, or other storage medium
• The strategy for database recovery
• Checks and alerts for errors in the process

A good backup strategy will also incorporate an effective alerting and reporting system. It will provide strategies for correcting problems when things don't go according to plan.

Core elements of a backup strategy

So what, typically, is involved in a backup strategy?

Recovery model

What type of backup should be undertaken: full, differential or file? This depends on the recovery model you select. Normally, the full Recovery Model will be chosen for production databases, as it enables recovery to be made to a 'point in time', using the transaction log backups. However, occasionally, other types will be more appropriate. The System databases will need frequent backups, but this will use the simple recovery model.

Data retention

Your service level agreement will determine how long data is retained. If you are lucky, then this is simple. If not, then you could be told that personal information has to be destroyed within six months and financial information must be retained for six years. If accounting irregularities or errors are being investigated, you may be required to provide backups that will create the system at any point within that six-year span.

Generally speaking, a backup is made initially to a local drive and then archived off to network or tape. The recycling of tape or MO storage has to be done in such a way that data is deleted at the right moment.

Data Archiving

Backups will generally need to be copied to a secure offsite archive, usually via a flavor of secure FTP. Data that must be deleted will also have to be deleted from all archives, as well as the primary backup location.

Data Integrity checking

The database will need to be checked prior to a full backup, in order to ascertain whether there are any problems with it. Alternatively, it must be immediately restored onto another server and the restored database checked for its data integrity.

Backup and Archive Security

Backups will generally need to be encrypted if they contain financial or personal information.

Scheduling

Where database alerts and jobs, such as maintenance tasks, object backups, and routine aggregations are part of the production system, these must be archived and 'versioned'.

Replication, mirroring and clustering

Databases that are configured across several servers will have more complex backup requirements. With the move toward resilient systems, it is becoming increasingly likely that the DBA will be faced with a complex system that requires synchronization and reconfiguration before recovery can be effective.

Source control

If a release goes wrong, a DBA may well be required to revert a production server to a previous version of software, including such routines as functions or stored procedures, whilst retaining the up-to-the-minute data. To do this, the source, and static data, along with alerts, jobs and events need to be retained.

Object-level backup

Where objects contain data that changes little, it is sometimes an effective strategy to backup and restore the data as a separate exercise to the main database backup. 'Enumerations', such as the international codes of countries, or currencies, are an example of this. Sometimes, this sort of data is added to source control along with the scripts of the routines, tables and constraints.

Backup of file-based data

It is easy to forget to back up text-based logs such as the application log or the security log. If an investigation is underway, these may prove vital in constructing the complete audit trail. There may be a number of other file-based information such as email logs that are really part of the system.

Checking history logs

Inevitably, things go wrong with any scheduled process and so the history files have to be checked for anything unusual, along with the application log and the security log for each database server. Spotting an unusual error condition, and dealing with it there and then, can save a great deal of time.

Notifications and alerts

There will usually be a requirement for email alerts if there was an error in the backup or, more rarely, whatever the outcome.

Reporting

There will be a requirement for a report of the backup history for reference, or for management-reporting.

A SQL Backup strategy

SQL Backup is built on top of native Backup and Restore. It uses only the published interfaces with Microsoft SQL Server, in much the same way as tape storage software would do.

At the "coalface" level, SQL Backup provides the support you'd expect to backup and restore databases across your enterprise. To simplify implementing your maintenance plan, and assist in many of these aspects of the maintenance plan, SQL Backup will:

• Back up or restore a list of databases
• Use multiple threads when creating a backup
• Copy backup files after completion automatically
• Copy log files after backup or restore
• Email log files after a backup or restore to one or more recipients
• Email the result of the backup or restore operation
• Write mirrored backup files to one or more locations
• Erase old backup files either before or after the backup
• Allow you to customize the default naming convention used for backup files and folders
• Set up scheduled transaction log shipping

Furthermore, SQL Backup offers support in many of the other key areas that comprise a typical backup strategy, as listed above. For example:

• Data archiving – with SQL Backup, the backup files are usually 50% to 90% smaller than native SQL Server backups depending on the nature of the data in the database; this saves disk space and makes off-site backups quicker to send and more economical.
• Backup security – you can encrypt your backup files to prevent unauthorized access. (128-bit and 256-bit key Advanced Encryption Standard encryption, using the Rijndael cryptographic algorithm).
• Backup and restore scheduling and monitoring – SQL Backup 5 provides a 'timeline' for quick access to a graphical overview of completed backup and restore activities, and jobs scheduled for the future. You can look at an overview of all activities for a SQL Server instance, or activities on individual databases. It clearly shows activities that have failed, which you can then select to see more details. It also shows potential conflicts for scheduled jobs so that you can pre-empt problems.
• Backing up databases on remote SQL Servers and SQL Server clusters – SQL Backup Pro supports this.

SQL Backup also provides several reports of Backup and Restore history across all the servers that are registered in the GUI. It will even allow installation of new versions of SQL Backup to all those servers from a central location via a mouse-click.

SQL Backup is best used from the GUI. Even the more esoteric backup requirements can be generated initially from the wizards, which will deliver TSQL, or command-line code just as easily as an immediate running or scheduling of a backup. Code can, if required, be pasted into existing scripts or used to implement particular requirements.

Database recovery plans

Normally, there will be a series of scripts and simple 'cookery book' instructions in place that will cover most maintenance and recovery tasks, such as implementing a backup system whilst creating a new or replacement server. The most important of these will be the Recovery Plan.

A recovery plan is essential. It must include every detail necessary to rebuild the production server. These details are liable to change constantly, with every alteration and revision to the production system. This recovery model may end up being acted on in an emergency by someone without DBA knowledge who must use it to recover the server without errors. It will be done under pressure and probably not by a DBA.

Almost every production application comes with several ancillary configuration items, files, logs, registry settings, components, logins, directory structures, and so on, which are vital to the well-being of the application. These have to be recorded obsessively and any recovery has to be automated where possible, and rehearsed after every revision of the production software.

SQL Backup can provide everything for a typical database restore via the wizard. Much of this, such as doing a point-in-time recovery, is difficult to script. Once the database itself is restored, the process is then supplemented if necessary with the running of any additional scripts that are required for the working system.

The enterprise-wide perspective

Any enterprise will have a whole range of production database systems. With anything more than ten production database servers, a central console is no longer a luxury when monitoring data backup and recovery. Even the most reliable backup system can occasionally go wrong. The sooner the DBA can see the problem, the sooner it is fixed, and the less likelihood of consequential damage.

It is quite usual for a DBA to be responsible for the data integrity of eighty or so database servers. With only the Management Studio or Enterprise Manager, it is hard to monitor so many without missing details that could presage problems. The traditional tools of the DBA are inadequate for this sort of work. For example, it takes five mouse clicks in Enterprise Manager to get to the history of a backup job, so just routinely scanning eighty servers would take four hundred mouse-clicks. Even then, you'd scarcely be in a fit state to spot a problem. Monitoring tools that can take in the state of the database servers right across the enterprise have become essential. SQL Backup includes, in version 5, an Enterprise Backup monitoring tool that not only allows the DBA to see at a glance, which backup regime is in place at any point in time, for all the databases under his area of responsibility, but also warns of any errors or overlaps. The task is reduced from four hundred mouse-clicks down to eighty, and it becomes far easier to take in the information. These servers need not all have SQL Backup installed; it will work even if the servers are running SQL Server's own backup. There are many other enterprise backup solutions around, but this is the first one that takes the 'project management timeline' approach to the representation of the backup tasks.

Fig. 1 From a glance at SQL Backup's GUI, you can see which activities are happening on
which servers, and when.

SQL Server itself has several tools that will help the DBA, whose responsibilities span a large number of database servers. The system of alerts, operators and jobs provide a number of tools that can be used to create custom systems to keep the DBA informed of all sorts of events. However, it is hardly a shrink-wrapped solution; more an invitation to spit on your hands and write your own application.

Enterprise monitoring tools are not new, but Timeline Monitoring™ for consistent planning and analysis of past, present, and future Backup and Recovery is a novel approach for the hard-pressed DBA. With it, we can visualize potential clashes in future backups, or failed activities in the past. We can zoom in and out to view activities as they occur every day or even every minute, and we can also spot problems at a glance.

Fig. 2 A close-up shot of the timeline in SQL Backup shows a series of successful backups over time alongside a conflict that can be resolved from the interactive timeline.

Using SQL Backup

The DBA will have to decide the best way he can deploy SQL Backup. It can take on a number of roles depending on circumstances:

Where SQL Backup is replacing the current enterprise backup regime, the 'Timeline Monitoring' central console provides an intuitive display of all the backup and restore activities of an enterprise, past, present and planned. It can hide much of the complexity, to make it easier to respond to, and correct, problems in applications.

Where there is a pre-existing backup system that does not need replacement, then SQL Backup can normally be used as a direct substitute for the native Backup, since it can be used as an extended stored procedure that takes, as a parameter, the same TSQL syntax as the native backup: the so-called 'SQL Syntax'. Using the extended stored procedure (sqlbackup) you can start a SQL Backup process using your favorite database connectivity layer (ADO, OLE DB etc).

Where there is an existing system that involves backups being scripted outside SQL Server, SQL Backup can be done from a command-line interface, (SQLBackupC.exe).

SQL Backup 5 and a central console

Normally, we need to do no more than to install the client side, and server-side, components and configure the system using the User Interface. This will allow us to do the following:

• Specify the type of backup (full, differential, transaction log, or filegroup/file backup)
• Provide a list of all the databases to back up
• Specify the time schedule for the backups
• Opt for split-file or single-file backups
• Choose the location, and specify whether each database should go in its own subdirectory or not
• Decide whether previous backup files of the same name should be over-written
• Determine the age of backup files that should be deleted from disk
• Allocate a compression type
• Choose whether to encrypt the backup file and, if so, what strength of encryption
• Select any performance optimization of the backup process
• Specify any actions to take place on completion, such as verification, sending an email confirmation message, sending the backup via email, or copying the backup to a network location

Fig 3. The Compression Analyzer in the Back Up wizard lets you select the optimal compression level for your backups

Having done this, and having tested the strategy that you have created, you can opt to let SQL Backup schedule the task for you on the SQL Server Agent. This would then be all that is required for a simple maintenance strategy. If you need more flexibility, SQL Backup will present you with the TSQL or command-line script that will allow you to create or update your own scripted solutions. The Help files contain a detailed explanation of the syntax, which is based on that of SQL Server's own 'SQL' syntax of the BACKUP and RESTORE command. This is supplemented by many examples.

We occasionally come across reasons for using a script. If you are running SQL Server Express, for example, there is no agent and the backups have to be scheduled via the Windows Scheduler. There are other circumstances too. For example, the backups for an enterprise can be coordinated by a central application that schedules tasks on all the servers via the SQL Servers Agent. Alternatively, backups might be performed by independently scheduling tasks on the Windows Scheduler of each machine. The type of backup strategy you use depends on the application, and each type has advantages and disadvantages. Whatever the local requirements, SQL Backup can be integrated into the solution without disruption.

Creating a scripted system

Scripted backups are widely used, in one of a number of scripting languages such as VB, Jscript, Python, Perl or Powershell. Scripting and monitoring is often placed on the Servers Windows Scheduler, normally with 'push' onward reporting of alerts and events.

Quite often, this is done to provide the extra features and facilities that SQL Backup provides 'out of the box', such as compression, encryption, and so on.

However, for doing database restores, scripting still has an important role: In a High- Availability setting, the first-responder to an alert that a system is offline should be able to quickly execute a script without requiring specialized knowledge.

A recovery plan for a server and database will usually be scripted. Because the SQL Backup or Restore can be implemented as a command shell or extended stored procedure, it will allow the DBA to choose from a number of options. The features of SSMS or Enterprise Manager can be scripted via DMO/SMO.

Creating a centrally-orchestrated system

Microsoft provides the means for creating a centralized system for scheduling and monitoring backups using SMO/DMO, ADO, OLEDB, ODBC, etc. This bypasses the need to use the agents of the individual servers. Although this can result in a possible point of failure, it can provide great benefits in reducing complexity. SQL Backup 5 will report, graphically, the backup history across the enterprise, even where this type of system is in place.

Alert-based transaction backups

With earlier versions of SQL Server, transaction log backups were always done at fixed time intervals, purely because it was the only possible way. It is fine if transactions occur at a constant rate, but real systems generally experience peaks and troughs in usage. If the backup job is placed on the SQL Server Agent, then it can be fired by an alert as well. This means that additional transaction logs can be made at times of high transaction throughput, or when the log reaches a certain size, or grows by a certain percentage. When the alert fires, it then triggers the initiation of the log backup. This can be implemented after SQL Backup has been scheduled by the Back Up wizard, since SQL Backup uses the SQL Server Agent to schedule its tasks rather than, in many systems, having a centrally-based proprietary scheduling system.

SQL Backup compression

The compression you can achieve with SQL Backup depends upon the type of data stored in the database; if the database contains a lot of highly-compressible data, such as text and uncompressed images, you can achieve higher compression. SQL Backup provides a 'Compression Analyzer' utility so that you can assess the best compression level to use with your data.

Smaller backups save you valuable disk space. For example, if you achieve an average compression rate of 80%, you can store the backup for a 42.5 gigabyte (GB) database on an 8.5 GB DVD-R dual layer disc. Smaller files can also be transferred more quickly over the network, which is particularly useful, for example, when you want to store backups off-site.

There are three compression levels to choose from:

• Compression level 3 Generates the smallest backup file in most cases. Uses the most CPU cycles and takes the longest to complete.
• Compression level 2 Is a variation of compression level 3. On average, completes between 15% to 25% faster, consumes between 12% to 14% fewer CPU cycles, but results in a file that is averagely 4% to 6% larger when compared to compression level 3.
• Compression level 1 Uses a different compression algorithm compared to levels 2 and 3. On average, completes between 10% to 20% faster, consumes between 20% to 33% fewer CPU cycles but results in a file that is averagely 5% to 9% larger when compared to compression level 3.

Part of the speed in compression level 1 is due to the fact that it does not generate checksums for its compressed data. Thus, if the backup file is corrupted (intentionally or unintentionally), a buffer overflow may occur. You should store the backup file in a secure location to prevent the backup file from being intentionally tampered with to cause a buffer overflow.

In some rare cases, compression level 1 results in a file that is smaller than compression level 3, without a sacrifice in speed. This happens when the database contains frequently repeated values e.g. a database containing results of SQL Profiler trace sessions.

The compression level has no noticeable difference on recovery time.

SQL Backup encryption

SQL Backup encrypts the backup files as they are being created and protects the data, ensuring compliance to information security and privacy acts such as the UK's Data Protection Act, and, in the USA, GLBA, HIPAA and Sarbanes-Oxley.

Compressed backups are much smaller and, therefore, easier to transfer over the network. SQL Backup Pro supports 128-bit and 256-bit key Advanced Encryption Standard encryption; it uses the Rijndael cryptographic algorithm which was specified as the Advanced Encryption Standard in 2001 by the National Institute of Standards and Technology (NIST) in Federal Information Processing Standards (FIPS) Publication 197. SQL Backup Standard supports 128-bit encryption. SQL Backup Lite does not support encryption.

Converting SQL Backups to Microsoft's native format using SQL syntax

You can convert SQL Backup (*.sqb) files to Microsoft Tape Format (MTF) files that you can restore without requiring SQL Backup to be installed on the computer. This might be necessary where a database has to be restored to a standby server that had not installed SQL Backup.

The CONVERT command or sqb2mft.exe utility can be used to achieve this. To convert a split backup to MTF format, you first have to use sqb2mft.exe to convert each of the backup files in turn. You can then restore the MTF format files as a group of split backup files.

Summary

SQL Backup takes SQL Server's own backup and restore facilities, and uses a range of features and facilities to turn it into a powerful and intuitive system that meets the common requirements for an enterprise-wide backup strategy.

This means less time and cost spent on backups, together with the confidence that data security will not be compromised.


This article was originally published on Simple Talk, a service provided by Red Gate Software.

Author profile:
	William Brewer is a SQL Server developer who has worked as a Database consultant and Business Analyst for several Financial Services organisations in the City of London. True to his name, he is also an expert on real ale.