Home » SQL Server » Differential backup of master database?

Differential backup of master database?


Master database is in simple recovery model & simple recovery model supports Full & Differential backup.

Is it possible to take the differential backup of master database?”. The answer is No.

Why not try taking the differential backup of master database instead. Open SSMS and the backup option looks like this.

Only FULL backup type is listed. So SQL Server does not allow any other type of backup operation to be performed on master database. Let us try out the script to take the differential backup of master database.

Here as well SQL Server states the same. You can only perform a full backup of the master database. It is confirmed that SQL Server is designed to allow only FULL backup of master database.

But why? For differential backups to be applied on a database, the database has to be in NORECOVERY (Recovering) state. But SQL Server does not allow master database to be restored without recovery. Master database has to be in Recovered status always.

Moral of the story is you can only take FULL backup of master database.

Advertisements

1 Comment

  1. I do not even know the way I finished up here, but I thought this submit was once great. I don’t recognize who you are but definitely you’re going to a well-known blogger for those who aren’t already. Cheers!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: