Database Backup Myth

1.       backup operations cause blocking

No. Backup operations do not take locks on user objects. Backups do cause a really heavy read load on the I/O subsystem so it might *look* like the workload is being blocked, but it isn't really. It's just being slowed down. There's a special case where a backup that has to pick up bulk-logged extents will take a file lock which could block a checkpoint operation – but DML is never blocked.

2.       switching from the FULL recovery model to the BULK_LOGGED recovery model and back again breaks the log backup chain.

No. It just doesn't. Switching from either FULL or BULK_LOGGED to SIMPLE *does* break the log backup chain however.

3.       breaking the log backup chain requires a full backup to restart it

No. You can restart the log backup chain with either a full or differential backup – anything that bridges the LSN gap from the point at which the log backup chain was broken.

4.        Concurrent log backups are not possible while a full or differential backup is in progress.

No,  In SQL Server 2000, a concurrent log backup with either a full or diff backup (I’ll just say ‘full’ from now on but take it to mean ‘full or diff’) was not permitted. The reason is that a log backup would clear the inactive portion of the log once it’s been backed up, but a full backup may still need some of that log so it can’t be cleared

In SQL Server 2005, the restriction was lifted, but there’s a subtle twist. You can do concurrent log backups with full BUT the log is not cleared when the log backup ends. The clearing of the inactive portion of the log is delayed until the full backup completes. This could cause you to have disk space problems if your log generation rate is huge and you’re relying on very frequent backups to manage the log size.

5.       A full or differential backup clears the log.

No. A log backup includes all the log since the last log backup – nothing can change that – no matter whether that log was also backed up by a full or differential backup. In the FULL or BULK_LOGGED recovery models, the only thing that clears the log is a log backup.

6.       Using the BULK_LOGGED recovery model for minimally-logged operations reduces the size of the next transaction log backup

No. A minimally-logged operation is so-named because only the page allocations are logged. A log backup needs all the information necessary to reconstitute the transaction, so a log backup following a minimally-logged operation must backup the log plus all extents changed by the minimally-logged operation. This will result in the log backup being roughly the same size as if the operation was fully logged.

7.       Full and differential backups only contain the log generated while the backup was running

No. A full or differential backup contains enough log to be able to recover the database to a transactional consistent view of the database at the time the data-reading portion of the backup finished (or as far back as the oldest log record that transactional replication has not yet processed – to ensure that replication works properly after a restore).

8.       Backups always test existing page checksums.

No. It only does it when you use the WITH CHECKSUM option – which you should.

9.       Backups read data through the buffer pool.

No. The backup subsystem opens its own channels to the database files to avoid the performance hit of having to read everything into SQL Server's memory and back out to the backup device (and also effectively flushing the buffer pool in the process). If you ask for the page-checksum checking, it uses its own small portion of memory.

10.   Backups perform consistency checks (a la DBCC CHECKDB)

No.

11.   If the backup works, the restore will too

No. You must regularly validate your backups to give yourself a high level of confidence that they will work if a disaster occurs. 

12.   A mirrored backup will succeed if the mirror location becomes unavailable.

No. If any one of the paths to a mirrored backup fails, the entire mirrored backup operation fails.

13.   A tail-of-the-log backup is always possible.

No. A tail-of-the-log backup is one that backs up all the log generated since the last log backup, in an exceptional situation. If the data files are damaged, you can still do a tail-of-the-log backup EXCEPT if the un-backed-up log contains a minimally-logged operation. That would require reading data extents – which cannot be done if the data files are damaged. For this reason, the BULK_LOGGED recovery model should not be used on databases that have 24×7 user transactions.

14.   Backup a database snapshot possible.

No. It's not implemented

15.   Log backups will be the size of the log.

No. The log has to accommodate the space necessary to roll back active transactions, the amount of space returned by DBCC SQLPERF (LOGSPACE) on a busy system doesn't accurately reflect the amount of log records in the log. log backup is just all the log generated since the last log backup – not the whole log file usually.

16.   Backup a corrupt database not possible.

No. In most cases you can use the WITH CONTINUE_AFTER_ERROR option to back up the corrupt database.  If that fails (maybe because of a damaged boot page or file-header page), there are no other options apart from OS-level file backups.     

       

17.   Log backups always clear the log.

No. If there's no concurrent data backup running, a log backup will always *try* to clear the log, and only succeed in clearing the inactive portion of the log – the log that's only considered 'required' by SQL Server because it hasn't yet been backed up. If anything else is holding the log 'required', it cannot be cleared, even though it has been backed up. Subsequent log backups will check again and again until the time comes when that portion of the log can be cleared.

Also, if there is a concurrent data backup running, the log clearing will be delayed until the data backup finishes. See the blog post in myth 30-04 for more details.

18.  You can back up a mirror database.

No. A mirror database is not accessible except through a database snapshot. And you can't back up that either. But it has changed in New feature in Always On where backup is permitted for secondary database.

19.  Back up a single table is possible.

No. You can effectively back up single table if it happens to be wholely contained on a single filegroup, but there's no way to say BACKUP TABLE (except export/import method).

20.   My transaction is guaranteed to be contained in the backup if it committed before the backup operation completed.

No. The commit log record for the transaction has to have been written out before the data-reading portion of the backup finished.

21.    You should shrink the database before a backup to reduce the backup size.

No. Shrink just moves pages around so won't make any difference.

22.   You don't need to back up master, msdb, model.

No. You should always back up the system databases. Master contains all the security info, what databases exist – msdb contains all the SSIS packages, Agent jobs, backup history – model contains the configuration for new databases. Don't fall into the trap of only backing up user databases otherwise you'll be in a world of hurt if you have to do a bare-metal install.

23.   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. But log and diff backup possible for Model and MSDB database.

24.   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.

25.    The native SQL Server 2005 backups are in clear text.

True before SQL 2008 - With SQL Server 2008 is the introduction of natively encrypted database backups.

Prior to SQL Server 2008 a third party product was necessary to encrypt the database backups.

26.    We can stop SQL Server successful backup writing to the SQL Server Error Log.

A trace flag 3226 has been introduced that allows you to turn off logging of all successful backups in your SQL Server error logs. DBCC TRACEON (3226,-1) will turn it up globally.