January 12, 2010 at 9:53 am
Hello Experts
Can you let me know why the differential bkp of the 'master' db is not allowed? By default the master db is in "simple" recovery model. Even if we change that to 'full' recovery model, we can't take the 'differential' backup...Why so?
-- the below query will produce error.
BACKUP database master TO DISK = 'D:\master_full.bak'
with differential
Msg 3024, Level 16, State 0, Line 1
You can only perform a full backup of the master database. Use BACKUP DATABASE to back up the entire master database.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Thanks.
January 12, 2010 at 9:56 am
Simple answer, by design. If you find yourself in a position of having to restore the master database, I am guessing it needs to be operational upon completion of the restore.
January 12, 2010 at 10:08 am
Lynns guess is correct. You cannot have master database in a recovery state, it would make SQL inoperable and then you would not be able to recover it.....................hence only full backups are allowed.
Also, even if you set the recovery mode of master to full via the options, it is still in fact operating in simple mode.
Do yourself a favour, leave the settings of master db alone 🙂
---------------------------------------------------------------------
January 12, 2010 at 11:13 pm
The size of master database is less than 15 MB.
So, taking full backup should not be an issue.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply