Microsoft SQL Server 2000 High Availability

To properly create an effective strategy, there are numerous issues you must consider. For example, how are you going to execute your backup? To what storage medium are you going to back up your databases? Where are you going to store your backups? Conversely, can you find the necessary backups during a restore? Now that you think you have a strategy, it is time to figure out backup options, syntax, recommendations, and requirements.

Options for Performing a Backup

There are several ways to create backups: Transact-SQL syntax executed real time within SQL Server Query Analyzer; Transact -SQL syntax that has been automated through SQL Server Agent jobs and alerts; the Database Maintenance Plan Wizard; SQL-DMO; or any custom applications or third-party tools where you can access SQL Server. Although all are acceptable options, there are some basic recommendations for each option s usage.

There are numerous options related to how backups are written to backup devices. Backups can be performed to a new device (a new file or tape) for each backup, or a backup device can be used multiple times by either appending subsequent backups, keeping different backups on the same device, or overwriting the backups on the same device, keeping only the most recent backup. Many utilities that help to create automated backups use a new file for each backup. In this case, the filenames for the backups typically are created using the date and timestamp of when the backup was performed. For example, using the Database Maintenance Plan Wizard to create full database backups or transaction log backups, the filename has the following format:

dbname_backuptype_YYYYMMDDHHMM .bak.

Although this naming convention is helpful for finding backups by filename, it does not necessarily scale well. In fact, using the Database Maintenance Plan Wizard for small databases is acceptable, but for larger and more complex configurations where a variety of backup types are desired, it is likely you will create your backup strategy a bit more manually.

Typically, administrators automate the backup to disk through the day, and then have some type of nightly backup operation that picks up all of the backup files at the operating system level and backs them up to tape. The main reason for this is speed. Typically it is faster to back up and restore files from disk than tape because you avoid the long mechanical process of positioning the tape and rewinding. Transaction log backups are commonly automated using SQL Server Agent jobs that run at fixed points in time.

Creating a Backup Device

When you perform a backup, there are two ways you can specify the destination. You can either physically reference the output device (tape or disk) at the time of the backup, or you can make a logical reference to it by creating a backup device. Making a logical reference to the location by creating a backup device eliminates later problems with backup scripts. By abstracting the name from your scripts, you can update the hardware or devices and just update the logical definition.

For example, by backing up to a device with the logical name TapeBackup , you do not rely on the physical tape unit to stay the same. If the underlying tape units change from \\.\tape0 to \\.\tape12, only the logical definition needs to be updated, not all of the individual backup scripts and jobs. The same is true for backups directly to a hard disk. If a drive is starting to become full and you decide to place backups on a new drive, then you can change the device without changing all of your backup scripts. During the restore process you need only to have the backups themselves . You can restore them from any location even if that is not the location to which you backed up the database.

You can create backup devices by using the sp_addumpdevice system stored procedure or using SQL Server Enterprise Manager. (Expand Management, right-click Backup, and select New Backup Device.) When you use the sp_addumpdevice stored procedure, remember that no paths or filenames are verified for access or even existence. This point is important because verification does not occur until you later attempt to back up to this device, which is when you would see errors causing the backup to fail. Make sure all of your paths and filenames are verified at the time of creation, and if the device is an over-the-network backup, you should also verify the path for appropriate permissions.

When using a network device using a Universal Naming Convention (UNC) name ( \\ servername \sharename\pathname\filename. ext), SQL Server must have the appropriate permissions to use the device. If you execute a backup command through Transact-SQL syntax (for example, in SQL Server Query Analyzer), then the command will be executed by the underlying MSSQLServer service. If you execute a backup as part of a job, then the backup will be performed by the SQLServer Agent service. You must verify that the domain accounts under which these two services are started have the appropriate write permissions to create and write files to the UNC device.

You can view the list of existing backup devices by using the sp_helpdevice system procedure or by reviewing the list in SQL Server Enterprise Manager under Management, Backup. When you perform a backup, you specify the device (or devices) to which the backup should be performed, and SQL Server writes to the location as defined by the device. A simple example of using a backup device follows :

BACKUP DATABASE DatabaseName TO DeviceName

Caution

When you use Transact-SQL or SQL Server Enterprise Manager to remove a backup device, the default behavior is to leave the files intact and not delete them. This approach has advantages and disadvantages. It is advantageous if you truly want to remove only the logical reference to the device. In fact, you can immediately create a new logical device name (for example, with a new name) that points to the same file if you want. Creating backup devices does not affect the physical file.

Special Backup Device: NUL

In addition to creating permanent or temporary devices, there is an output device called NUL. NUL is an operating system device that acknowledges a write operation without actually writing the data anywhere .

Note

This is not a typo; the device is spelled NUL , not NULL .

NUL is used in special cases to test the read side of the backup process; however, the backup is not saved. Instead the backup is processed as if it were a regular backup but the write portion of the backup is never actually done. There are a few interesting uses for this device:

Using the NUL device in code is performed the same way as the use of any device physically defined at backup. The following full database backup command uses the NUL device to test the size and impact of the backup:

BACKUP DATABASE Inventory TO DISK = 'NUL' go

The result will be similar to the following:

Processed 17888 pages for database 'Inventory', file 'InventoryData' on file 1. Processed 1 pages for database 'Inventory', file 'InventoryLog' on file 1. BACKUP DATABASE successfully processed 17889 pages in 5.872 seconds (24.955 MB/sec).

From this output, you can determine the size of your backup. SQL Server does not need to back up every page of the database; instead, it needs to back up only allocated extents. The size of a backup is therefore the size of the database minus the free space. How large is that? In the previous example, a full database backup was performed to the NUL device. The backup showed that a total of 17,889 pages were processed. At 8 KB per page, the backup size would be about 146.5 MB. This information is helpful to know if you need to estimate storage because of space restrictions or if you are backing up to disk to copy the backup device(s) to CD or DVD.

As an alternative, you could use the sp_spaceused system procedure or run a system table query to get the same number that sp_spaceused produces. However, of the two, using a system table query is easier to add to programmatic scripts, batches, and so on. To get an estimate of the space usage for a database, use the following query:

SELECT sum(reserved)*8/1024 -- Estimate in MB FROM sysindexes WHERE indid IN (0, 1, 255)

Unfortunately, both sp_spaceused and this system table query might not be as accurate as you need them to be sometimes. To get a more accurate value you can run sp_spaceused @updateusage = 'true' and the information will be more accurately gathered by scanning the entire database; however, this can create quite a burden on the system.

Finally, there are some technical notes to be aware of when using the NUL device. Performing a backup to NUL will indicate that the backup strategy has begun ”even though nothing exists from which you could recover. This situation requires you to manage the transaction log if you are not already doing so. You must always perform another real full database backup after backing up to the NUL device because you will need a proper full database backup from which you could recover. Differential database backups performed after a backup to the NUL device cannot be applied to a previous full database backup; in fact, only the transaction log backups are useful. Performing any full database backup ”even with the NUL device ”resets the differential bitmap. Because this backup to NUL performs exactly the same steps as a regular full database backup without the actual capture, you should use this option only in test or development scenarios when you are truly looking to evaluate the performance impact of the read side of backup on your disk subsystem.

Important

Transaction log backups to NUL will break the log sequence chain.

Parallel Striped Backup

To improve performance, you can use multiple backup devices in parallel to create a striped backup set. The biggest benefit of creating a striped set is that you can back up to as many as 64 independent devices at a time. If you have two drives to which you would like to store backups, then you might be able to cut the backup time in half (depending on system configuration). Realize, however, that the benefits of parallel striped backup are really only achieved when the devices are different physical devices. Backing up to multiple backup devices on the same physical disk is probably not beneficial at all unless you need the files to be of a certain size. For example, you might back up to three files on one physical disk to ensure that the three files are small enough to individually burn to a certain media type, such as a 680-MB CD-R. You can then later restore directly from the CDs. If you had backed up to one large file and then split it across multiple CDs, you would need to put the file back together on disk before commencing with the restore process.

Note

Backing up to multiple files on a device does not give you any performance improvements. You will actually have worse performance than if you were backing up to one file on that device.

Additionally, if you have multiple devices that you would like to act as one, consider creating a RAID 0 array and then using that as your backup location. Creating a parallel striped backup has the same configuration as a RAID 0 array. There is zero redundancy, and only performance gains because to successfully restore from a parallel striped backup, you must have all devices at the time of the restore. This is not true of tape devices, but it also means that you need all of the tapes to perform your restore; each media family is processed before another one can be used. Handling media families that span multiple tapes is not an easy task.

When you back up to multiple devices in parallel to a hard drive, for example three devices, approximately one-third of the database will be placed on each of the three devices. For tape, an algorithm is used where a faster tape can consume more of the data. If one device or file is not accessible at the time of recovery, the two remaining devices are essentially useless.

The following example shows a full database backup of the Inventory database to three devices: InventoryBackup1, InventoryBackup2, and InventoryBackup3.

BACKUP DATABASE [Inventory] TO [InventoryBackup1], [InventoryBackup2], [InventoryBackup3]

If the file or tape on which InventoryBackup2 resides were to become damaged, then the complete backup would be unusable. You cannot recover any data from the two remaining devices (InventoryBackup1 and InventoryBackup3) without also having the data from InventoryBackup2.

When creating a parallel striped backup, you must have good naming conventions. If recovery were necessary, you would want to be able to find all components of your backups as quickly as possible. In fact, in addition to naming the individual backup devices, you can name the media set. Creating a media set name makes finding backups a lot easier because each device shows the media set name and description. Without the name and description, the only identifier you will have is a globally unique identifier (GUID) that SQL Server places into the backup device s header at the time of media set creation (which is a good secondary check, but it is harder to read and harder to work with). Therefore, you should always use a media set name and description, and you must set them with the very first backup. When performing the first backup (or later if you want to break up a media set or change the use of a device), you can add the FORMAT option to define the device s use.

Caution

If any information is currently on the devices, it will be overwritten and useless. Use the FORMAT option only when necessary.

Using the same example as before, the syntax changes to the following when you add a media set name and description:

BACKUP DATABASE [Inventory] TO [InventoryBackup1], [InventoryBackup2], [InventoryBackup3] WITH FORMAT, MEDIANAME = N'InventoryStripeSet', MEDIADESCRIPTION = N'3 Devices = InventoryBackup1-3'

If you decide to use parallel striped backup to disk devices, you must make sure that each independent device has enough space to handle the appropriate portion of the database. Remember that files of filegroups do not always fill evenly. If one of the devices runs out of space, the backup will terminate.

Multifile Backups

After you have created a backup device or backup devices in a media set, you might want to save multiple backups to the same device(s). In fact, if you use the syntax to back up to a device without specifying any options other than the database and the devices to which you would like to back up, SQL Server automatically appends the new backup to the backup device. This has both advantages and disadvantages. If you back up the same database to a backup device three times over the course of a week, for example, and then you need to restore your last backup, you would probably do this:

On Monday, you execute the following:

BACKUP DATABASE [Inventory] TO [InventoryBackup1], [InventoryBackup2], [InventoryBackup3]

On Wednesday, you execute the following:

BACKUP DATABASE [Inventory] TO [InventoryBackup1], [InventoryBackup2], [InventoryBackup3]

On Friday, you execute the following:

BACKUP DATABASE [Inventory] TO [InventoryBackup1], [InventoryBackup2], [InventoryBackup3]

The following Monday, you decide you want to restore the Inventory database to its state at the Friday backup, so you execute the following:

RESTORE DATABASE [Inventory] FROM [InventoryBackup1], [InventoryBackup2], [InventoryBackup3]

And you restore Monday s backup, not Friday s. This mistake is one of the most common user errors when using multifile backups. To restore the appropriate backup, you must specify the backup based on position within the multifile backup device(s). To see the list of backups performed to a backup device(s), you can use the RESTORE HEADERONLY command, which has the following syntax:

RESTORE HEADERONLY FROM [InventoryBackup1]

This lists all of the backups (in this case, three rows) that have been performed to this device or these devices. You need to specify only one device because SQL Server can access the header from any of the devices of a parallel striped media set. After you list the header, you can use the position column to determine which backup must be restored. In this case the position is 3, so the restore command would use the following:

RESTORE DATABASE [Inventory] FROM [InventoryBackup1], [InventoryBackup2], [InventoryBackup3] WITH FILE = 3

If you would like to overwrite the contents of backup devices while backing up, you can do so by using the INIT option instead of appending. Because appending (NOINIT) is the default, you must specify when you want to overwrite. INIT only works when you are overwriting a media set with exactly the same format (which means the same number of backup devices) and when the backup set does not have a required retention period. If you want to break up a media set, which is changing the number of backup devices, you must use WITH FORMAT. If the backup set has a required retention period and you want to INIT the devices before the retention period has been met, you can tell SQL Server to SKIP the backup device headers.

Useful Backup Options

Although many backup options are offered , not all are available for each individual backup method (for example, the password options are not available through SQL Server Enterprise Manager). For more details, spend some time testing and working with your own server. The options are as follows:

Defining a media set

Use the following:

[[,] FORMAT NOFORMAT] [[,] MEDIADESCRIPTION = {'text' @text_variable}] [[,] MEDIANAME = {media_name @media_name_variable}] [[,] MEDIAPASSWORD = {mediapassword @mediapassword_variable}]

Format implies INIT and SKIP. FORMAT can be used to break up a striped media set; however, use caution because FORMAT renders all devices unusable. Every media set should have a descriptive name as well as a description that details the number and name of the devices that make up the media set. If a password is used on the backup, a media set password will need to be supplied for every restore. Good naming conventions can save time and minimize errors in disaster recovery.

Defining an individual backup

Use the following:

[[,] NAME = {backup_set_name @backup_set_name_var}] [[,] DESCRIPTION = {'text' @text_variable}] [[,] PASSWORD = {password @password_variable}]

Every backup should have a name and a description. If you have issues with the physical security of your backups or backup media, consider using a password. This password is for the individual backup, not the media set. Good naming conventions can save time and minimize errors in disaster recovery.

Manipulating the media or backup device

Use the following:

[[,] INIT NOINIT] [[,] NOSKIP SKIP] [[,] EXPIREDATE = {date @date_var} RETAINDAYS = {days @days_var}] [[,] STATS [= percentage]]

INIT initializes backup devices only when they are in the correct structure. INIT does not allow the backup to proceed if the backup device was previously used in a media set and has not been reformatted. You must use FORMAT to break up the media set. NOINIT is the default, meaning that every backup is automatically appended to the backup devices. EXPIREDATE and RETAINDAYS set the backup retention period. EXPIREDATE is the date when a backup can use INIT ”to the same device ”without error. RETAINDAYS is similar, except the retention period is set as the number of days that must pass before the backup can be overwritten with INIT. Neither option invalidates the backup or prevents it from being restored once the time has expired . These options are effectively retention settings, not expiration dates.

Important

All bets are off and all expiration options are discarded if FORMAT is used or if SKIP is used. If you use FORMAT, it implies the use of SKIP.

The STATS option defines when progress messages are returned from a backup. Using WITH STATS when performing backups manually, as well as when automating backups, provides a mechanism to see the progress of a backup.

Working with tape devices

Use the following:

[[,] NOREWIND REWIND] [[,] NOUNLOAD UNLOAD] [[,] RESTART]

NOREWIND is important to use if you plan to perform automated multifile backups to tape. However, it is not essential. Specifying NOREWIND allows SQL Server to leave the tape heads in position to perform (and append) the next tape backup where the previous one left off. Therefore, you save time when backing up (or restoring from) tape. NOREWIND implies NOUNLOAD. However, if only NOUNLOAD is specified, the tape will still rewind. This can be useful if you want to backup and then immediately restore. Finally, if a power failure were to occur during the backup, you could use RESTART to resume the backup at the point at which it failed.

Important

NOREWIND holds the tape device locked, so other applications cannot use it.

Third-Party Backup and Restore Tools and SQL Server

Some third-party hardware and tools, such as enterprise backup software or storage area network (SAN) devices, can back up your file systems and databases as well. This might simplify management in an organization because all backup-related work is standardized, bypassing learning syntax for each product you run. However, it is still helpful to know the syntax because it is essentially what is issued behind the scenes for whatever technology you employ .

Nevertheless, you must understand that a normal backup tool cannot just back up an active SQL Server database file. SQL Server must be accessed properly so that when the software starts a backup, SQL Server believes and responds as if a native backup is occurring. Otherwise , you might damage your databases, obtaining an inconsistent set of database files on restore. To enable SQL Server to believe a native backup is happening, device drivers and software must utilize the SQL Server Virtual Backup Device (VDI) API. You must check with your preferred software or hardware vendor prior to implementing or purchasing a solution to ensure that it not only works properly with your servers running SQL Server instances, but also supports the VDI. Similarly, if your system administrators use a program already for performing backups and now want to use it to back up your live SQL Server databases, verify that it is capable of doing so.

If you intend to use a third-party program that does not support the SQL Server VDI, one of the best options to integrate it into your SQL Server backup strategy is to have a SQL Server Agent job back up the database to a specified disk, and then have the program back up the SQL Server “generated file. Other variations on this theme obviously exist, but this example illustrates that you can leverage your existing backup solution even if it cannot back up your SQL Server databases directly. If the program does support the VDI, you might want to use it to manage your entire backup strategy. Mixing SQL Server Agent jobs or manual backups might interfere with your packaged solution. You should choose one strategy and stick to it.

