March 1, 2010 at 8:23 am
I've got a maintenance plan in place on our test database server, to backup all databases and their transaction logs, on the weekend. For some reason the master database's transaction log has never been backed up. Why is that?
It is a SQL Server 2005 db with SP3 applied.
Kindest Regards, Rod Connect with me on LinkedIn.
March 1, 2010 at 8:43 am
March 1, 2010 at 9:00 am
Does master change so often that you need it in full recovery and log backups being taken? Same goes for model?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 1, 2010 at 11:01 am
GilaMonster (3/1/2010)
Does master change so often that you need it in full recovery and log backups being taken? Same goes for model?
I have no idea how often, nor by how much, either Master or Model databases change. Those are things which I assume happen as a consequence of something else not occurring. Put differently, unless we make changes to either database accidentally, we don't make any changes to them at all.
Kindest Regards, Rod Connect with me on LinkedIn.
March 1, 2010 at 11:26 am
The only type of backup permitted with the master database is full backup.
From BOL:
Note:
Only full database backups of master can be created.
March 1, 2010 at 11:36 am
Also from BOL:
The types of operations that cause master to be updated, and that require a backup to take place, include the following:
Creating or deleting a user database.
If a user database grows automatically to accommodate new data, master is not affected.
Adding or removing files and filegroups.
Adding logins or other operations that are related to login security.
Database security operations, such as adding a user to a database, do not affect master.
Changing server-wide or database configuration options.
Creating or removing logical backup devices.
Configuring the server for distributed queries and remote procedure calls (RPCs), such as adding linked servers or remote logins.
March 1, 2010 at 1:02 pm
Excellent information, Lynn, thank you! Well then I can see that there won't be a lot of activity on our Master database, as we don't do most of those activities on a frequent basis.
BTW, I've got the Master database set to Full for recovery model, and the .mdb is being backed up regularly.
Kindest Regards, Rod Connect with me on LinkedIn.
March 1, 2010 at 1:10 pm
Just take full backups weekly (or daily). Same with model. As Lynn pointed out, you can't take log backups of master and model should almost never change.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 1, 2010 at 2:45 pm
Even if you have changed the recovery mode of master to FULL, it will still be operating in simple mode. Master ignores recovery mode changes.
think about it, to restore a transaction log you first have to do a FULL restore and leave the db in norecovery or standby, how could SQL work with an unrecovered or read only master database?
---------------------------------------------------------------------
March 2, 2010 at 3:06 am
Even though master will always operate in Simple mode regardless of the recovery mode you have set, personally I would want the recovery mode to reflect what is happening. If you want to avoid other people getting confused it might be best to set the recovery mode for master back to simple.
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 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply