Master DB restore

Question

Post reply

Master DB restore

Janice R

SSC Journeyman

Points: 95
More actions
July 20, 2005 at 7:45 am

#164362

Hello,

we were doing some DR testing and restored the master DB from one system to another (a 2000 standard edition master DB over to a 2000 MSDE install) and now the MSDE instance will not start. In the startup, It can't find the Model and MSDB databases because now the paths are wrong..

starting up database 'model'.

2005-07-20 09:23:10.32 spid5 FCB:pen failed: Could not open device d:\data\MSSQL\data\model.mdf for virtual device number (VDN) 1.

Can this be fixed or do I have to reinstall the MSDE instance???

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

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

DCPeterson SSCoach Points: 15256 More actions · Answer 1

I have generally found that for DR purposes it isn't necessary to restore the master db to the target server, in fact it usually just causes problems. The only thing in the master db that is generally needed are the logins and those are easily scripted.

Unless you have a backup of the old master db file you will need to reinstall or rebuild the master db.

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/

Janice R SSC Journeyman Points: 95 More actions · Answer 2

Thanks. I did BACKUP my master db before I blew it away with the restore.. BUt I guess that doesn't do me any good and I should have stopped SQL Server and made a copy of the files.. oh well.. live and learn...

as far as why I did what I did.. I was just playing around with DR scenerios - I had read someone's DR plan and they did restore Master and then msdb. I know msdb probably contains local DTS packages and such.. and maybe local scheduled jobs too??? Can you restore msdb (with move) without restoring master first???

DCPeterson SSCoach Points: 15256 More actions · Answer 3

Yes, you can restore msdb without restoring master and that is exactly what I have done for DR purposes. msdb contains all the jobs, dts packages and backup history among other things.

And as long as you backed up your master db you can (and probably should) restore it rather than rebuilding. You can use another instance of sql server to restore the backup of your master, but restore it as some other name. Then you can move the data and log files into the location where they should be. Then restart sql server. This isn't fool proof, but from where you are it should be pretty safe to attempt.

You can check the HKWY_LOCAL_MACHINE\software\microsoft\microsoft sql server\InstanceName\MSSQLServer\Parameters\SQLArg0 key in the registry to verify the path to the master db data file.

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/

Janice R SSC Journeyman Points: 95 More actions · Answer 4

Janice R

SSC Journeyman

Points: 95

July 20, 2005 at 1:51 pm

#575914

I'll give it a shot!!! Thank you much!

Scott Coleman One Orange Chip Points: 27451 More actions · Answer 5

Another technique would be to restart SQL Server with trace flag 3608, which prevents recovery of any database other than master. This would give you a chance to fix the file locations in master..sysaltfiles, then restart the server without the trace flag. I think you could use sp_detach_db and sp_attach_db to avoid directly updating the system table. (I might feel comfortable updating system tables myself, but there's no way I would put that step in a written disaster recovery plan.)

But copying the master database from a different server sounds to me like disaster creation, not disaster recovery.

Scott Coleman One Orange Chip Points: 27451 More actions · Answer 6

I feel I should clarify that my post was about the general case of when the server won't start because it can't find a required system database (other than master). This is what you would do, for instance, it you were intentionally trying to move the model database to a different drive.

In your specific case, I like the previous suggestion to try to get a working copy of the original master db by restoring it (under a different name) on another working server.

Janice R SSC Journeyman Points: 95 More actions · Answer 7

I reinstalled sql so I can break it again : )

I can see now why it's probably not a bad Idea to have an offline copy of your system DB mdf and ldf files!!!

I will actually try the first suggestion.. restoring my orig master DB backup to another instance as another name and them using those mdf and ldf files to try and recover .. thank you so much for all your help and suggestions !!