Knowledge is no fun, unless you share it!!! :)

Wednesday, 24 July 2013

* * * * * TYPES OF BACKUP IN SQL-SERVER 2008 * * * * *


Backup and Restore are very interesting concepts and one should be very much with the concept if you are dealing with production database. One never knows when a natural disaster or user error will surface and the first thing everybody wants is to get back on point in time when things were all fine. Well, in this article I have attempted to answer a few of the common questions related to Backup methodology.

1. Full Backups

2. Differential Backups

3. Transaction Log Backups

4.File and file group backup

5. Partial Backups

6. Copy-Only Backups.

1. Full Backups

A full backup captures the entire database, including part of the transaction log, so the database may be completely restored to a consistent state at the point in time when the full backup completed. Any uncommitted transactions that were open at the time the backup completed will be rolled back in the event of a restore. The full backup also serves as a base for subsequent differential and log backups. You can't take differential or log backups on a database if you have never taken a full backup. Typically, you will take a full backup once a day if the database size is within reason, and then supplement with transaction log backups throughout the day. If the database is too large to create daily backups in a timely fashion, you can take less frequent full backups, perhaps on a weekly basis, and then supplement with differential backups throughout the week. No matter what strategy you choose, it all begins with a full backup of the database.

SQL Server only backs up the data pages in the database that have currently been used, so the full backup will generally be smaller than the size of the database.

2. Differential Backups

A differential backup captures all changes that have occurred in the database since the last full backup. The differential backup uses a bitmap page that contains a bit for every extent to keep track of the changes. The bit is set to 1 in the bitmap page for each changed extent since the last full backup referred to as the differential base. Each subsequent differential backup following a full backup contains all changes made since the last full backup, not just the changes made since the last differential. This means that over time, the differential backup can become as large as the full backup itself. In order to benefit from the speed advantage and space savings of differential backups, you should make sure to schedule your full backups in short enough intervals to serve as a new differential base, so the differential backup remains significantly smaller than the full backup.

3. Transaction Log Backups

If you are using the full or the bulk-logged recovery model you must schedule regular transaction log backups. Routine transaction log backups not only provide the highest level of data protection, they also truncate the inactive portions of the log and enable you to reuse the log space for new transactions. If you never back up your transaction logs, the logs will never be truncated and will ultimately grow out of control. Beginning with SQL Server 2005, you can now make concurrent full database backups and transaction log backups. In previous versions, the transaction log backup would wait for the full backup to complete before proceeding. You cannot make transaction log backups using the simple recovery model because SQL Server automatically truncates the log on checkpoint.

Each transaction log backup only contains the new log records that were not backed up in the previous transaction log backup. A succession of uninterrupted transaction log backups forms a log chain that allows you to restore to a point in time within the log chain. SQL Server assigns each transaction log backup a log sequence number (LSN) that it uses to maintain the log chain. Once the log chain is broken for any reason, such as a missing backup file or data corruption, you cannot restore any further transactions in the chain until you take a full or differential database backup to serve as a new base for the chain.

4. File Backups

File backups allow you to create a backup that contains individual files or filegroups. File backups give you the flexibility to take backups of large databases based on usage patterns. For example, you may have a set of tables that is only updated once a month in one filegroup and frequently updated tables in another. You may also want to use file backups if you have multiple filegroups on separate disks. If a single disk fails, you will only need to restore the file backup for files contained on a single disk instead of the entire database.

Unless your database is in Simple mode, you also need to make sure you are backing up the transaction log when working with file backups. In order to completely restore a database using file backups, you must have the appropriate transaction log backups as well. All of the file backups plus the transaction log backups taken since the first file backup are equivalent to a full backup. You can also create a differential file backup to increase recovery time that will only contain the changed extents since the last full file backup.

5. Copy-Only Backups

You can create a copy-only backup to perform a full or transaction log backup; this is independent of the normal backup sequence that is maintained using standard backup operations. Copy-only backups were introduced in SQL Server 2005 to enable you to backup a database without interfering with the normal backup and restore routine. For example, you cannot use a copy-only full backup as a differential base because the differential backup ignores the fact that the copy-only backup was made, and the differential is based on the last full backup that was made without using the copy-only option. A transaction log copy-only backup allows you to create a backup of the transaction log without breaking the log sequence number or truncating the transaction log. As with any other transaction log backup, you cannot create a copy-only log backup of a database using the simple recovery model. 

6. Partial Backups

Partial backups were introduced in SQL Server 2005 to provide an alternative way to back up large databases that contain read-only filegroups. Partial backups are similar to full backups except that they are designed to back up only the primary filegroup, any read/write filegroups, and any read-only filegroups that are optionally specified. If you create a partial backup of a read-only database, only the primary filegroup will be included in the backup.

Just as with full backups, you can also create a differential partial backup that uses a partial backup as a differential base. The differential partial backup will then only contain the changed extents since the last partial backup. If your last partial backup included any optional read/write filegroups, you must also include them in the differential partial backup. You cannot create a differential partial backup that uses a full backup as a differential base.