More Info

For information on the SQL Server 2000 VDI, go to http://www.microsoft.com/sql/downloads/virtualbackup.asp .

Storage Assisted Backups

Your storage vendor might provide technologies that greatly enhance your ability to back up and restore large amounts of data quickly, especially in a SAN environment. SQL Server 2000 supports these technologies through extensions to the VDI previously mentioned. Relative to SQL Server, a storage assisted backup is usually referred to as a split-mirror backup.

Note

Although SQL Server 2000 supports these hardware-based backups through the VDI, the technology itself is not built into SQL Server. Check with your storage vendor to see whether any storage assisted backup options are available to you.

Split-Mirror Basics, Pros, and Cons

A split mirror is pretty much what it sounds like: You take one of your RAID mirrors and separate it from the others. You can combine a storage assisted backup with conventional database backups to accomplish rolling your database forward to the point of failure or to an arbitrary point in time ”or both. Like any other type of database backup, the history is stored in msdb if the application writes backup history there. The utility provided by the storage or backup vendor might automatically determine the files that comprise the database and determine which volumes to capture. In other cases, you might have to supply a list of volumes to the utility.

In terms of usage, a split-mirror backup is applicable to media-failure scenarios as well as application-error or user-error scenarios. From a high availability perspective, split-mirror backups are beneficial because the process is usually measured in seconds, and not minutes, hours, or even days. Imagine trying to back up a petabyte of information using another method ”on any database platform for that matter ”in a matter of seconds. The same principle is applicable on a restore; what you gain in speed might be worth the additional cost of the solution.

Another good use of a split-mirror backup is to initialize a log shipping secondary. If you have a large database, again, you need to weigh the risks and rewards. But if you need to initialize a 1 TB database as your base database for log shipping, virtually no other method can perform the task more quickly because using a form of a normal restore, at some point, no matter how much you tweak disk I/O and such, you are still trying to restore 1 TB of data. Along the same lines, if you need to initialize a test or development database housing your large production VLDB, as with the log shipping secondary, there are very few ways to restore a large database easily.

Pros

Split-mirror backups can virtually eliminate the time it takes to accomplish the backup itself as well as the data copy phase of a restore, which is the most time-consuming aspect. The exact benefits depend on the vendor s implementation. If the production disks are reconciled with the clone in the background, the restore itself is almost instantaneous. So from both a SQL Server and high availability standpoint, this is probably the biggest advantage. The database needs only to be rolled forward to the target time and recovered. It is then available for updates while the disks are reconciled in the background. And because backups are performed on a separate mirror that is already in sync at the time of the split, you get the benefit of no I/O hit for reading the data and then writing to a file. Therefore, you reduce the impact of the entire backup process on a live production system.

Cons

Ultimately, the biggest drawback of implementing a split-mirror solution is the cost. These solutions are usually out of the price range of most small and midsized companies, and even some large companies. The biggest cost is obviously the number of disks. If your disk solution already has, say, 48 disks, with two stripes of 24, you need to add another 24 for a total of 72. Assuming for the sake of this example that each disk costs US$1,000 because of its large capacity and high speed, that is US$72,000 in just physical disks alone. Then you need to consider the enclosures, the racks to house them, all cabling, proper cooling, and so on ”you get the idea.

Furthermore, you need to ensure that your staff is properly trained in the use of split mirrors. Unlike a normal SQL Server backup in which you are dumping a file to a disk or tape, this is a specialized solution. Even if you have the money, do not implement a backup solution that you cannot manage.

Unlike a normal backup to disk, split mirrors really have no concept of versioning. Once you split the mirror, and then remirror , that backup you made by doing the original split is gone unless you backed it up to tape or disk elsewhere. You will therefore need to manage your backups more carefully if nothing else is employed. Other caveats specific to each process are outlined next.

Using a Split Mirror for Database Backups

If you employ split mirrors, a backup is accomplished by physically splitting one mirror away from the others at the disk level. This mirror contains a copy of the data at the time of the split. This mirror can be referred to by different names depending on the storage vendor. Some names are clone , snapshot , or business-continuance volume (BCV). The mirror, together with the small amount of descriptive data created by SQL Server, is the backup.

The benefit of splitting one mirror off is that it is a fast operation. SQL Server briefly stops all writes to the database while the split is initiated and completed so that you will not have torn pages in your database. This process should usually be measured in seconds and is vendor-dependent in terms of timings and implementation. The availability of the database should not be affected except for the brief stop to ensure consistency. If you have a VLDB, a split mirror might be the only way to achieve some of your service level agreements (SLAs) if you cannot use a traditional backup and restore scenario that is outlined in this chapter.

Important

Use a minimum of a three-way mirror to ensure that your production disk subsystem will still be highly available. Using only two mirrors leaves you exposed if you encounter a disk failure. Work with your storage vendor to ensure that your configuration is optimal for both SQL Server and the hardware level for using an advanced technology such as a split mirror.

Remirroring the Disk Stripe

To use the disk stripe that was split, you must remirror it with the active disk mirrors to update it. During the remirroring process, the stripe data is copied from the active mirror(s) at a low level to synchronize it; the process is not handled by SQL Server. Your database is completely available for use during this process. However, especially on large databases, your other mirrors will be active, so you might see some sort of performance impact on your database because the stripe remirroring will be reading from the other disk stripes. Some storage vendors can perform the reconciliation by copying only what has changed, and others will synchronize everything. Many will allow you to prioritize the remirroring against being able to run your daily workload on the same set of disks.

Warning

