Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


can not find Model after Master restore


can not find Model after Master restore

Author
Message
UncleBoris
UncleBoris
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 697
I was doing some testing to ensure I had all the version build files required to restore the Master database if the entire server crashed and it had to be restored on another server.

I managed to track down the hotfix that gave me the correct version and applied that to the new named instance I had created on my machine for testing [ABC\SQL2008].

I got a master backup from a prod server and managed to restore the Master OK.

The SQL Service then would not start with the error

I have had the issue trying to restore Master before on a new system where the build was different. I built a named instance and this error happened. After tracking

2013-03-18 15:03:39.53 spid7s Error: 15466, Severity: 16, State: 1.
2013-03-18 15:03:39.53 spid7s An error occurred during decryption.
2013-03-18 15:03:39.55 spid8s Starting up database 'model'.
2013-03-18 15:03:39.55 spid7s Server name is 'ABC\SQL2008'. This is an informational message only. No user action is required.
2013-03-18 15:03:39.55 spid7s Informational: No full-text supported languages found.
2013-03-18 15:03:39.56 spid8s Error: 17204, Severity: 16, State: 1.
2013-03-18 15:03:39.56 spid8s FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\model.mdf for file number 1. OS error: 2(failed to retrieve text for this error. Reason: 15100).



This location in the error is the location for the production server Model database.
There is also an error that it can't create the tempdb (which is in the D:\ of the prod server that does not exist on my machine).

Does anybody have any pointers on how to fix this?

If a Master is installed on another server does the instance type (in this case default) and file paths have to be exactly the same as the server the Master came from?
and do the user databases have to exist on start up (at the Master DB paths) or can these be added later on)


thanks
Vegard Hagen
Vegard Hagen
Mr or Mrs. 500
Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)

Group: General Forum Members
Points: 519 Visits: 368
Well, to be blunt: You're doing it wrong!

If you want to be able to rebuild your server from scratch, you need a backup of this server's master database, not one from a different server.

The exact contents of master is unique per instance and yes, it contains information (including file paths) on all other databases on your instance. Hence, if your instance is somehow screwed up, but the file system is still intact, you can often get away with restoring only master and your SQL Server will automatically sort out everything else on first startup.



Vegard Hagen
Norwegian DBA, blogger and generally a nice guy who believes the world is big enough for all of us.

@vegard_hagen on Twitter
Blog: Vegard's corner (No actual SQL stuff here - haven't found my niche yet. Maybe some day...)

"It is better to light a candle than to curse the darkness."
(Chinese proverb)


GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47359 Visits: 44392
Don't move master databases from one server to another, asking for trouble.

As for how you fix the missing model problem:
http://www.simple-talk.com/sql/backup-and-recovery/the-sql-server-instance-that-will-not-start/


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


UncleBoris
UncleBoris
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 697
Thanks for the replies.

So if a DB server totally dies and it is not recommended that the Master is restored on another machine then what would be the normal recovery procedure.

Install SQL on new machine
Apply any updates
Restore User databases from backups
Restore MSDB from old server from backup (to get jobs etc)???

but what about logins and any user objects in the Master database from the corrupt server? How would these be retrieved, or should these all be pre-scripted as part of the DR plan and these used in case of a disaster..thanks
Bill (DBAOnTheGo)
Bill (DBAOnTheGo)
SSChasing Mays
SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)

Group: General Forum Members
Points: 639 Visits: 585
If you're trying to load this on test from production to test... I'd either say build a like environment to get a proper test or I would clean install SQL and restore only.

Yes I would keep all jobs saved in a source control of some sort and I would take backups occasionally of a scripted set of logins. These should be in change management as well.

In DR mode, I would install and upgrade SQL to what ever patch I was at, then restore... Given that my new system was built the same as the last one.


Side note on a corrupt master:

As backup, and please correct me if it's wrong, I install sql 2005, 2005 ent, 2008, 2008 ent, so on so forth... saved the system databases with the file path pointing to C:\Temp\ as my install path.

I know this sounds really wrong, stick with me.

I only have these sets incase master corrupts and I need to recover master when it will not load far enough to do so. then I place these in the correct location, start it up and restore the master from backup. (all things now point to the right place and should load) If you only save the system databases without any patches at all, they will script upgrade to the correct version.

.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47359 Visits: 44392
UncleBoris (3/18/2013)
Thanks for the replies.

So if a DB server totally dies and it is not recommended that the Master is restored on another machine then what would be the normal recovery procedure.


Recreate machine, you'd keep the same name due to all the apps having connection strings, same version, same patch, same drives, same paths
Restore the latest backup of master.

What I said was don't restore master from another machine, so don't restore the master DB from a machine named Domain1.Machine1 to an instance on Domain2.Machine2


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search