March 17, 2013 at 8:20 pm
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
March 18, 2013 at 3:15 am
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, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)
March 18, 2013 at 3:30 am
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
March 18, 2013 at 1:22 pm
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
March 18, 2013 at 3:37 pm
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.
.
March 18, 2013 at 4:21 pm
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply