SQL Server Performance Forum – Threads Archive

When log shipping must one also ship master and ms

Greetings, I have implemented log shipping to create a standby database on a second server. As I start to hink about it I began to wonder about the need to keep the master and msdb databases in sync by log shipping as well. Is there any need to do this and if not why not? As I understand it these database contain informaiton about the logins, security and data dictionary. What happens if objects in the primary are added, deleted or modified? I expect these changes will be propagated to the standby but is there a need to also sync the master and msdb databases? If anyone can help me understand that I would greatly appreciate it. Thanks.

Hi, You cannot log ship system databases. Actually, I’ve never tried it, but it shouldn’t be possible. When log shipping, the secondary database remains offline, in standby (or norecovery) mode and it would not be possible to have SQL Server running whilst the master database is not online. In the case of the msdb database, if it was offline then the jobs that are supposed to run as part of log shipping would not be able to run (because the db is offline). Not only that, but the jobs on the primary server are completely different to the jobs on the secondary server so it wouldn’t make sense to log ship msdb in that respect either. As far as keeping the logins in sync you should manually copy logins that are associated with the users in the log shipped databases (perhaps by using a DTS package) from the primary server to the secondary. Basically though, other than logins, you shouldn’t need to worry about keeping any of the system dbs in sync. Hope that helps, Karl Grambow www.sqldbcontrol.com

.. in addition to Karl’s reference view KBAs
http://support.microsoft.com/default.aspx?scid=kb;en-us;314515
http://support.microsoft.com/default.aspx?scid=kb;en-us;321247&sd=tech Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Few years later, it is possible to log ship to a different database name and log shipping is now scriptable.
So to come back to the original question, would it be feasible and is it recommanded? Log shipping is not present via SSMS for system databases.
Could I log ship to a copy of master or msdb (masterLS and msdbLS) on the destination server via scripting or other method and would it be recommended or a potential cause for issues/disaster?

Welcome to the Forums!.
Kindly, this post is from 2006. Please open a new one.

Hi Antoine
As Luis suggested it would help to start a new thread, anyways to avoid the duplication here is the solution.
You can generate a script against a database to create the objects and how about the data, it is not possible to script out all the data in the master & msdb databases (how about resource db). Thats why they have been kept under SIMPLE recovery model and documentation insists using a backup/restore strategy to provide such a standby for your system databases.
Hope this helps.

Thank you both.
I followed your recommandations and opened a new post with a bit more juice here. http://sql-server-performance.com/Community/forums/p/32385/167111.aspx#167111
Satya, I’ll do some more reading however it you have a reference to the documentation you refer to, I would appreciate it.
Regards

In that case follow that thread as I have commented there..

]]>