Solution of Database Backup Incremental vs. Differential vs. Tail-Log Backup

Incremental vs. Differential

Incremental backup contains only the changes since the last incremental backup.

A differential backup refers to a backup made to include the differences since the last full backup.

The difference between incremental and differential backups can be illustrated as follows

Incremental backups: 

The above assumes that backups are done daily. Otherwise, the “Changes since” entry must be modified to refer to the last backup (whether such last backup was full or incremental). It also assumes a weekly rotation.

Day	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
Backup Type	Full	Incremental	Incremental	Incremental	Incremental	Incremental	Incremental	Full
Effect	N/A	Changes since Sunday	Changes since Monday	Changes since Tuesday	Changes since Wednesday	Changes since Thursday	Changes since Friday	N/A

Differential backups:

It is important to remember the industry standard meaning of these two terms because, while the terms above are in very wide use, some writers have been known to reverse their meaning. One case, for example, is the one found at The Elder Geek website

Day	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
Backup Type	Full	Differential	Differential	Differential	Differential	Differential	Differential	Full
Effect	N/A	Changes since Sunday	Changes since Sunday	Changes since Sunday	Changes since Sunday	Changes since Sunday	Changes since Sunday	N/A

The log or differential backup restored

For SQL Server database to be used it should in online state. There are multiple states of SQL Server Database.

• ONLINE (Available – online for data)
• OFFLINE
• RESTORING
• RECOVERING
• RECOVERY PENDING
• SUSPECT
• EMERGENCY (Limited Availability)

1.     The common practice during the backup restore process is to specify the keyword RECOVERY when the database is restored.

2.     When RECOVERY keyword is specified, the SQL Server brings back the database online and will not accept any further log backups.

3.     If you want to restore more than one backup files, i.e. after restoring the full back up if you want to apply further differential or log backup you cannot do that when database is online and already active.

4.     There are more than one database backup files to be restored; one has to restore the database with NO RECOVERY keyword in the RESTORE operation.

--Example of restoring differential backup successfully after restoring full backup

RESTORE DATABASE MYSQLDB

FROM DISK = '\\networkfilesvr\SQL_DB\MYSQLDB_FullBackup.bak'

WITH NORECOVERY; -- There are more backups to restore

RESTORE DATABASE MYSQLDB

FROM DISK = '\\networkfilesvr\SQL_DB\MYSQLDB_DiffBackup.bak'

WITH RECOVERY; -- This is the final backup

Databases backup in full recovery mode are taken in three different kinds of database files.

a.     Full Database Backup

b.     Differential Database Backup

c.      Log Backup

Golden Rule:

1. After restoring full database backup,

2. Restore latest differential database backup and

3. ALL the transaction log backup after that to get database to current state.

·        It is very clear that there is no need to restore all the differential database backups when restoring databases.

·        Restore the latest Differential database backup.

·        Differential database backup is the backup of all the changes made in database from last full backup; it is cumulative itself.

·        All differential database backups contain all the data of previous differential database backups.

·        You just have to restore the latest differential database backup and

·        Right after that install all the transaction database backups to bring database to the current state.

If you do not want to have differential database backup and have all the transaction log backups, in that case, you will have to install all the transactional database backups, which will be very time consuming and is not recommended when disastrous situation is there and getting server back online is the priority.

In this way, differential database backups are very useful to save time as well as are very convenient to restore database. Instead of restoring many transaction database logs, which needs to be done very carefully without missing a single transaction in between, this is very convenient.

After restoring full database backup, restore latest differential database backup and the entire transaction log backup after that to get database to current state.

  

*From Left to right

*DIFF= Differential Database Backup   *LgBkup = Log Backup --ref: sqlauthority

·        (SLOWEST): Full Database Restore  >>>    LgBkup A_1 >>>     LgBkup B_1 to all remaining logs.

·        Full Database Restore >>>   DIFF 1  >>>    LgBkup B_1 to all remaining logs

