Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

can not find Model after Master restore Expand / Collapse
Author
Message
Posted Sunday, March 17, 2013 8:20 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, July 27, 2014 6:56 PM
Points: 94, Visits: 486
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


Post #1432071
Posted Monday, March 18, 2013 3:15 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, April 11, 2014 6:50 AM
Points: 517, Visits: 346
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)

Post #1432114
Posted Monday, March 18, 2013 3:30 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:05 AM
Points: 42,469, Visits: 35,540
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 2008, MVP
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

Post #1432117
Posted Monday, March 18, 2013 1:22 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, July 27, 2014 6:56 PM
Points: 94, Visits: 486
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


Post #1432322
Posted Monday, March 18, 2013 3:37 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 2:20 PM
Points: 604, Visits: 502
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.


.
Post #1432366
Posted Monday, March 18, 2013 4:21 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:05 AM
Points: 42,469, Visits: 35,540
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 2008, MVP
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

Post #1432385
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse