recovery model for System Databases

Question

recovery model for System Databases

repent_kog_is_near

SSCertifiable

Points: 7278
More actions
March 24, 2010 at 12:35 pm

#156819

What are the recommended recovery models for the sys db's (MSDB, Master, model )databases? I guess simple of tempdb?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Carl Federl One Orange Chip Points: 25384 More actions · Answer 1

Master and Tempdb are always in simple recovery mode and you cannot change the recovery mode.

Model can be any recovery mode where new databases will be in the same mode. Databases restored from backup or attached, will have their original recovery mode.

MSDB can be any recovery mode but in the case of recovery, point in time and log recovery cannot be performed. I recommend using full recovery for MSDB and performing log backups on a regular basis (hourly?).

SQL = Scarcely Qualifies as a Language

repent_kog_is_near SSCertifiable Points: 7278 More actions · Answer 2

compared to MSDB, how important is Model? do you have a recommendation for model?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Model is the "model" that will be used by SQL Server to create any new databases.

Thus it's importance is "it depends" on your environment. I would place MSDB above model.

If you want your future databases to be created with the Full Recovery model by default, then the Model db should be set that way.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Nitin Gupta - SQL DBA Ten Centuries Points: 1058 More actions · Answer 4

First of all its totally depends on your environment but as per standard practice System Databases (Master, Model and msdb) should in Full recovery mode.

As all of that databases recording the changes whether its master, modfel or msdb and helpful in case of emergency or point in time recovery of system database.

Thanks & Regards,

Nitin Gupta
SQl Server (7.0,2000,2005) - Sr. DBA
HCL Technologies, India

Carl Federl One Orange Chip Points: 25384 More actions · Answer 5

A Clarification regarding the master database; although the recovery mode can be set to FULL, transaction log backups cannot be performed and transaction log restores cannot be performed.

Here is the error message for "backup log master ..."

Msg 4212, Level 16, State 1, Line 1

Cannot back up the log of the master database. Use BACKUP DATABASE instead.

Msg 3013, Level 16, State 1, Line 1

BACKUP LOG is terminating abnormally.

Reproduce the error SQL:alter database master set recovery full;

go

select databasepropertyex('master', 'Recovery')

;

backup database master to disk = 'G:\SQL2008\MSSQL10.MSSQLSERVER\MSSQL\Backup\master_db_201003250000.bak';

go

backup log master to disk = 'G:\SQL2008\MSSQL10.MSSQLSERVER\MSSQL\Backup\master_tr_201003250001.bak';

go

alter database master set recovery simple;

go

SQL = Scarcely Qualifies as a Language

DBA in Unit 7 SSCarpal Tunnel Points: 4059 More actions · Answer 6

so there is no way to restore master to a point in time? only be able to to restore to the last good backup?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

DBA in Unit 7 (3/25/2010)
so there is no way to restore master to a point in time? only be able to to restore to the last good backup?

There should be no need to perform transaction log backups against the master. This database should not be under heavy transactional use.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events