·        (FASTEST): Full Database Restore >>> DIFF 2  >>> LgBkup C_1 to all remaining logs.

While doing RESTORE Operation if you restoring database files, always use NORECOVER option as that will keep database in state where more backup file are restored. This will also keep database offline also to prevent any changes, which can create integrity issues. Once all backup file is restored run RESTORE command with RECOVERY option to get database online and operational.

·        NORECOVER= more backup file are restored

·        RECOVERY= last backup.

Following is the sample Restore Sequence

RESTORE DATABASE <DATABASE> FROM full_database_backup WITH NORECOVERY;

RESTORE DATABASE <DATABASE> FROM differential_backup WITH NORECOVERY;

RESTORE LOG <DATABASE> FROM log_backup WITH NORECOVERY;

-- Repeat this till you restore last log backup

RESTORE DATABASE <DATABASE> WITH RECOVERY;

Finding Last Backup Time for All Database – Last Full, Differential and Log Backup –Optimized

SELECT d.name AS 'DATABASE_Name',
MAX(CASE WHEN bu.TYPE = 'D' THEN bu.LastBackupDate END) AS 'Full DB Backup Status',
MAX(CASE WHEN bu.TYPE = 'I' THEN bu.LastBackupDate END) AS 'Differential DB Backup Status',
MAX(CASE WHEN bu.TYPE = 'L' THEN bu.LastBackupDate END) AS 'Transaction DB Backup Status',
CASE d.recovery_model WHEN 1 THEN 'Full' WHEN 2 THEN 'Bulk Logged' WHEN 3 THEN 'Simple' END RecoveryModel
FROM MASTER.sys.databases d
LEFT OUTER JOIN (SELECT database_name, TYPE, MAX(backup_start_date) AS LastBackupDate
FROM msdb.dbo.backupset
GROUP BY database_name, TYPE) AS bu ON d.name = bu.database_name
GROUP BY d.Name, d.recovery_model




SQL Server – Creating and using Dump Devices for Backups

You need to restore the tail log backup using the RESTORE LOG command with the NORECOVERY option to bring the database up to the point in time that the failure occurred (2:30 p.m. on Tuesday):

RESTORE LOG Orders
  FROM DISK='d:\backups\Orders_Tail.trn'
  WITH NORECOVERY
GO

Finally, you can bring the database online using the RESTORE statement with the RECOVERY option:

RESTORE DATABASE Orders WITH RECOVERY
GO

 

Tail-Log Backups (SQL Server)

Ref:  microsoft.com

            This topic is relevant only for backup and restore of SQL Server databases that are using the full or bulk-logged recovery models.

1.     A tail-log backup captures any log records that have not yet been backed up (the tail of the log) to prevent work loss and to keep the log chain intact.

2.     Before you can recover a SQL Server database to its latest point in time, you must back up the tail of its transaction log. The tail-log backup will be the last backup of interest in the recovery plan for the database.

Scenarios That Require a Tail-Log Backup

            SQL Server database is using either the FULL or Bulk-Logged recovery model.

We recommend that you take a tail-log backup in the following scenarios:

1.     If the database is online and you plan to perform a restore operation on the database, begin by backing up the tail of the log. To avoid an error for an online database, you must use the … WITH NORECOVERY option of the BACKUP Transact-SQL statement.

2.     If a database is offline and fails to start and you need to restore the database, first back up the tail of the log. Because no transactions can occur at this time, using the WITH NORECOVERY is optional.

3.     If a database is damaged, try to take a tail-log backup by using the WITH CONTINUE_AFTER_ERROR option of the BACKUP statement. On a damaged database backing up the tail of the log can succeed only if the log files are undamaged, the database is in a state that supports tail-log backups, and the database does not contain any bulk-logged changes. If a tail-log backup cannot be created, any transactions committed after the latest log backup are lost.

 

The table summarizes the BACKUP NORECOVERY & CONTINUE_AFTER_ERROR options

