KNOW THE TRANSACTION LOG –PART- 3

This is third article in the KNOW THE TRANSACTION LOG series. In Part 1 I explained about the Transaction Log File and its behaviour. In Part 2 I explained about the Recovery Models available in SQL Server 2005 which affects the behaviour of Transaction log file of the database. In this Part 3, I am going to explain about the various Backup options available in SQL Server 2005, because, Backups are the backbone of the Restore Recovery of course for a DBA too. :)

Backups in SQL Server 2005.

Two major categories of Backups are available in SQL Server. They are Data backup and Log Backup.

Data backup includes image of one or more data files and log record data. It has three types.

· Full Database Backup includes all data files in the Database which is complete set of data. This also have enough log records that allow to restore the data during restore recovery. This is called base backup. Every restore situation need at least one base, full backup. For small databases, performing a Full Backup takes small amount of time and the backup occupies small amount of disk spaces. As database becomes larger, the full backup takes more time to finish, so as the restore takes more time during recovery. As for as larger databases concern, take Full backup along with supported differential backups, transaction log backups to reduce backup and restore time and associated system overhead.

When restoring a database from a Full backup , SQL Server re-creates the database in one step. As Full database backups include transaction log records within it, after restoring is over, all uncommitted transactions during the time of full database backup taken, are rolled back. So the restored database matched the original database when it was backed up minus the uncommitted transactions.

· Differential Backup : Differential backup of a database backs up only modified data since a last base database backup. It is small in size comparative to Full Database backup, obviously, runs fast, saves backup time. The base for first Differential backup after the full backup, is last full backup and for subsequent differential backups the base is the previous differential backup until the next full data backup is performed. This base is called as differential base. For a Simple Recovery model database, there should be only one differential base and for Full Recovery Model, there may be multibase Differential bases are allowed, but it is difficult to administer. For a Read-Write and online databases, sys.database_files system catalog view returns various information including three column information about differential base. That columns are differential_base_lsn , differential_base_guid, differential_base_time. For a read-only databases sys.master_files catalog view should be use to get the information about the differential base.

Have a full database backup and subsequent frequently taken differential backup for a large mission critical databases to avoid data loss. As the differential backup process takes smaller time to finish, the restore from it also takes minimum time.

When restoring from Differential Backups , a full backup restore should be done first and then a most recent Differential backup is to be restored even though a multiple differential backups has been taken between Full Backup and most recent Differential backup. No Log Backups that were taken between full backup and Differential backup need to be restore. If any tail log backup that has been taken before the full backup is restored, then that should be restored after restoring the differential backup.

· Partial Backup includes primary file groups and read-write file groups. Excludes read-only file groups by default. It can back up specified read-only file groups while taking backup. This is new to SQL Server 2005. It is different from differential backup. It is designed to provide flexibility for databases having simple recovery model. A Partial Backup of a read-only databases only have the Primary file groups files. To create Partial Backup we have to use READ_WRITE_FILEGROUPS [<filegrouplist] option in T-SQL Statement. Partial Backups can not be done through SSMS. Maintenance Plans also do not support Partial Backups.

A Partial Backup can be base for the Differential Partial Backup. Differential Partial backups back up all the data extents that are modified after a base partial backup of same set of file groups are performed. This can be performed with the help of the following command.

BACKUP DATABASE database_name READ_WRITE_FILEGROUPS [ , <file_filegroup_list> ] TO <backup_device> WITH DIFFERENTIAL

· File Or File Group Backup includes the file or file groups specified. An Individual file of a database alone can be backed up with this type of backup. This backup is very useful for the failure situation like if only one file is damaged in the database, we can restore that particular file only instead of having full database restore. This can minimize the restore time very much. There are two types of File backups. File Backup and Differential File Backup. A File Backup of a database can be the base for the Differential File Backup. Performing Differential File backup will give you an error if you changed the read/write file to read-only file after taking last full file backup. So whenever you change a read/write file to read-only file or a read-only file to read write file then take a full file backup.

An advantage of having file backup is recovery from damaged files or a file located in damaged media is very faster. The only damaged files can be restored. The disadvantage of this is maintaining complete file backup set can be more time consuming and complexity of administrative task is increased.

A complete set of file or file group backup is equivalent to Full database backup. When performing file group backups for a full or bulk logged model database do perform transition log backups additionally.

· Transaction LOG Backup includes only log records. For a full or bulk-logged recovery model regular transaction log backup is required. If not taken the transaction log file grows continuously till the disk is full. LOG Backup can be performed with the following command.

BACKUP LOG <database name> To <device name>.

There is a special type of LOG Backup that is Tail-Log backup. This log backup is taken immediately after the database failure if the log disk is accessible. This can be done if you include WITH NORECOVERY option in BACKUP LOG Command. When you issue this option the database becomes Restoring State and becomes offline to guaranty no modification can be done after finishing the tail-log backup. After taking Tail-Log Backup you have to restore the database.

· COPY-ONLY Backup : This is a special situation backup. It does not affect the regular SQL Server Backups and Restore sequences. After taking the COPY-Only Backup the transaction logs are not truncated. As the name specified it does only copying either Full Database or Full Log. This can be performed when issuing WITH COPY_ONLY option in BACKUP Command.

Backup History

The information about the backup history are stored in the msdb database which are very useful to manage backups. The following system tables in the msdb system database store history information about backups.

1) Backupfile stores a row for each data and log file in the database including a column is_present that indicates whether that file was backed up as a part of the backup set.

2) Backupfilegroup stores a row for each filegroup in a database at a time of a backup but this table does not indicate whether the filegroup was backed up or not. This table is new to SQL Server 2005.

3) Backupset stores a row for each backup set when a new backup set is created for each backup event.

4) Backupmediaset stores one row for each media set to which backupsets are written.

5) Backupmediafamily stores one row for each media family or its part of mirrored media set and one row for each mirror in the set.

For more information about Backup History see Books Online.

In the next and final part, Part-4, I will explain about Restoring Database.

KNOW THE TRANSACTION LOG –PART 2

In my previous post

Technorati Tags: SQL Server,Recovery,Restore Recovery,Backup,Recovery Models

, I wrote about the Restart Recovery which is automatically done by SQL Server 2005 in the event of SQL Server startup. There is another type of recovery available that is, well known, commonly practiced and a manual process, RESTORE RECOVERY.

Restore Recovery is triggered manually by DBAs during the data loss events, to bring back the SQL Server database to a particular point of working state. The data is recovered from the BACKUP of the database taken and stored away in a media either tape or disk file.

Before looking further inside, let us discuss some basics of Backups.

What is the need of a Backup?

Backup is the backbone for the mission where critical data involved. Even though having high availability system configured with compatible RAID level of Disk Subsystems or fully redundant Storage Area Networks and for Servers that are clustered with failovers with Microsoft Cluster Services and SQL Server 2005 failover clusters, backups of mission-critical databases are so important for many reasons. Say suppose, a developer executed a DELETE FROM query forgetfully missed a WHERE clause in it against a production server, instead of Development Server where he supposed to execute! This is one simple example. A lot of such situations may arrive to test your Database Administrative abilities. You have to rely on your database Backup.

In a Restore Recovery, Backup is so important. But a behaviour of Restore Recovery is based on a property of the Database that is ‘Recovery’. There are three Recovery Models available in SQL Server. They are SIMPLE, FULL, and BULK-LOGGED. When you create a Database, the default value of this option is ‘FULL’. This can be changed with ‘Alter Database’ command with a SET RECOVERY option. For example Alter Database mydb SET RECOVERY SIMPLE.

Simple Recovery Model.

This model provides a very simplest form of backup. This model minimizes the administrative overheads to a DBA. When this RECOVERY option is set in a database, then its transaction log will not be included during the backup and it is not possible to take TRANSACTION LOG Backup. When you take backup, the SQL Server automatically truncates transaction log by dropping the inactive log records and free up the space used by them.

This model of recovery is advisable to

· The databases that are under development process.

· The databases that are mainly used for data ware houses.

· The databases that are used for read-only purposes.

There are no log backups involved in Simple Recovery model; the database can be restored to the end of the most recent backup. So the work done after the last full backup can be lost.

Simple recovery model has following restrictions.

1. Page restore can not be done.

2. File Restore and Piecemeal Restore are available only for read only databases.

3. Point-in-time restore is not available.

Full Recovery Model.

This is the default Recovery Model when you create a database in SQL Server 2005. This model provides a full protection to the data. Thus this is best option to prevent data loss. These recovery models full rely on transaction log backups. To avoid data loss you have to frequently take backup of transaction log along with data backups. If you have a transition log backup after a failure, then you can restore the data to the point of time when the failure occurs.

As all activities including Bulk Copy operations, SELECT INTO, and even Create Index, are logged into the Transaction Log file, and it keeps the log records even after taking the data backup, the Transaction log file may grow high in volume in disk size if you specify auto grow during creating the database. This is one disadvantage of this recovery model, but can be easily handled with DBA’s high attention. As the storage is growing high in this model, the restoration time will be relatively high. For each time a transaction log backup is performed, the inactive log records are truncated and the space used by them is freed up for future usages.

The following scenarios are highly suited to have a database with FULL Recovery Model.

1. If the database contains multiple filegroups or read-only file groups.

2. If having efficient DBAs who can perform point-in-time recovery, Individual page restorations.

3. For high cost tolerance scenarios to tolerate the disk cost due to highly growing transaction logs.

Bulk-logged Recovery model.

This model is very similar to the full recovery model excepts it won’t log the Bulk Copy Operations, SELECT INTO, CREATE INDEX, ALTER INDEX REBUILD, DROP INDEX, WRITETEXT and UPDATETEXT BLOB operations. That means these operations are minimally logged. Because these recovery model can not log these operations as they run very fast. But transaction log records are created for such operations that took place and the page extents which are affected by these operations are also recorded in that log records. Still Log Backup is required to free up the inactive transaction log records. The Bulk-Logged Recovery model does not support Point-In-Time Restoration. This model is very useful where frequent bulk copy operations take place, so that, they are minimally logged and the performance degradations due to bulk copy operations will not be there.

What is LOG Truncation?

If the log records are not eventually not deleted in a frequency of time, Transaction LOG file will grow in high volume (of course depends upon the file size mentioned CREATE DATABASE command) upto even completely full your disk drive. So, inactive transaction records should be deleted in frequency of time. Deleting all inactive Transaction records from Transaction Log file is called as LOG Truncation.

LOG truncates occurs automatically

· for a simple recovery model database after a checkpoint occurs

· for the FULL and Bulk-logged model after taking the Transaction Log backup, if a checkpoint occurs after the previous backup.

In the next part of this series, we will see the Backup Types and Restore Sequences.