Thursday, May 31, 2012

Differential Backups for System Databases

There was a question in www.sqlservercentral.com asking what the system database you can’t take differential backups.

My initial thought was it is tempdb as I couldn’t see a reason why you need take differential backups in tempdb. Also, msdb and model you might need to take the differential backups and in master database you should be able to take differential backups since it has user information.

But the answer is master. Confused smile

Then I thought of digging further into this.

1. Is this true?

image

Yes it is. If you go through the backup UI , you will only see Full backup option under master database while for all the other system databases you will see Full and Differential backup option.

2. Why?

I was wondering why is this since I can’t see a logic behind this. However, it is very simple. Smile with tongue out

What is the mechanism to restore differential backups.

  1. Restore the Full backup with NORECOVERY option on.
  2. Then Restore the differential backup.

So if you want restore differential backup of a master database, first you need to restore full back with NORECOVERY option. which you CANNOT do for master database. Since you can’t do that there is no point of taking a differential backups.

No comments:

Post a Comment