19.4. BackupsWith SQL Server 2005, you can usually perform most backup operations without worrying about performance impact to users. In previous versions, greater consideration has to be given to your database architecture and backup plan so performance is not degraded. Backups can be performed at any time, but if the server is currently trying to create or delete a file, the backup is postponed until these operations are complete. Additionally, when a backup is in progress, a database cannot be created or deleted. Even though backups are not supposed to impact performance, it is always a good idea to schedule backups during the least busy time for your database. With SQL Server, you can use many different backup techniques to minimize your downtime. This includes the creation of hot, warm, and cold standby servers. Depending on the budget and using various mirroring, log shipping, and restore processes, it is possible to create scenarios where downtime ranges from nonexistent with mirroring to possibly several hours with cold standby to someplace in between with log shipping. 19.4.1. Backup DevicesBackup devices can be any type of media, such as disk, tape, removable storage, or network storage (such as SAN or NAS). In previous versions of SQL Server, it was necessary to explicitly define a backup device before making a backup. In SQL Server 2005, backup devices are created on the fly. However, it is usually considered good practice to specifically define backup devices for continuity and simplification of administration.
19.4.1.1. Logical and physical devicesBackup devices can be identified by either their physical or logical name. The physical name is the name as it would appear to the operating system, such as d:\backups\backup.bak. The logical name is simply an alias for the physical device name. The mapping of the logical device name to the physical device name is stored within the system tables. The logical name is simply an easier reference to the physical device name. 19.4.2. Recovery ModelsA SQL Server recovery model helps in planning what to back up and how best to perform that backup. There are three primary recovery models available: simple, full, and bulk-logged. It is best to verify the recovery model in use for a particular database before beginning your backup and restore operations because the type of backup needed may be dictated by this setting. The recovery model affects how much data can be restored and the performance and duration of the restoration.
The recovery model can be viewed from the GUI or by Transact-SQL. 19.4.2.1. SQL Server 2005To find out the recovery model for SQL Server 2005, run the sp_helpdb stored procedure: sp_helpdb Database_name Look in the status column for the RECOVERY= section that contains the name of the current recovery model. Alternatively, you can execute the following Transact-SQL query: select name, recovery_model from sys.databases; This shows you a listing of all databases, followed by the number 1, 2, or 3 in the recovery_model column. The integer in the recovery_model column indicates one of the following values:
To see the current recovery model in the Management Studio, follow these steps:
19.4.2.2. SQL Server 2000One option for finding the recovery model in SQL Server 2000 is to run the sp_helpdb stored procedure: sp_helpdb Database_name The status column for the RECOVERY= section will contain the name of the current recovery model. Alternatively, you can execute the following Transact-SQL query: select databasepropertyex('database', 'recovery') To see the current recovery model in the Enterprise Manager:
The recovery model can be seen in the Model pull-down menu. 19.4.3. Backup TypesThere are many different types of backups in SQL Server. Each has advantages and disadvantages depending on your backup strategy. 19.4.3.1. FullA full database backup is exactly what the name suggests; it creates a full backup of the specified database. This includes all objects, system tables, data, and portions of the transaction logfiles. This type of backup allows you to completely restore the server to the state when the backup finished. 19.4.3.2. DifferentialA differential backup contains all the changes since the most recent full backup. An initial differential backup with a relationship to the full backup is called the differential base. Over time, the differential grows, often getting close to the size of the differential base. It is therefore important to schedule both regular full backups and differentials. 19.4.3.3. Transaction logIt is important to back up the transaction log because all transactions are written to this log before ever being committed to the database. Backing up the transaction log allows you to recover the server to the most recent time since the failure. There are three different ways to back up transaction logs: a full or pure backup, a bulk log backup and a tail log backup. A full (or pure) backup contains the full transaction log for a given interval and does not include any bulk changes. Note that with this backup type, PIT recovery is not an option. A bulk log backup is similar to the full/pure, except it does contain transactions changed by any bulk operations. The tail log backup method should be used if corruption of the database is suspected. This backs up the transaction log records that have not yet been backed up by either of the previous two methods. This backup method can contain either regular or bulk logged data. When transaction logs are restored, they are always applied in sequence, starting from the oldest and moving to the most recent. This playback is done after the most recent full or differential backup is restored and then continues through all the logs. This sequence of logs is called a log chain. The log chain must be intact for the entire restore process to work.
19.4.3.4. Copy-only backupCopy-only backups provide a convenient way to take a copy of an existing database to use on another server. Copy-only backups do not reset the backup flag marker, so differential backups on the initial server are not affected. (The backup flag marker is similar to the archive bit in Windows.) 19.4.3.5. Partial backupsPartial and differential partial backups are new in SQL Server 2005. A partial backup is very similar to a full database backup except it doesn't contain all filegroups. It includes all the data in the primary filegroup as well as every read-write filegroup. It also can include user-specified files. A differential partial is the same as a partial except it includes only the data in those extents that have changed since the last partial backup. As with other differential backups, the backup before the partial is called the base for the differential. 19.4.3.6. File and filegroupAn alternative to backing up your entire database is to back up files or filegroups individually. This has the advantage of possibly faster restores because if only one file fails, this file can be restored without having to do a complete database restore. Since a filegroup is simply an easy way to manage multiple files in a database, a filegroup backup is a way to more easily back up multiple files. 19.4.4. Backup/Restore of System DatabasesSystem databases contain all the information about the running server's configuration as well as many other specifics such as file and filegroup location information, storage parameters, and information about system-level configurations. Without these databases, restoring your data becomes impossible. Therefore, backup and recovery of these databases is very important. While there are six system databases previously mentioned (master, model, msdb, resource, tempdb, and distribution), the only one required for the instance of SQL Server to start is the master database. If this database becomes corrupt or damaged, there are two recovery options. The first option is for when the instance can still be started. In this case, you can restore master from your last full database backup. The second option is if the instance is so damaged that it can't even start at all. If this is the case, a complete rebuild of the master database might need to be performed. After successfully rebuilding, you would restore the master from the last full backup.
19.4.5. Viewing Information About the BackupIt is possible to view information about the history of backup operations. This information is located in the msdb database in the following tables: backupfile, backupfilegroup, backupmediafamily, backupmediaset, and backupset. There are three commands to view the backup history: restore filelistonly, restore headeronly, and restore labelonly. The following examples use a database called Inventory that has a backup created in a backup set located at E:\Backups\Inventory.bak. To view the file list in this example, run the following command within a Transact-SQL window (Figure 19-1), which should be valid for multiple versions of SQL Server: restore filelistonly from disk = 'E:\Backup\Inventory.bak' Figure 19-1. Listing the backup header informationThis command lists the backup header: restore headeronly from disk = 'E:\Backup\Inventory.bak' Of course, the same information is also available in the SQL Server Management Studio for 2005 or Enterprise Manager for 2000:
19.4.6. Verify BackupsIf you have time, it is always a good idea to verify the backup once it completes. Like most other operations, there are multiple ways to accomplish this. By far the easiest is to check the "Verify backup when finished" checkbox, as in Figure 19-2 for 2005 Management Studio and Figure 19-3 for 2000 Enterprise Manager. Figure 19-2. The "Verify backup" checkbox in 2005 Management StudioTake note of the Perform checksum option below the "Verify backup" checkbox in 2005. The Verify backup option verifies only that the backup can be restored. It provides no guarantee that the actual data on the backup is valid. If the "Perform checksum" option is also selected, it provides some indication of the reliability of the data. If you want to verify the backup using Transact-SQL, issue the following command for either 2000 or 2005: restore verifyonly from disk = 'F:\Backups\Inventory.bak' 19.4.7. Backup Expiration DateSetting the expiration date of the backup allows the backup set to be overwritten without explicitly specifying that it is acceptable to overwrite. This date can be set using command-line backup options or from the Management Studio in 2005 or the Enterprise Manager in 2000. To specify this in the Management Studio, connect to the instance where the database is located, and then follow these steps:
Figure 19-3. The Verify backup checkbox in 2000 Enterprise ManagerTo specify this in Enterprise Manager, connect to the instance where the database is located, and then follow these steps:
19.4.8. How to Back UpIn its simplest form, without taking into consideration backup strategies, recovery models, and other implementation details, the following steps perform a full backup of your database. The procedure is essentially the same between 2005 and 2000 with only a few of the options having different labels.
Figure 19-4. Back Up Database, General pageAt first, the destination is preselected, but a diligent database administrator alters the location to be on a different disk than the actual database files. In Figure 19-4, I selected E:\Backups\Inventory.bak as my backup location. (The E: drive is a different physical drive than the C: drive, where the database and logfiles reside.) Even though this is a quick backup, it would be negligent to not specify some of the options in the Options tab. At a minimum, select the checkbox to verify the backup, as previously shown in Figure 19-3. Of course, this performs only the most basic of backups. Ideally, careful consideration would be given to planning your backups. This includes full or differential backups, locations of these backups, how much storage is necessary, and other details. 19.4.8.1. Command-line backup with Transact-SQLThe same disk-based backup can be accomplished using Transact-SQL and the backup database command for 2000 or 2005. The following command does a basic backup without any additional options: backup database inventory to disk = 'E:\Backups\cmd_Inventory.bak' with name = 'Command Line Inventory Backup' go 19.4.9. Transaction Log BackupsIt is also important to ensure that the transaction logs are backed up on a regular basis. This can be done only with a full or bulk logged backup type. In order to perform a transaction log backup, follow these steps:
These steps are shown in Figure 19-5. They are almost the same for 2000 with Enterprise Manager, but the Backup type is a set of radio buttons instead of a drop-down list. Figure 19-5. Transaction log backupGo through the rest of the options on the page and customize as needed. Afterwards, select the Options page, and make changes as necessary to the Transaction log section, shown in Figure 19-6. If this is a normal backup, simply leave the default "Truncate the transaction log." Select the "Back up of the tail log" option only if you are doing a tail log backup of the database after a failure, are in preparation for a restore operation, or are in preparation to fail over to a secondary database. Figure 19-6. Transaction log optionsAs with most other operations, the backup of transaction logs can also be accomplished using Transact-SQL as in the following example. This example backs up the transaction log for the Inventory database to the previously created backup device Inventory_dev; this procedure is valid for 2005 and 2000: backup log Inventory to Inventory_dev 19.4.9.1. Log truncationLogs could theoretically grow until they fill the entire disk. To prevent this, logs are periodically truncated. Depending on the recovery model, logs are truncated at different times and in different ways. In the simple recovery model, in which log backups are not supported, log truncation is automatic, usually happening after a checkpoint. In the full and bulk-logged recovery models, the log is truncated when it is backed up. 19.4.10. Master Database BackupsIt is extremely important to back up the master database on a regular basis. This database holds all the configuration information for the running system as well as all the configuration information for all databases and other information such as logon accounts. Without this database, the rest of the system is useless!
19.4.11. Scheduling a BackupTo save time running manual backups, SQL Server allows jobs to be created that can automatically back up your database at a specified time. An automated job consists of two pieces: the backup job itself and a backup schedule. To create a new job, follow these steps for 2005 Management Studio:
This creates the backup job, but it is also necessary to create a schedule. That can be done from the initial job creation screen by selecting the Schedules page, or if you've closed it, by right-clicking on the backup job, and selecting Properties. Once you've clicked on the Schedules page, select New to open the window shown in Figure 19-7. From here, enter a Name, and set the type of backup frequency. Figure 19-7. Scheduling a backup jobIn 2000's Enterprise Manager, the easiest way to schedule a backup is to use the Maintenance Plan wizard:
This wizard walks you through the steps necessary for scheduling a backup. |