Post reply

restore sql server system databases

  • July 21, 2010 at 12:27 am

    #94991

    Hi All,

    Why can't we restore the system databases among the database servers as we do it for Application databases.

    What are the internal reasons behind we cannot migrate/restore system databases directly on a new server.

    Thanks in Advance

  • July 21, 2010 at 1:23 am

    #1195991

    The main reason is that the system databases are in use. Consider the five standard system databases (I'm avoiding the distribution database used in replication).

    1. Master - This is always in use. If you want to restore this, see http://msdn.microsoft.com/en-us/library/ms175535.aspx - it involves starting SQL Server in single user mode.

    2. Model - This is the same as a user database

    3. Msdb - This is the same as a user database, but all users have to be out of it, including SQL Agent.

    4. TempDB - This cannot be restored - it is recreated whenever you restart SQL Server.

    5. Resource DB - This is read only and cannot be modified.

    You've mentioned having a new server. In this case, I would NOT recommend restoring the master database from a different server onto this server. Model and MSDB would be OK, but I would prefer to start fresh with a new MSDB database.

  • July 21, 2010 at 3:06 am

    #1196018

    Since we are restoring on a new server which is currently not in use and i have taken the sql server into single user mode then why cant we restore the sysdatabases?

    Can we know the reason behind why it is not recommended not to restore master(which avoids creation of logins using sql scripts)

    and msdb (which avoids creation of jobs and maintenance plans).

    Is that master contains its own configuration settings which differ on the new server of better hardware?

    Is that msdb contains log shipping information and mirroring information with respect to that server?

  • July 21, 2010 at 5:44 am

    #1196080

    Oracle_91 (7/21/2010)

    Since we are restoring on a new server which is currently not in use and i have taken the sql server into single user mode then why cant we restore the sysdatabases?

    isn't sysdatabases just a view of all teh attached databases on the server? so if you have a new install, if i restore or attach 4 databases to it, 4 new entries in the sysdatabases view will appear.

    Can we know the reason behind why it is not recommended not to restore master(which avoids creation of logins using sql scripts)

    and msdb (which avoids creation of jobs and maintenance plans).

    restoring from another server? much of the information in the system databases could/would be incorrect. the reference to the servername would be the first i can think of...also drive paths to the folders fo backups and restores...all the jobs in the msdb database refer to the server it existed on, so even if you restored the msdb database, i think the jobs would fail. better to script out the jobs and run the script on the new server i think. similar to logins....you want to script them out and run the script on the new server, rather than trying to restoe master to get the logins on the new server.

    Is that master contains its own configuration settings which differ on the new server of better hardware?

    Is that msdb contains log shipping information and mirroring information with respect to that server?

    yes all the replication settings, which are specific to the server would be in msdb...so a restore on the other server would not be pretty for the replicaiton settings, which reference a specific server as the publisher or subscriber...i think that that is anotehr item that needs to be scripted out and executed...good point!

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 21, 2010 at 10:48 am

    #1196302

    Makes sense. You mean to say, rather than restoring all the system databasess and modifying the ssystem tables to point to new server is a clumsy act and which is not a good practice. Right?

  • July 21, 2010 at 3:28 pm

    #1196530

    Oracle_91 (7/21/2010)

    Makes sense. You mean to say, rather than restoring all the system databasess and modifying the ssystem tables to point to new server is a clumsy act and which is not a good practice. Right?

    You are correct.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • July 22, 2010 at 12:50 am

    #1196648

    Thanks for all your kind suggestions.

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

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