BACKUP LOG option	Comments
NORECOVERY	Use NORECOVERY whenever you intend to continue with a restore operation on the database. NORECOVERY takes the database into the restoring state. This guarantees that the database does not change after the tail-log backup. The log is truncated unless the NO_TRUNCATE option or COPY_ONLY option is also specified. Important We recommend that you avoid using NO_TRUNCATE, except when the database is damaged.
CONTINUE_AFTER_ERROR	Use CONTINUE_AFTER_ERROR only if you are backing up the tail of a damaged database. Note When you use back up the tail of the log on a damaged database, some of the metadata ordinarily captured in log backups might be unavailable. For more information, see Tail-Log Backups That Have Incomplete Backup Metadata, later in this topic.

Tail-Log Backups That Have Incomplete Backup Metadata
Tail log backups capture the tail of the log even if the database is offline, damaged, or missing data files.
This might cause incomplete metadata from the restore information commands and msdb.
However, only the metadata is incomplete; the captured log is complete and usable.

RESTORE HEADERONLY

Returns a result set containing all the backup header information for all backup sets on a particular backup device.

Backup type:

1 = Database

2 = Transaction log

4 = File

5 = Differential database

6 = Differential file

7 = Partial

8 = Differential partial

Whether the backup set is compressed using software-based compression:

0 = No

1 = Yes

Returns the information in the header for the disk file

RESTORE HEADERONLY

FROM DISK = N'\\fsltcinasclsfs\SQL_DB\Increment_DB\Increment_FULL_DB.bak'

WITH NOUNLOAD;

 

Situations where a Tail-Log Backup is required

ref:sqlbak.com

If the database is online and your intention is to do a restore operation of the database, your first step should be to back up the tail of the log. If you do not want to risk having errors for an online database, you must use the WITH NORECOVERY option of the BACKUP T-SQL statement.

·        NORECOVERY is an option that backs up the tail of the log and leaves the database in RESTORING state. NORECOVERY is useful when failing over to a secondary database or when saving the tail log of the log before a RESTORE operation.

·        In order to perform a log backup that will skip log truncation and then take the database into the RESTORING state in an atomic state, use the NO_TRUNCATE and NORECOVERY options together.

BACKUP LOG myDatabse TO DISK = '\\local\mydatabase.bak'

WITH NORECOVERY, NO_TRUNCATE

  We recommend using NO_TRUNCATE only when your database is damaged!

           

In case the database is offline and unable to start but you need to restore the database, first you will have to back up the tail of the log. Due to the fact that no transactions can occur during this time, using the WITH NORECOVERY option is not mandatory.

If a database is damaged, you should try to make a tail-log backup by using the WITH CONTINUE_AFTER_ERROR option in the BACKUP statement. CHECKSUM is also an option which works well in this situation.

BACKUP DATABASE myDatabase TO DISK = 'Z:\myDatabase.bak'

WITH CHECKSUM, CONTINUE_AFTER_ERROR

·        On a damaged database, backing up the tail of the log can be successful only if the log files are not damaged, the database is in a state which supports tail-log backups and the database does not contain any bulk-logged changes.

·        In the case when a tail-log cannot be created, any transactions committed after the latest log backup will be lost.

Incomplete Backup Metadata Tail-Log Backups

The tail-log backups capture the tail of the transaction log even in the case in which the database is offline, missing data files or damaged. This might be the cause for incomplete metadata from the restore information commands and msdb. However, even in this situation, only the metadata is incomplete; the captured log is complete and fully usable.

In case a tail-log backup has incomplete metadata, in the backupset table, has_incomplete_metadata column’s value is set to 1.

If case in which in a tail-log backup you have metadata that is incomplete, the backupfilegroup table will be missing most of the information about filegroups at the time of the tail-log backup. In this case, most of the backupfilegroup table columns will be populated with NULL values.

Ref: Microsoft site & DBA site.