Working with Backups in SQL Server

When working with SQL Backups there are in general two types of backup: Full, Differential and Transaction-Log (to be explained later in this post). Additionally, it is important to know that the   recovery model (Database Options) plays also an important role has an impact to backup strategy.
In this post  I’m not going to explain all these in detail, but just to give a short practical overview.
If the database is setup for Simple recovery model (Fig. 1), you can make following backups: Full and Differential. If the database is setup for Full Recovery, there additionally is possibility to make Transaction Log backup (See Fig. 5).

Recovery Model Comparison:

Simple

No transaction log backups
Saves transaction Log space
Changes since the most recent backup are unprotected
Can NOT recover to a specific point in time

Full

Requires transaction log backups
Can recover to a specific point in time
Takes a lot of space for transaction log
If the tail of the log is damaged, changes since the most recent log backup must be redone

Bulk Logged

Requires transaction log backups
Enables fast performance for bulk-operations.
Can NOT recover to a specific point in time

Backup Comparison

Full backup
A full backup is backup which contains all the data in database (or filegroups or files), and enough of transaction log to make total recovering possible.
To make this backup right mouse click on Backup and select Full as shown in Fig. 1.

To recover from FullBackup simply choose the FullBackup and device.

Differential Backup

A differential backup contains only the data that has changed since the differential base. Differential backups can speed up the process of making frequent backups to decrease the risk of data loss. If they are taken soon after the Full-backup are smaller and faster to create than the base of a full backup. To make this backup do the same as shown in Fig. 1, but choose Differential instead of Full.

Fig 2

To recover from Fifferential Backup you must first recover from FullBackup with “RESTORE WITH NO RECOVERY” as shown in the Figure 2. This will restore full backup. and the database (in fig 5 TestDb) will remain in recovering mode. That means it is not usable and it is awaiting to be fully recovered.
If the previous restore from FullBackup or even Differential Backup was not don with optoin NO RECOVERY, following error will appear:

“The log or differential backup cannot be restored because no files are ready to rollforward.”

Now, select Task | Restore | From File or Database and select Differential Backup to be restores. Usually you will use restore from database. However sometimes you might want to keep your backups in separated files. If you do that be sure that you know in which order backups have been stored in certain files!

Fig 3.

Transaction Log Backup

Under the full recovery model or bulk-logged recovery model, regular transaction log backups (or log backups) are required. Each log backup covers the part of the transaction log that was active when the backup was created, and it includes all log records that were not backed up in a previous log backup. An uninterrupted sequence of log backups contains the complete log chain of the database, which is said to be unbroken.

To limit the number of required log backups, it is recommended to routinely back up your data. For example, you might schedule a weekly full database backup and daily differential database backups.

Fig. 4 shows how to do transaction log backup. Select backup Type “Transaction Log”.

Fig 4.

Restoring of Transaction Log can only be done if previously Full Backup and all differential backups (if there are some) have been recovered in NO RECOVERY mode (See fig 3.).
Figure 5 shows how to perform transaction log backup.

Fig 5.

While restoring the backup be sure that you have all backups (especially if shared across different files) and that you know the exact order of backups. One example is in fig 6.

Fig 6.
Last but not least, while restoring the last backup, be sure that you restore it with option WITH RECOVERY (default one) shown in fig. 2.
Related post: Truncation is not Shrinking.