SQL server Recovery Models and Backup types

In this post I am going to explain the importance of recovery models and backup types. 

Why I am writing this post ?

Because, when I was teaching about the topic to my PHP friend .He got doubts on bulk logged model and differential backups. I have given some examples and cleared his doubts. I am going to share this with you guys too.

I hope this will help for fresher's and DBA managers because they don't know about the recovery models and backups types. (RPO/RTO data’s at all). This will help for newbies especially an interview time.

Recovery models and backup types are important to plan the recovery point objective (RPO)and recovery time objective (RTO). We can’t differentiate both the recovery models and backup types. Simply it’s a mixture.

Let's see the discussion.

Manager:

We need minimum data loss and down time.

DBA:

Existing system has low disk space and the transaction log backups are failed often. We need more disk space to do this, Sir!

Manager:

I asked about this to management they will give soon until, you will do something with your DBA knowledge.

DBA:

Changing the recovery models full to simple and back to full and shrinking the log file daily.The DBA send an email to his manager. The shrinking is not good practices, Sir!

Manager:

???

(Story ended)


Let’s come to the point.

Recovery Models

SQL server has three types of recovery models.

1. Full

2. Bulk Logged

3. Simple

Full

•  In full recovery model all the database operations are fully logged. This means all the modifications have written fully to the log file.

• The full recovery model is always best one for production servers.

• In full recovery model we can take all kind of backups like full, differential, transaction Log and point in time (PTR) recovery too.

Bulk Logged

• The name itself you can understand the answer. Bulk operations have minimally logged in this mode.This means it has written only minimal roll backing information to the log file not whole (i.e not fully logged).The BCM page contains all the required information. See an example you can understand.

• Bulk logged recovery model is best model for server performance. Because all the bulk changes have been written minimally (Not fully) to the transaction log file.

• In bulk logged recovery model we can take all kind of backups like full, differential and transaction Log but, the drawback is point in time (PTR) recovery is not possible, when there is a bulk operations  have done to the transaction log file.

Keep in mind,in full and bulk logged recovery model the log files are grow big until the BACKUP LOG has done.

For more clarity. See an example:

Just assume 5,000 bulk operations have written in 8000 pages.

In full recovery model each 5,000 bulk operations have written to 8000 pages, because all the changes have written fully in the full recovery model.

But when we used bulk logged recovery model, it has written 1000 pages only ,Because SQL server has written the bulk changes to an extent wise not the page wise i.e. minimally logged operation (8 pages = 1 extent).It will helps to improve the overall server performance.

Now you know, Writing 8000 pages are good or 1000 pages.

Note: This bulk logged model is good only for the bulk operations.

Simple

• The name itself you can understand the answer. In simple recovery model is just simple this means SQL server will run the checkpoint every minute and truncate the transaction log file often.

• Most of the time the simple recovery model is good choose for non production servers.

• In simple recovery model the transaction log backup is not possible. We can take full and differential backups only.

 Backup Types

SQL server has number of backup types. Here I am going to explain the main three types.

1. Full

2. Differential

3. Transaction log

I have recommended to reading Paul S Randals's backup survey post.

 

http://www.sqlskills.com/BLOGS/PAUL/post/Importance-of-having-the-right-backups.aspx

Full backup

• A full backup backs up the full/whole database. That is backs up all the committed data.

• The full database backup has been done in all the recovery models.

Differential backup

• A differential backup only backs up the changed data/extents that were modified after the full database backup has done.The DCM has tracked all the changed extents.

• The differential backup has been done in all the recovery models.

For more clarity. See an example:

Assume we are scheduled every Sunday full backup @12 am and daily differential backup 10 PM.

Note: The data changes daily 500 MB.

Question:

How much data got back up on Thursday?

The student answers 500 MB.

As I already told, differential database backup "backs up all the data that has changed since the last full database backup".

The answer is,

Monday 500MB + Tuesday 500 MB + Wednesday 500MB + Thursday 500MB= 2000MB

Edit: I added these lines after i had replied the forum question SSC.

So the differential backup on Thursday has all the data. That is (Monday+Tuesday+Wednesday+Thursday). It helps to bring the database online as quickly as possible.

Transaction log backup

• A transaction log backup backs up the transaction log file. That is all the modifications/changes.

• The transaction log backup only possible in full and bulk logged recovery model.

• The transaction log backup is important to minimize data loss and log files size too.

It's  very important to keep the transaction log backup as much as safe,because the restoration needs the sequence of the transaction log backup if, you deleted/missed any one of the backup then you can't recover the whole data.

I have suggested running the log backups often. It will reduce the log file size and helps to minimize your data loss.

 Question:
How to restore the database after a disaster? (OR) What is the sequence to restore the database?


Answer:
Restore a full backup and followed by recent differential backup and all the log backup one by one in a sequence.