If you are employing a split mirror in conjunction with SQL Server 2000 failover clustering, when the remirroring process occurs, you must guarantee that the disk signatures will not be altered. If they are altered , you can damage your cluster. Talk to your hardware vendor, because this mistake could be painfully expensive.

Using a Split Mirror to Restore Databases

Performing a restore with a split mirror is similar to a remirror, but the actual data copy process flows the other way. The mirror is the master and the production volumes are synchronized with it. As part of the restore, the backup utility gives SQL Server the proper description of the backup being restored, as well as the appropriate restore options, such as NORECOVERY. The database might be rolled forward using conventional differential, file-differential, transaction log backups, or all three after main file is restored.

Ideally, the data is presented to SQL Server immediately, and the reconciliation occurs in the background. In this case, the restore occurs in a matter of seconds, and the database can be recovered and made available immediately. This functionality is the primary benefit of implementing a split mirror.

Planning Considerations for Split Mirroring

When you want to implement a split-mirror backup solution for SQL Server, consider the following:

Windows Server 2003 Volume Shadow Copy Service and SQL Server 2000

A new feature of Windows Server 2003 is the Volume Shadow Copy Service (VSS). It is a volume-oriented, snapshot-based backup. Windows Server 2003 also ships with something known as the SQLWriter, which is the back-end component of VSS that interacts with SQL Server. Under Windows, the writer is displayed as MSDEWriter. This can be verified by executing a VSSADMIN LIST WRITERS command in a command window.

SQLWriter is a VDI-based application that utilizes the VDI s BACKUP WITH SNAPSHOT support. Because of the SQLWriter, you can use VSS with SQL Server 2000 to perform full backups. All recovery models are supported, and NORECOVERY allows you to roll the database forward. Differential, transaction log, and file backups are not supported with VSS. However, full database backups made with VSS can be combined with any other native backup strategy (such as transaction log backups or a third-party tool and so on) to handle the rolling forward after the VSS backup is restored to your instance.

Important

For user databases, you can restore them to a live SQL Server instance, but for system databases (master, model, and msdb), you have to stop SQL Server to restore the VSS backup. VSS is fully supported in a server cluster.

Note

Due to the timing of the release of Windows Server 2003 and the writing of this book, there is not a lot of information available on implementing VSS with SQL Server 2000. Check http://www.microsoft.com for updated information.

Executing the Full Database “Based Backup Strategy Using Transact-SQL

You can execute backups in many ways, but ultimately it all boils down to the syntax of the command being executed. Whether you use SQL Server Enterprise Manager to set up the backup or type it in yourself, it is just a command. This brief section shows examples of how to use the syntax to create your backup.

On the CD

There is a script included, Full_Database_Based_ Backup_Strategy.sql, which is an example of a full database backup and restore and is detailed in this section.

Executing a Full Database Backup

When using the full database “based backup strategy, you always use a full backup to create the recovery set. This backup will be the first one used during the restore. To create a full database backup, you must use the BACKUP DATABASE command and all of the appropriate options. In the following example, a full database backup will be performed to three devices in parallel. The backup set will have a defined media set name, description, and password to protect the media set, a password to protect this specific backup, and the backup will be performed to tape devices so that the next backup can immediately append. Additionally, this backup will be set to prevent INIT for seven days and it will return the status of the backup every 10 percent backed up. The syntax for this command is:

BACKUP DATABASE [Inventory] TO [InventoryBackup1], [InventoryBackup2], [InventoryBackup3] WITH FORMAT, MEDIANAME = N'InventoryStripeSet', MEDIADESCRIPTION = N'3 Devices = InventoryBackup1-3', MEDIAPASSWORD = N'InventoryStripeSetPassword', RETAINDAYS = 7, NAME = N'InventoryBackup', DESCRIPTION = N'Full Database Backup of Inventory', PASSWORD = N'InventoryBackupFullDBPassword', NOREWIND, STATS = 10

Executing a Transaction Log Backup

As the most important type of backup to perform, transaction log backups are critical to maintaining an optimal database size and allowing the full spectrum of recovery options. Transaction log backups ”like all others ”should be automated. In fact, to make a system highly available you will automate all of these backups to run at a consistent and frequent interval. Transaction log backups are the most frequent. In the upcoming section Simplifying and Automating Backups, you will automate three different situations that trigger a transaction log backup to occur.

To create the transaction log backup you use a very similar command (BACKUP LOG) with many of the same options as BACKUP DATABASE used. To perform against the same three devices that the full database backup used, you submit the media set password. The media set password is required for all commands that need to access this media set. Without the password you will receive the error Access is denied due to a password failure. To create an individual backup password for the transaction log backup, to leave the tape ready for another backup, and to return stats to know the status of the transaction log backup, use the command listed next.

Note

Using a password is not mandatory. In the big picture, although it will secure the backup in SQL Server, it only prevents access by SQL Server tools. A disk-based backup is more exposed than one on tape.

BACKUP LOG [Inventory] TO [InventoryBackup1], [InventoryBackup2], [InventoryBackup3] WITH NOINIT, MEDIAPASSWORD = N'InventoryStripeSetPassword', NAME = N'InventoryTLogBackup', DESCRIPTION = N'Transaction Log Backup of Inventory', PASSWORD = N'InventoryBackupTlogPassword', NOREWIND, STATS = 10

Executing a Differential Database Backup

Executing a differential database backup is actually exactly the same as executing a full database backup, except that you back up only the extents that have changed. You use the same command with one additional clause added: WITH DIFFERENTIAL. Following the same command as the full database backup (removing the media set definition parameters because they are necessary only on the first backup) ”including STATS for progress and this time telling SQL Server to rewind and unload (unload implies rewind) the tape ”use the following syntax:

BACKUP DATABASE [Inventory] TO [InventoryBackup1], [InventoryBackup2], [InventoryBackup3] WITH DIFFERENTIAL, NOINIT, MEDIAPASSWORD = N'InventoryStripeSetPassword', NAME = N'InventoryDiffBackup', DESCRIPTION = N'Differential Database Backup of Inventory', PASSWORD = N'InventoryBackupDiffPassword', UNLOAD, STATS = 10

Executing the File-Based Backup Strategy Using Transact-SQL

The commands for executing the file-based backup strategy are exactly the same for all parameters immediately after the TO portion of the BACKUP command, including the device list, media set information, tape option, password protection, and retention options. The file-based strategy differs before the TO clause. To perform file-based and filegroup-based backups, specify the database you are backing up with the BACKUP DATABASE command. Before you state the devices, you must specifically state the subset of the database you want based on the file (or files), the filegroup (or filegroups), or both.

When using the file-based backup strategy, you must create a complete set of all files by backing each file up individually, or by backing up groups of files as defined by your database s filegroups. During the restore process, you always start with the file or filegroup backups and then roll forward using other backup types. To create file or filegroup backups, you must use the BACKUP DATABASE command and specify the files, filegroups, or both that you want backed up in this backup set.

On the CD

To create the File_Based_Backup_DB sample database and all the backups shown in the case study diagram shown in Figure 10-1, run the File_Based_Backup_Strategy.sql script, which you will find on the CD that accompanies this book. You can execute this script in its entirety, but you should work your way through the script, slowly reviewing the syntax to fully understand the backup strategy. For best understanding, review the syntax descriptions from this section prior to execution.

The example uses seven database files: a primary file (.mdf), three nonprimary data files in a filegroup named RWFG (used for read-write data), two files in a filegroup named ROFG (used for read-only data), and one transaction log file. After creating the FileBasedBackupDB database, the script modifies data between each of the backups. The FileBasedBackupDB figure shows the backup types from left to right in this sequence (the number corresponds to the number along the timeline).

Figure 10-1: File-based backups offering significant flexibility.

  1. Full file backup of the primary file

  2. Transaction log backup

  3. Full filegroup backup of the RWFG filegroup

  4. Transaction log backup

  5. Full filegroup backup of the ROFG filegroup

  6. Transaction log backup

  7. Differential filegroup backup of the RWFG filegroup

  8. Transaction log backup

  9. Full file backup of the primary file

  10. Transaction log backup

  11. Differential filegroup backup of the RWFG filegroup

  12. Transaction log backup

Using the example shown for FileBasedBackupStrategy.sql, the first backup is a file backup of the primary file named FileBasedBackupDBPrimary. Because all of the options for tape backup, media set names, descriptions, and passwords are the same, the syntax is kept to a minimum. The syntax for the file backup at point in time 1 is as follows:

BACKUP DATABASE [FileBasedBackupDB] File = N'FileBasedBackupDBPrimary ' TO [FileBasedBackupDev] WITH NAME = N'FileBasedBackupDB Backup', DESCRIPTION = N'File = FileBasedBackupDBPrimary', INIT

Transaction log backups are an integral part of the file and filegroup strategy because they ensure transactional integrity of a backup when it is restored. Transaction log backups can use the same device or devices as the database backups when necessary. In the script, a transaction log backup occurs at every even position: 2, 4, 6, 8, 10, and 12. Each transaction log backup uses the exact same syntax:

BACKUP LOG [FileBasedBackupDB] TO [FileBasedBackupDev] WITH NAME = N'FileBasedBackupDB Backup', DESCRIPTION = N'Transaction Log', NOINIT

The next backup type, shown in position 3, is a filegroup backup. The RWFG filegroup backup is performed with the following syntax:

BACKUP DATABASE [FileBasedBackupDB] FILEGROUP = N'RWFG' TO [FileBasedBackupDev] WITH NAME = N'FileBasedBackupDB Backup', DESCRIPTION = N'FileGroup = RWFG', NOINIT

In the backup shown in position 5, the ROFG filegroup is backed up with the following syntax:

BACKUP DATABASE [FileBasedBackupDB] FILEGROUP = N'ROFG' TO [FileBasedBackupDev] WITH NAME = N'FileBasedBackupDB Backup', DESCRIPTION = N'FileGroup = ROFG', NOINIT

In the backup at positions 7 and 11, a filegroup differential is chosen for the RWFG. To perform a differential backup, the syntax is exactly the same as a file or filegroup backup, with the addition of the WITH DIFFERENTIAL clause. The syntax for the RWFG filegroup differential backup is as follows:

BACKUP DATABASE [FileBasedBackupDB] FILEGROUP = N'RWFG' TO [FileBasedBackupDev] WITH DIFFERENTIAL , NOINIT, NAME = N'FileBasedBackupDB Backup', DESCRIPTION = N'FileGroup DIFFERENTIAL = RWFG'

The syntax for the file-based strategy is just as straightforward as the syntax for backing up databases using the full database “based strategy. The complexities with this strategy are not in the backup, but instead in the recovery process.

Caution

You must perform significant testing so that all possible recovery paths are implemented properly. If even one file is missing, the database cannot be recovered from backups.

Simplifying and Automating Backups

The Transact-SQL syntax is not overly complex once you get a feel for it. However, creating a backup plan is hardly about syntax. To create an optimal strategy, you must have a recovery-oriented plan that is well tested and well defined ”and most important ”automated. Backups should never be handled manually because they will be more prone to human error or might even be forgotten. To ensure optimal recovery, implement a consistent and automated plan to handle backups. One of the easiest ways to perform this task is by creating automated jobs using the SQL Server Agent.

For each backup type you choose, you should implement a job to automate that backup type at the necessary frequency. As a simple first step, you can use SQL Server Enterprise Manager to help you create an automated backup schedule for your full database backup. To create a full database backup that runs weekly, right-click your database, select Tasks, and then select Backup Database. Select all of the options you want, as if you were actually going to perform the backup. Before you click OK, however, select the Schedule check box, as shown in Figure 10-2.

Figure 10-2: The SQL Server Backup - Inventory dialog box to simplify creating a scheduled backup.

You can set the schedule now by clicking the ellipses () button, or you can set it later, after the job is created. Instead of performing the backup, SQL Server creates a job with the chosen schedule when you click OK. The backup is not performed; only the job is created as long as you have the Schedule check box selected. Once created, you can add additional steps such as sending e-mail regarding the completion or failure of the backup and modifying additional properties about your backup.

To demonstrate the simplicity of setting up jobs using the SQL Server Enterprise Manager backup user interface, the following example creates a backup of the transaction log that will be automated to run every 10 minutes. The time interval you choose depends on several criteria:

Because it is a time-based backup, some transaction log backups will be larger (such as those made during working hours) and some will be smaller (such as those made during off hours when less work is being performed). To check the percentage of the log currently used, run the DBCC SQLPERF(LOGSPACE) command. This command shows you percentages used and free for all transaction logs on your SQL Server instance:

DBCC SQLPERF(LOGSPACE)

To automate the transaction log backup for the Inventory database, as shown in the previous examples, right-click your database, choose All Tasks, and select Backup Database. On the General Tab (shown in Figure 10-2), enter a name, description, and all of the backup properties.

Once you have set the options, click (ellipses) to set the schedule for the transaction log backups. The Edit Schedule dialog box opens, as shown in Figure 10-3.

Figure 10-3: The Edit Schedule dialog box to define backup frequency.

Once the schedule has a name, select Recurring. On this tab, shown in Figure 10-4, you can set the exact frequency of your backups. Once you create the job, you can create multiple schedules for the execution of the job: for example, weekly full backups, the second Wednesday of every month, or the first or second weekend day of the month. Make sure you investigate all of the possibilities in these dialog boxes. The smallest granularity is 1 minute.

Figure 10-4: The Edit Recurring Job Schedule dialog box to provide numerous scheduling possibilities.

Once you have set all of the options, click OK to create the scheduled job. To review the job, modify its properties so you can modify the job if you want. (For example, you cannot set the media set name or password within this dialog box.) All jobs are executed by SQL Server Agent, which should be set to Auto-start. In SQL Server Enterprise Manager, expand Management, click SQL Server Agent, Jobs, and then double-click the Inventory Transaction Log Backup job. Here, you can set properties for completion notifications (whether successful or failed) by using SQL Mail ”using e-mail, e-mail-based paging, or the NET SEND command. You can add additional steps to the backup job, and you can build complex jobs that include custom external executables or other applications.

For the job in this example, the execution occurs only at the scheduled time. With something fairly frequent, you might think that everything is covered. Unfortunately, some transaction-log-related events could occur that could cause you downtime, for example, if a long-running transaction occurred and filled the log. When the transaction log fills, all activity is stopped . Luckily, this problem is relatively simple to fix: back up the transaction log to free up some space. In this case, you want the transaction log backup job to execute at an unscheduled time, on the log full error (error 9002 “ The log file for database dbname is full. Back up the transaction log for the database to free up some log space.).

This process might seem complex, yet it is extremely easy using an alert. In addition to supporting regularly scheduled jobs, the SQL Server Agent also has alerts. An alert is a reaction to an event that has occurred in SQL Server. Errors of a higher severity ”those written to the Windows Event Viewer s Application Log ”are errors on which you can define an alert. In fact, you can even create your own user-defined errors that are logged.

To set up the alert to perform a transaction log backup when the Inventory database s transaction log is full, use SQL Server Enterprise Manager. Expand Management, select SQL Server Agent, select Alerts, right-click Alerts, and select New Alert. In the New Alert Properties dialog box shown in Figure 10-5, type the name of the alert and select the kind of event that should trigger it ”error 9002. Also, be sure to select the database in which this error should be triggered. (Yes, you must set up an alert for each of your production databases.)

Figure 10-5: The New Alert Properties dialog box to trigger transaction log backups when the transaction log is full.

The Response tab of the New Alert Properties dialog box is important for two reasons: here, you set the job to execute as the response and define how long it will take for SQL Server to respond. More specifically, setting the delay between responses is the most important option to set appropriately. When an error occurs, SQL Server must have time to see the error and respond to it. SQL Server reacts quickly to the error being raised, yet the response might take minutes to run. If a transaction log backup takes roughly 4.5 minutes to execute, setting the delay between responses to 4 minutes and 30 seconds minimizes the number of times this alert is fired . SQL Server fires another alert only if another log full message is issued after the 4 minutes and 30 seconds have passed.

What if you think your system is well automated, but when the log fills, you have downtime? You can set a transaction log backup to occur just before the log fills, preventing the transactions from failing. Not only can you create alerts to react to SQL Server errors, but you can create alerts to react to performance monitor counters, such as Percent Log Used. In the next error, you create the same response but this time, the setup for the alert is a bit different. Again, select New and then Alert from the SQL Server Agent node of the Management folder Enterprise Manager. In the New Alert Properties dialog box, shown in Figure 10-6, change the default type from SQL Server Event Alert to SQL Server Performance Condition Alert. The options to define the alert change, allowing you to set the exact conditions under which the response should be triggered.

Figure 10-6: The New Alert Properties dialog box set to trigger transaction log backups when the transaction log is 85 percent full.

After you create this job and define the two alerts, this database is less likely to go down because of log full errors. Make sure you monitor long-running transactions and get a sense of what could potentially cause the log to fill. The end result is that the Inventory Transaction Log Backup Properties dialog box (Figure 10-7) shows three schedules: the recurring job schedule, the SQL Server event alert, and the SQL Server performance condition.

Figure 10-7: Inventory Transaction Log Backup schedules.

Creating a Production SQL Server Agent Backup Job

Creating a production SQL Server Agent backup job is deceptively simple.

On the CD

A diagram with the flow of an automated SQL Server Agent job that runs a full database backup nightly can be found in Agent_Backup_Job.pdf.

The specific details for each of the seven steps found in the Visio diagram are provided here.

Note

Please note that you can use your preferred method for alerts, such as SQL Server s built-in alerts, but using operating system shell commands or other tools might present a security risk to your environment. This example shows other tools to demonstrate how you can integrate them into your SQL Server workflow.

  1. Disable the Transaction Log Backup. Because full database backups pause transaction log backups, the first step within this automated job disables the transaction log backup that normally runs every 10 minutes. You do not need to disable the job, but this simplifies errors created by the transaction log backup job not succeeding. As soon as the full database backup completes, the transaction log backup job will be re-enabled. From the msdb database, the transaction log backup is disabled using the sp_update_job system stored procedure:

    sp_update_job @job_name= N'Transaction Log Backup...', @enabled= 0

  2. Execute a full database backup. This backup uses three LTO tapes for backing up this VLDB in parallel. The syntax of the command is:

    BACKUP DATABASE Inventory TO LTO1, LTO2, LTO3 WITH FORMAT, UNLOAD, STATS = 2

    Step 2 uses the Advanced tab to determine the next step (see Figure 10-8). If there is success, then go to Step 3. If there is a failure, then go to Step 4. Additionally, the output of the backup stats will be directed to a file using the Output File box to generate information that is used as part of the backup status e-mail.

    Figure 10-8: SQL Server Agent job step detail.

  3. On success, e-mail operations that all is OK ”additionally, page the on-call support person to let him or her know the backup completed successfully. For this job a custom SMTP-based e-mail application is used to e-mail the text of the backup output. The operating system command executed is:

    d:\batchjobs\common\SMTP.exe "operations_email" "LTO backup complete" "Please check file:\server\d$\mssql\reports\backup.txt" If the mail was sent OK, go to Step 5; otherwise run Step 4.

  4. On failure, e-mail operations that the backup failed and needs immediate attention. The on-call support staff is also paged to let them know a failure occurred.

    d:\batchjobs\common\SMTP.exe "alert" "URGENT: backup failed" "Please call operations & check file:\server\d$\mssql\reports\backup.txt"

  5. Page the on-call analyst and let him or her know that the backup succeeded. The command also used a special application created for their paging application:

    d:\batchjobs\common\cpage.exe oncall "LTO Backup Completed"

  6. Page the on-call analyst and let him or her know that the backup failed:

    d:\batchjobs\common\cpage.exe oncall "LTO backup failed!"

  7. Either way, re-enable the transaction log backup. From the msdb database, the transaction log backup is disabled using the sp_update_job system stored procedure:

    sp_update_job @job_name= N'Transaction Log Backup...', @enabled= 1

The details of the steps as seen in the SQL Server Agent are shown in Figure 10-9.

Figure 10-9: SQL Server Agent backup job.

Checking the Completion of a Backup

When a backup job is being run using SQL Server Agent, it is tough to see how complete the job is as it is running because there is no graphic representation. However, if you use the STATS option with the BACKUP command, you can see the percentage completed using the DBCC OUTPUTBUFFER command against the session performing the backup. The percentage complete is shown in the right-hand column of the output.

To see the percentage completed you must first know the system process ID (SPID) of the backup process. To get the SPID of the backup use the following query:

SELECT DISTINCT(spid) FROM master.dbo.sysprocesses (nolock) WHERE cmd LIKE 'BACKUP%'

Output:

spid ------ 742

Next, use the DBCC OUTPUTBUFFER command with the backup process SPID:

DBCC OUTPUTBUFFER(742)

When reviewing the output, the far right column shows the character values for the hexadecimal output. This is where you can see the last percentage returned by the job. The following is an example output showing that the backup is at 3 percent:

Output Buffer ----------------------------------------------------------------------------- 00000000 04 00 00 5d 02 e6 03 00 79 01 00 00 00 ab 44 00 ...]...y....D. 00000010 8b 0c 00 00 01 00 14 00 33 00 20 00 70 00 65 00 .......3. .p.e. 00000020 72 00 63 00 65 00 6e 00 74 00 20 00 62 00 61 00 r.c.e.n.t. .b.a. 00000030 63 00 6b 00 65 00 64 00 20 00 75 00 70 00 2e 00 c.k.e.d. .u.p...

Verifying Backups

Once you have performed your backups and likely automated them, it is critical that you periodically test that your process and strategy is occurring. These verification procedures do not guarantee that a restore will be successful, but they do guarantee that you have the backups you are expecting to have.

Warning

There is only one way to truly test your backup strategy ” perform periodic and complete restore testing on a test system.

In the interim, there are four ways you can verify your backups:

The BackupType column can have one of the following values:

The Position column refers to the number of backups performed against this device (or media set). During a restore, this is the most important column to understand for multifile backups. If the wrong restore is performed, this could lead to additional downtime and frustration. The syntax to view the header information is:

RESTORE HEADERONLY FROM BackupDevice

Implementing an Effective Backup Strategy: In Summary

Here s a summary of what you must to do implement an effective backup strategy:

  1. Determine the acceptable amount of downtime, if any.

  2. Determine the acceptable amount of data loss, if any.

  3. Determine the priority of other operations such as bulk operations and log shipping.

  4. Determine your recovery model ”static or changing for batch operations.

  5. Design your database using filegroups, if necessary.

  6. Consider various strategies, determining which pros and cons are appropriate to your database environment.

  7. Perform scenario-based studies based on the risks you have assessed to see if your strategy works.

  8. Implement it in a test environment.

  9. Test it.

To really put the backup strategy to the test you need to determine if you can recover to the point in time of the failure with little to no data loss within your defined downtime interval. In the next section you will get a better understanding of how to react quickly and effectively to help minimize the overall downtime you will suffer due to each type of failure.

Related

Категории