Sql server Blog Forum Helping SQL server DBAs and Developers


SQL server Disaster Recovery plan

SQL server Disaster Recovery Options

This month’s TSQL Tuesday party is being hosted by Allen Kinsel (Blog | Twitter) and the topic this month is disaster recovery. It’s a DBA Topic/Job.

The disaster recovery is really “It depends”.

What is disaster recovery?

Disaster recovery is called DR. Hmm...

Disaster recovery is the best option for the business to minimize their data loss and downtime. The SQL server has a number of native options. But, as I already told everything is depends upon your recovery time objective RTO and recovery point objective RPO.

That is how much data can you afford to lose? How long can you wait? How much money do you ready to spend?

If you have answers for these questions then, you can choose your best DR plan.

Who knows what will happen tomorrow?

I have seen most of the company don’t have at least simple DR setup, Like keep the backup in safe DR site. The reason is they didn’t face any disaster yet.

Truncating and deleting the 'critical tables' also consider a big disaster. So guys keep the backup, backup and backup the database. Before going to set-up your DR plan asks the questions to DR Guru's via online (Like twitter, SSC and MSDN) and also read the DR Guru Paul Randal's white papers it will help.

I have recommended to watch the brentozar‘s DR video. Its amazing video and it covers the basics DR plan.

Seriously I have no idea about Geo cluster. Thanks to @sqlsoldier and @SQLpoolboy  for their twitter reply about the Geo cluster method.

For understandable I will classify the disaster recovery (DR) into three types. I am not going in depth just I will cover the basics of high availability HA and disaster recovery DR.

1. Data center disaster

2. Server(Host)/Drive (Except shared drive) disaster

3. Database/Drive disaster


Backup restore is common method its simplest (less expensive) and good option for disaster recovery but it’s depends upon your backup strategy and recovery model. The backup restores will work for all the above three DR methods. The DBA job is keeping the data as much as safe and application availability (Zero down time). That is test and keep your database backups safely try to automate the backup test and move them automatically to the DR site.

The backup restore does not good choose for big databases (TB) in case of any disaster, because the restoration takes more time to bring the database online. However you can speed up the backup restoration using the following SQL native features.


Clustering is a combination of one or more servers it will automatically allow one physical server to take over the tasks of another physical server that has failed. Its not a real disaster recovery solution because if the shared drive unavailable we cannot bring the database to online.

Clustering is best option it provides a minimum downtime (like 5 minutes) and data loss in case any data center (Geo) or server failure.

  • Clustering needs extra hardware/server and it's more expensive.

Database mirroring

Database mirroring introduced in 2005 onwards. Database Mirroring maintain an exact copy of a database on a different server. It has automatic fail over option and mainly helps to increase the database availability too.

  • Database mirroring only works FULL recovery model.
  • This needs two instances.
  • Mirror database always in restoring state.

For more details read Robert L. Davis's Pro SQL Server 2008 Mirroring book.

Log shipping

Log shipping is the process of automating the full database backup and transaction log on a production server and then automatically restores them on to the secondary (standby) server.

  • Log shipping will work either Full or Bulk logged recovery model.
  • You can also configure log shipping in the single SQL instance.
  • The Stand by database can be either restoring or read only (standby).
  • The manual fail over is required to bring the database online.
  • Some data can be lost (15 minutes).

Peer-to-Peer Transactional Replication

Peer-to-peer transactional replication is designed for applications that might read or might modify the data in any database that participates in replication. Additionally, if any servers that host the databases are unavailable, you can modify the application to route traffic to the remaining servers. The remaining servers contain same copies of the data.

Note: Database mirroring, logs shipping and replication also treated as DR if it's located in different data center. We can use both the HA and DR combination to set up the disaster recovery.

Again everything is "It Depends". Read Paul Randal's white papers before going to configure the DR.


Muthukkumaran Kaliyamoorthy

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

More Posts


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 recovery 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 Freshers and DBA managers.

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

Let's see the discussion.


We need minimum data loss and downtime.


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


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


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 best practices, Sir!




Let’s come to the point.

Recovery Models

SQL server has three types of recovery models.


1. Full

2. Bulk Logged

3. Simple


  • In full recovery model all the database operations are fully logged like all redo information. This means all the modifications have written fully to the log file.
  • The full recovery model is always the best one for production servers.
  • In the full recovery model we can take all kinds of backups like full, differential, transaction Log and point in time recovery (PTR) too.

Full recovery mode, the transaction log file will grow like anything(big), until we do a log backup. In other words, The log file will not be reusable/clear until the log backup taken.

Bulk Logged

  • The name itself you can understand the answer. The bulk operations (bulk operations) are minimally logged in this mode. This means it does not have sufficient information in the log to replay the transaction. The BCM page contains all the required information. See an example you can understand.
  • Bulk logged recovery model is the 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 kinds of backups like full, differential and transaction Log but, the drawback is the point in time recovery (PTR) is not possible, when there is a bulk operations  have done with the transaction log file.

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

For more clarity. See an example: (This is not a concept just an example)

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

In full recovery model each 5,000 bulk operations have written in 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 help to improve the overall server performance.

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

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



  • The name itself you can understand the answer all operations are fully logged except bulk operation, Since bulk are minimally logged. Simple recovery model is just simple this means SQL server will run the checkpoint every time and truncate the transaction log file and marked the space for reuse . Mostly the log file will not grow larger.
  • Most of the time the simple recovery model is a good choice for non production servers. Because the log file will not grow larger. Also we would not take log backups. (If you’re planning to take the log backup then don’t put in simple)
  • In the simple recovery model the transaction log backup is not possible. We can take full and differential backups only.

Now you could know the answer for the following questions.

What is the use of Full, Bulk logged and Simple recovery model.

Which one is best for your case?


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 recommend to read Paul S Randals's backup survey post.



Full backup

  • A full backup backs up the full/whole database. That backs up all the 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.


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 file 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/missing any one of the backups then you can't recover the whole data.

It's good to take a log backup often. It will reduce the log file size and helps to minimize your data loss.

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

Restore a recent full backup and followed by a recent differential backup and all the log backup one by one, that has done after a recent differential backup.

For more clarity. See an example:

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

The database crashed on Wednesday at 11PM. Then the restoration will be on Sunday full backup (@12AM)+ Wednesday differential backup (Most recent @8PM) + log backup @9+@10+@11 PM.

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

Note: The example has given is just for more understanding. Take a look at MSDN for more information.



Muthukkumaran Kaliyamoorthy

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

More Posts