SQL Server 2005 – Backing up a Database
In this tutorial you will learn about Backing up a Database in SQL Server 2005 – Backup and Restore operations, Restoring System Databases, Media Reliability, Using Checksums during backup and Restore and Backup Media mirroring.
Backup and Restore operations.
A backup is a copy of the data that is maintained for the purpose of restoration at a later date if a requirement arises due system failure. Failures can occur due to media problems, user errors, hardware failures or natural disasters. Backup of data are also useful for copying data from one server to another, for setting up database mirroring, archiving and disaster recovery.
As is evident from the preceding paragraph a backup strategy contains a restore strategy as a natural sequel. The backup strategy defines the type and frequency of backups while a recovery strategy defines roles and responsibilities of the personnel who have to do the restoration within the time for recovery defined. It is advisable to document the backup and restore procedures in a run book.
It is also evident from the preceding paragraphs that backup and restore operations require a lot of planning. Planning involves taking into consideration the production goals of the organization, the nature of the databases, the constraints on the resources and so on. The DBA must be fully aware of the implications of data loss to the business and must plan to restore the database within the shortest possible time. This in turn involves the selection of a correct recovery model. The staffing requirements for the recovery procedure must also be considered and put in place to meet the stringent requirements of recovery.
A recovery model is a database property that controls the basic behavior of the backup and restores operations for a database. Recovery models determine how transactions are logged, which logs require backups and the kinds of restore operations available to the DBA. A new database inherits its recovery model from the model database. Recovery models simplify recovery planning, backup and recovery procedures, clarify tradeoff among system operational requirements and clarify tradeoffs among availability and recovery requirements.
SQL Server 2005 introduces changes to the BACKUP statement. It no longer supports the BACKUP LOG WITH NO_LOG and BACKUP LOG WITH TRUNCATE_ONLY options.
There are three type of recovery models in SQL Server. The simple recovery model logs most transactions minimally. It logs as much information as required for ensuring database consistency after a system crash. When old transactions are committed the log is truncated. This poses the potential danger of data loss in the event of disaster. This type of recovery model is considered inappropriate for production environments.
The full recovery model logs all transactions and retains all the log records until backup. The SQL Server 2005 Enterprise edition allows a database to be recovered up to the point of failure. Full database backups include log steps to reach a consistent state if transactions are in process when the backup is made.
Bulk logged recovery model performs a minimal log of bulk operations such as creation of indexes and bulk loads. This improves performance of bulk operations and is used as supplement to the full recovery model.
Mirrored backups helps DBAs create two or four identical copies of backup files in case one of the sets is damaged. Online Restores provides the ability to do an online restore to an offline filegroup while keeping the database online. Copy only backups provide the DBA with the ability to make a copy of the backup file in the middle of the backup sequence without disrupting the sequencing of other backup files. No changes have been made to the way differential backups are made. Transaction log backups have now the ability to make copy only backups. Partial backups are similar to differential backups except that it backs up all file groups other than those marked read only. Finally SQL Server 2005 also provides the DBA the ability to restore from earlier versions of the database.
Restore is a process that restores data from one or more backups and recovers the database when the last backup is restored. SQL Server Management Studio can also be used to restore databases and logs. The RESTORE statement contains a new option RESTRICTED_USER. This restricts access to the recovered database to members of the db_owner, dbcreator or sysadmin roles. It replaces the DBO_ONLY option of SQL Server 2000.
Point in Time recovery functionality has been added to SQL Server 2005 by allowing the STOPAT option to be used to restore the log to a specific point in time. Partial restore operations have also been enabled in this version. Errors in isolated parts of the database can be corrected using this option. A partial restore of the primary file group containing a specific part of the database can be executed to restore the contents to an original state in a point in time. Page level restore operations are useful in restoring individual pages when using the full or bulk logged recovery models. This greatly reduces the time duration that the database is offline. Piecemeal restore operations have also been introduced to allow the DBA restore file groups in stages, bringing the groups online as the restoration operation is completed. For full and bulk logged recovery models piecemeal restore is valid only for secondary file groups. In simple recovery model secondary file groups can only be restored if they were read only when the backup was executed and have remained so since. This restore is attempted after partial restore of the primary filegroup.
A new concept of Online restore has been introduced in SQL Server 2005. The database remains online while restoring individual files or pages to a database. This enhances uptime of tables in full and bulk logged recovery models. These can be of two types: Page online restore and file online restore.
Multiple RESTORE statements can be used to restore sequences that copy data, roll it forward and bring it online at the end of the roll back phase. Valid restore sequences can be derived from the catalog views, the msdb database and backup that contains the required information. Catalog views also provide information regarding the state of the database.
Restoring System Databases
The restoration of system databases require that the instance of the SQL Server must be running. To run the SQL server instance the master must be accessible. To restore the master from a current database backup or restore it from a full backup. If the master is damaged beyond restoration, it must be rebuilt and then the backup must be used to restore it.
The model, msdb or distribution database must be restored from a full backup.
Backup and Restore operations in SQL Server 2005 can be performed in two ways
1) Using multiple backup devices in parallel with enough throughput.
2) Using a combination of full, full differential and transaction log backups to minimize recovery time. The full differential backups are faster to create and reduce the amount of transaction log required to recover the database.
Differential backup operations involve copying data from the database files to the backup devices and copying the portion of the transaction log needed to roll forward the database to a consistent state with the backup devices. The process is similar to creating a data backup of the changed data. The database file is copied to the backup device and the files are sorted by disk device and a reader thread is assigned to each device. The reader threads reads the data from the database files and a writer thread is assigned to each backup device. Thereafter the writer thread writes to the backup device. Parallel read operations can be increased by spreading the database over more backup devices and parallel write operations can be increased using more backup devices. However, if optimal throughput is not available bottlenecks can occur. A similar procedure is followed for Transaction log backups.
Media Reliability
Backed up data could still be at risk due to errors occurring on the backup media. SQL Server 2005 provides functionalities to alert the DBA about such errors during the process of backup or restore process.
Using Checksums during backup and Restore
The first of these is the CHECKSUM option of the BACKUP statement. This calculates a checksum on each page of the data and verifies the checksum before writing the information to the backup media. This helps ensure that valid data is written on to the media. If an error is detected during checksum creation process, the backup will fail. However, this can be overridden using the CONTINUE_AFTER_ERROR option. The CHECKSUM option of the RESTORE statement can be used to verify the data before restoring it to the server.
The CONTINUE_AFTER_ERROR option is to be used with discretion. The nature of the error will determine its usage. If the checksum verification fails it is possible that the remainder of the data to be restored in valid and the process can continue. However, if an error occurs in a tape drive, it might prove impossible to continue with the restore operation. If errors do occur the database may be marked as SUSPECT at the end of the restore process and the media may be checked manually for errors that impact the restore operation.
Backup Media mirroring.
The potential of recoverability is increased through the use of backup mirroring. Using this method data can be backed up in two locations of the same media type. The precondition is that when this kind of backup is attempted, all the media must be accessible and in restore only one need to be accessible.
In this tutorial we have studied backup and restore operations including partial backups, online backups and how to reduce the probability of media failure. In the lesson that follows we shall look in greater depth into data availability and database mirroring.
