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:
- 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.
- 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.
- 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.
- 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.
- Change of recovery model from full or bulk logged to simple and then back to full or bulk logged.
- 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.
