May 15, 2007 at 6:46 pm
Hello
My master database is in FULL mode and the msdb is in SIMPLE mode. My understanding is that, it should be possible to backup the log of a database in FULL mode. Both the master and msdb databases log backups are exitting with the message
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server '123SQL' as 'domain\sqladmin' (trusted)
Starting maintenance plan 'LogBkp_MasterMsdb' on 5/15/2007 5:20:00 PM
Backup can not be performed on database 'master'. This sub task is ignored.
Backup can not be performed on database 'msdb'. This sub task is ignored.
Deleting old text reports... 0 file(s) deleted.
End of maintenance plan 'LogBkp_MasterMsdb' on 5/15/2007 5:20:00 PM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
I am new to SQL Server ; What would happen in case of a crash. What if the master db backup is of (say) 8PM but the other databases got backed up at both 8PM and 10PM; Can i use the restore the master as of 8PM and hope to use the other db@10PM.
Would appreciate any feedback.
Thanks
SQL Server Newbee
May 16, 2007 at 2:35 am
The master database can only be backed up using a complete backup and as such is usually in SIMPLE mode. As the msdb is in SIMPLE mode no logs are created and cannot be backed up.
The only time you need to backup the master is if you have made changes to the server i.e. created a database, changed config values etc.
You would need to restore the master if it became corrupt not necessarily because of a server crash.
-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy:
May 16, 2007 at 3:46 am
master should never be put in anything other than SIMPLE recovery mode. I thought SQL always put master into SIMPLE mode whenever it started, but I may be wrong. There is code in SQL Server that will prevent a log backup of master from happening, which will result in the master transaction log growing indefinitely.
In SQL 2000, msdb is always put in SIMPLE mode whenever SQL Agent starts. You can change this after Agent is running, and then do a full backup of msdb to give a recovery start-point for subsequent transaction log backups. In SQL 2005, SQL Agent does not change the mode of msdb, so you can put it into FULL and it will stay like that.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply