Friday, 9 August 2013

10 Myths about Backups in SQL Server

There are various types of backups in SQL Server:
  1. Full backup : A full database backup provides a complete copy of the database and provides a single point-in-time to which the database can be restored.
  2. Transaction Log Backup: Transaction log backups are only possible in the FULL or BULK_LOGGED recovery models. A transaction log backup contains all the transaction log records generated since the last log backup and is used to allow the database to be recovered to a specific point in time.
  3. Differential Backup: A differential backup performs the same operations as a full backup, but only contains all the data that has changed or been added since the previous full backup.
  4. Copy Only Backup: A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. A copy-only log backup preserves the existing log archive point and, therefore, does not affect the sequencing of regular log backups.


Myth 1: A full backup only contains data file information.
The backup command contains all the data in the data files and some portion of the transaction log. The backup contains some portions of the transaction log so that database consistency can be maintained and roll forward/roll back operations can take place once the database is restored.

Myth 2: Transaction log and differential backups are possible for the master database in full recovery model.
We cannot take transaction log or differential backups of the master database. This has been configured because the master database cannot be in a restoring mode as this database is essential for your SQL Server to be online. Thus we can only take full and copy only backups for the master database. We can change the recovery model of master database from simple to bulk logged or full.

Myth 3: We cannot take transaction log and differential backup of model and msdb database.
We can perform all kind of backup and restore operation on model and msdb database. Recovery model of both the databases can be changed from simple to bulk logged or full. One thing to keep in mind while restoring the msdb database is that your SQL Server Agent Services should not be running otherwise it will not allow an exclusive access on the msdb database.

Myth 4: We can take backup of tempdb database and we can change the recovery model of the database. 
We cannot take backup of tempdb database nor we can change the recovery model of the database. The recovery model of tempdb will always be simple.

Myth 5: Transaction log backup cannot be restored on model and msdb database
Model and msdb databases can be restored will full and subsequent transaction log or differential backups.
Two things need to be taken into consideration while restoring model and msdb:
1.       Take a tail log backup of both the database
2.       SQL agent services should be in stopped mode, else exclusive lock on msdb database cannot be acquired.

Myth 6: Full and differential backup breaks the log chain.
A full backup doesn’t break a log chain. A log chain is only broken in two scenarios as the LSN mismatch cause the issue.
  1. Change of recovery model from full or bulk logged to simple and then back to full or bulk logged.
  2. Missing log backup

Myth 7: Differential backups are thought to be incremental backups
Differential backups are not incremental backups, rather are cumulative backups as they contain all the data that has been modified from the last full backups. Transaction log backups are incremental backup as it contains the records of the transaction that has taken place after the previous transaction log backups.

Myth 8: We cannot take full and transaction log backup concurrently.
Yes, concurrent full and log backups are possible from SQL server 2005 onwards, but the transaction log will be cleared only when the full backup has finished.

Myth 9: Table level backup is possible in SQL Server
No. Table level backup is not possible in SQL server, thus we can take only take full backup, file group backup, transaction log backup.

Myth 10: Mirrored database cannot be backed up.
We cannot backup a mirrored database. There is only one way to see the content of the mirrored database and that is by generating database snapshot.