none
How to restore system databases from one instance to another

    Question

  • Hi All!!

    Please can anyone tell me; can I restore system databases backup from one instance to another?

    Problem Scenario: I have three instances of SQL Server 2008 R2 and each has seprate locations of data files. I backup master,msdb,model on first instance and restore these on the same instance. These are working properly but when I restored master,msdb and model database on 2nd instance, I found that msdb, model and tempdb are pointing the datafiles of first instance.

        Then, I restore only master database on 3rd instance from the same backup which was taken on 1st instance and I got the path of msdb,model and tempdb changed. The master databases of these three instance have right path but other databases are using the data files of 1st instance. If I restore the msdb and model on second instance through 'with move' option ,it gives error. I am able to run the following command for restore:

    restore database msdb from disk='D:\Databases\SystemDatabasesBackup\SQLSERVER2008_12\MSDB1\msdb.bak'  with replace

    I Want: Please tell me how can I change the path of msdb, model databases. Will rebuild master the only solution?

    Wednesday, December 28, 2011 12:27 PM

Answers

  • In the case of a "disaster", you will want to restore the databases to a server with the same name, not a new name.  Otherwise all your application connections would also need to be changed.

     

    • Marked as answer by Vikas_s Friday, December 30, 2011 3:13 AM
    Thursday, December 29, 2011 4:49 PM
  • Vikas,

    You might look into a Clustering solution for DR.

    http://msdn.microsoft.com/en-us/library/ms189134.aspx

     

    • Marked as answer by Vikas_s Friday, December 30, 2011 3:13 AM
    Thursday, December 29, 2011 4:58 PM
  • The system databases should be restored to the same server and instance.

    Remember you can rename the server name but not the instance name.

    The MSDB stores the server/instance name under job properties .. restoring to a different server will not change these leftovers.

    Clustering is somethign that you want to consider for DR.. other options being DBM or Log ship.


    Thanks - Vijay Sirohi
    • Marked as answer by Vikas_s Friday, December 30, 2011 3:13 AM
    Thursday, December 29, 2011 7:17 PM

All replies

  • Why would you want to restore a system database from the "wrong" instance in the first place? I fail to see the use-case for this. Can you elaborate?
    Tibor Karaszi, SQL Server MVP | web | blog
    Wednesday, December 28, 2011 12:37 PM
  • I am trying to refresh database from one instance to another instance.
    Wednesday, December 28, 2011 1:57 PM
  • "Why would you want to restore a system database from the "wrong" instance in the first place? I fail to see the use-case for this. Can you elaborate? "


    Please tell me, what do you mean by "wrong" instanse?

    I took the system databases backup of first instance and I want to restore these backup on another instance. Both instances have different names. Is this possible to restore these bakups taken on one instance, restore on another instance.

    Enviornment: Window 7->SQLSERVER 2008 R2

     and both are named instance with different names.

    Wednesday, December 28, 2011 2:25 PM
  • When you restore system databases (master) from one to another then instance name will be same on both servers - If you want to change the instance on new server you can drop the server and add new server  but please test this on testing environments before tempting to do it on Production servers-

    --- First drop the server (instance)

    sp_dropserver [ @server = ] 'OLDInstance' 

    --- Add new server (instance)

    sp_addserver 'ACCOUNTS';

    Hope this helps you


    http://uk.linkedin.com/in/ramjaddu
    Wednesday, December 28, 2011 2:55 PM
  • Really thanks for immediate reply!!

    I can't change the server name.

    Please tell me, is there other option?

    Wednesday, December 28, 2011 4:07 PM
  • <<I am trying to refresh database from one instance to another instance.>>

    That you can do by just restoring a backup the database on the second instance, not trying to bring over all system databases. Is this for some fail over purpose? Or something else? If we know what it is, in the end, you want to accomplish, we might be able to suggest some good solution. As you have noticed, it isn't a good idea to just restore the system database from one instance onto a different instance, especially on the same machine, with all file name dependncies, dependencies on the instance names etc. Here's an article on moving a *database* from one instance to another, perhaps that can be helpful: http://www.karaszi.com/SQLServer/info_moving_database.asp


    Tibor Karaszi, SQL Server MVP | web | blog
    Wednesday, December 28, 2011 4:57 PM
  • Hi Vikas,

    I have to agree with Tibork here. Rather than explaining what you are doing (because it doesn't make a whole lot of sense at the moment), tell us what you are trying to accomplish as an end result. It sounds a bit like you are trying to invent a complex method to what might already be a simple solution.

    Wednesday, December 28, 2011 9:00 PM
  •  

    "That you can do by just restoring a backup the database on the second instance, not trying to bring over all system databases. Is this for some fail over purpose? Or something else? If we know what it is, in the end, you want to accomplish, we might be able to suggest some good solution. As you have noticed, it isn't a good idea to just restore the system database from one instance onto a different instance, especially on the same machine, with all file name dependncies, dependencies on the instance names etc. Here's an article on moving a *database* from one instance to another, perhaps that can be helpful: http://www.karaszi.com/SQLServer/info_moving_database.asp"

     

    sir, I am doing disaster Recovery. Want to retain the logins, Model customization and SQL Agent jobs and SSIS packages stored in the MSDB"

    Thursday, December 29, 2011 4:40 PM
  • In the case of a "disaster", you will want to restore the databases to a server with the same name, not a new name.  Otherwise all your application connections would also need to be changed.

     

    • Marked as answer by Vikas_s Friday, December 30, 2011 3:13 AM
    Thursday, December 29, 2011 4:49 PM
  • Vikas,

    You might look into a Clustering solution for DR.

    http://msdn.microsoft.com/en-us/library/ms189134.aspx

     

    • Marked as answer by Vikas_s Friday, December 30, 2011 3:13 AM
    Thursday, December 29, 2011 4:58 PM
  • The system databases should be restored to the same server and instance.

    Remember you can rename the server name but not the instance name.

    The MSDB stores the server/instance name under job properties .. restoring to a different server will not change these leftovers.

    Clustering is somethign that you want to consider for DR.. other options being DBM or Log ship.


    Thanks - Vijay Sirohi
    • Marked as answer by Vikas_s Friday, December 30, 2011 3:13 AM
    Thursday, December 29, 2011 7:17 PM
  • Thanks a lot....all of you!!

    You all are right, we can configure other appropriate techniques like clustering, logshiping,mirroring,etc

    But in case of different named instance; I found that we should not restore master database.

    To acheive benefit of backup/restore on different named instance, we should restore msdb and model databases so that we can avoid to configure jobs.

    Disadvantage will be: We have to create logins and connectivity of other applications.

    Thank you all again!

    Friday, December 30, 2011 3:25 AM