For more clarity. See an example:

Assume we are scheduled every Sunday full backup @12 am and daily differential backup 10 PM and an hourly log backup.

The database crashed on Wednesday at 1:30 PM. Then the restoration will be on Sunday full backup+ Wednesday differential backup (Most recent)+ log backup @11+@12+@1.

Believe me guys, Test your backups often and keep at least two copy of the full backup safely, Because the full backup is important to restore the all other backup. This means without a full backup you can't restore the differential and log backups.

sql server internal architecture

Folks,

             I am very glad to share my experience.I have two reasons to write this article here.

First one is I had an interview with one of the company, they asked about the SQL server internals.(On that time I struggled)

When I Googled I didn’t get an entire information in any of the website.

The thing is many of DBA's and Developers don't know the SQL server internals Allocation Map.

I try to write everything in this post using my simple English.Hope, someone gets benefit by reading this article.

Database Physical Structure & Internals:

Database:

A Database is a collection of data that is stored (or) organized.We can manage and access data easily.
When we create a new database, by default two files will be created physically.

We can create more than one log files and data files (NDF).

• Data file(MDF)
• Log file(LDF)

Data File:

All the data will be stored in a data file.Internally the data’s are stored in data pages.

A database file has unique FileID and PageID.

Log files do not have pages. They have series of log records. I will cover the Log file section in my next article.

Data Page:

Data page is a fundamental unit of data storage. Each data page has 8KB size (128 pages per MB).Each page has 96 byte header system information.

All the data rows are stored in data page. Expect BLOB (text, ntext, image, xml, varchar (max), nvarchar & varbinary)

Extents:

An extents are fundamental units in which space is allocated to table (or) an index. Extents are 8 contiguous pages 64KB size. (16 extents per MB).

Two Types of extents

• Uniform/Dedicated extents
• Mixed extents

Uniform Extents:

Uniform extents are owned by a single object. All the 8-pages can be used for owning object.

Mixed Extents:

When we create a new table (or) an index by default pages are allocated to mixed extents first.

When the data grow and its reach 8 pages then its switched to uniform extents.

How the Data Storage Size Internally For Page & Extents:

Storage Size	Page	Extents
Size per KB	8KB(1-Page)	64KB (8-Pages)
Size per MB	128 Pages	16 Extents

IAM: (Index Allocation Map)

IAM page tracks with which extents within the specified GAM interval belongs to a single entity (or) file.A Database file have multiple files some of the DB size more than 4 GB

IAM interval size is 64000 extents i.e. 4GB

GAM: (Global Allocation Map)

Data files split up into GAM intervals (Conceptually)

Bit -->1 Free

An extent is available for allocation.

Bit --> 0 Allocated

An extent is already allocated.

GAM interval size is 64000 extents i.e. 4GB

SGAM: (Shared Global Allocation Map)

SGAM bitmaps is exactly same as GAM.

Bit --> 1 mixed extent one (or) more free page available

An extent is mixed one (or) more unallocated page available.

Bit --> 0 Either Uniform/Mixed extent No unallocated pages

An extent is either uniform (or) mixed all are already allocated.

SGAM interval size is 64000 extents i.e. 4GB

PFS: (Page Free Space)

PFS has tracks with which pages have free space.

A PFS page is the first page after the file header page in a data file (with page number 1).
Next comes a GAM (with page number 2) followed by an SGAM (page 3).

PFS interval size is 8088 pages i.e. 64MB

PFS does not have bitmap only have byte map.

DCM: (Differential Changed Map)

DCM tracks extents that have changed since last BACKUP DATABASE statement.

Bit --> 1 extent modified

Extent has been modified since the last BACKUP DATABASE.

Bit --> 0 extent is not modified

Extent has not been modified since the last BACKUP DATABASE.

BCM: (Bulk Changed Map)

BCM tracks extents that have been modified by bulk operations since last BACKUP LOG statement.

Bit --> 1 extent modified

Extent has been modified by bulk logged operation after the last BACKUP LOG statement.

Bit --> 0 extent is not modified

Extent has not been modified by bulk logged operations .Although the BCM pages appear by default in all databases.

How the Data Stored/Allocated Internally for the entity:

(Allocation Maps)

Type of storage (Bit Type)	GAM	SGAM	DCM	BCM
Bit 1	Extent Free	Mixed Extent at least one free pages available	Extent modified	Extent modified
Bit 0	Extent Allocated	Either Uniform (or) Mixed No free space	Extent not modified	Extent not modified

Referred links :

http://msdn.microsoft.com/en-us/library/ms190969.aspx

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/24/645803.aspx

http://www.sqlskills.com/blogs/paul/post/Inside-The-Storage-Engine-GAM-SGAM-PFS-and-other-allocation-maps.aspx