Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

10 Myths about Backups in SQL Server

By Shashank Srivastava,

Why SQL DB backups required? If you need to restore data in case of any disaster then you need database backup to restore the data back to normal or you will have to build the database from the scratch. SQL DB backups are the best friends of DBA’s. If there are no SQL DB backups and disaster happens then DBA’s are in trouble and it’s time to start writing a resume.

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.

Here I am not going to tell you how different types of backup works in SQL Server rather I am going to tell you about the various myths that prevail in SQL Server 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.

If you see the below backup output, you can see that the full backup has also considered some portion of mastlog file, which is the transaction log file of the master database.

Processed 456 pages for database 'master', file 'master' on file 1.

Processed 3 pages for database 'master', file 'mastlog' on file 1.

BACKUP DATABASE successfully processed 459 pages in 0.441 seconds (8.131 MB/sec).

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.

Demo 1: Changing the recovery model and performing all backup operation on Master DB.

Each highlighted line of code in the image is run separately.

Change the recovery model to full.

                          

Full backup successful for master database

Log Backup unsuccessful for master database

Error received in case of failure:

Msg 4212, Level 16, State 1, Line 1

Cannot back up the log of the master database. Use BACKUP DATABASE instead.

Msg 3013, Level 16, State 1, Line 1

BACKUP LOG is terminating abnormally.

Differential Backup unsuccessful for master database

Error received in case of failure:

Msg 3024, Level 16, State 0, Line 1

You can only perform a full backup of the master database. Use BACKUP DATABASE to back up the entire master database.

Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

Copy Only Full backup successful for master database

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.

Demo 2: Changing the recovery model and performing all backup operation on model and msdb database.

For model database : 

Change recovery model to full

Full backup successful for model database

Log Backup successful for model database

Differential Backup successful for model database

Copy Only Full backup successful for model database

For msdb database :

Change recovery msdb to full

Full backup successful for msdb database

Log Backup successful for msdb database

Differential Backup successful for model database

Copy Only Full backup successful for 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.

Demo 3: Changing the recovery model and performing all backup operation on tempdb database.

Change of recovery model is not possible in tempdb

Recovery model of tempdb will always be simple

Full backup unsuccessful for tempdb database

Error:

Msg 3147, Level 16, State 3, Line 1

Backup and restore operations are not allowed on database tempdb.

Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

Transaction log backup unsuccessful for tempdb database

Differential backup unsuccessful for tempdb database

Copy only backup unsuccessful for tempdb database

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.

Demo 4 :- Restoration of MODEL database

Restoration of full backup with no recovery for model database

Restoration of transaction log backup with recovery for model database

DEMO 5 :- Restoration of MSDB Database

SQL agent preventing exclusive access on msdb database

Restoration of full backup with no recovery for msdb database

Restoration of transaction log backup with recovery for msdb database

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.

Total article views: 5380 | Views in the last 30 days: 6
 
Related Articles
FORUM

Can we take a transaction log backup in simple recovery model?

Can we take a transaction log backup in simple recovery model?

ARTICLE

Working with SQL Server Recovery Models

The recovery models of SQL Server define how different backup and restore tasks will be performed on...

FORUM

Recovery Model and Transactions

Mainly about the simple recovery model

FORUM

recovery model

recovery model

BLOG

Recovery Models

In SQL Server we have three basic recovery models: full, bulk-logged, and simple. By default we find...

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones