Database Administration
sql-server sql-server-2008-r2 restore system-databases
Updated Tue, 12 Jul 2022 06:16:14 GMT

Order to restore system and user databases


We are going to move an sql instance to a new physical server (SQL SERVER 2008 r2). The answer to this question explains how Agent jobs and ssis-packages will be moved by restoring the master and msdb-databases to the new server.

My question is if the order of restoring system vs user databases has anything to say? Is it OK to restore master and msdb before user databases, even though this will restore agent jobs and ssis-packages that point to user databases not yet restored/created?

Thanks for any help.

br Gert




Solution

my understanding is that the order is not important... as long as the agent is not running until all db's are restored that have jobs interacting with them.

Yes, there is a dependency, but that dependency only exists upon execution of the jobs.

I would be interested in feedback subsequent to the restore of the master db.

I assume also that you are moving an existing (SQL SERVER 2008 r2) instance...





Comments (4)

  • +0 – Hi. Thanks for your answer. Yes, it is from-to SQL SERVER 2008 R2. We will upgrade to 2014, but will do this after the restore. — Jan 07, 2015 at 12:04  
  • +1 – Hi again. Some feedback after our restore. We restored the system dbs first. What happened then, was that all the user dbs was listed, but without any real contents. Then we could restore over the "existing" user db. — Feb 03, 2015 at 10:04  
  • +0 – Thanks for the update. Did your user databases SIDs need to be corrected? Did the logins have to be recreated? So, post master restore, the databases were "there" but only showed up as "suspect" or something? — Feb 03, 2015 at 12:02  
  • +0 – We did not have to do anything with the SIDs, and all users and jobs were restored with the msdb, however the linked servers did not work, they needed to be recreated. I don't think the databases were suspect (can't really remember), just empty since the files were not there. One importent thing though: when restoring system-dbs from old server, make sure that the disk paths (i.e F:\ E:\ etc) also exists on the new server, or you will get some problems. — Feb 03, 2015 at 13:51