November 13, 2002 at 7:03 pm
[SQL Server 2000 Enterprise Edition on Windows 2000 Advanced Server.]
This is a disaster that I recently had to recover from. Everything was recovered without any problems. I have one question though and I will put it at the end.
Disaster Recovery Scenario:
- All engines were properly shut down before disaster due to scheduled SAN outage.
- After SAN guys did their work and rebooted, we had a corrupt O.S. (C drive)
- SQL Server binaries all resided on the C drive.
- All system databases were on the C drive, except TempDB which was relocated for the PROD and Default Instance. All these system databases were available. ("good" system databases)
- All user databases were on other SAN logical volumes and were also available.
Recovery Procedure I used:
These below steps were taken because the O.S. was corrupt and not fixable:
1. Layed down an older Ghost Image that had no SQL Server binaries.
- did not have to apply any Windows Service Packs as it was contained in the Ghost Image.
2. Put the /PAE switch in the boot.ini file and rebooted. (Needed for the O.S. could see all 8GB RAM.)
3. Gave Local Administrator rights to the NT User doing the SQL Server 2000 installation.
3. Re-installed all SQL Server 2000 Instances(7 of them) to the SAME directory structure as before.(C drive)
- The reason for the same directory structure is because the "good" master had a specific registry location for its data, log and error file location (-d, -l, -e startup parameters).
- need the same install location in order to use the "good" master.
4. Applied all SQL Server 2000 service packs.
- there were no hot fixes to apply here.
5. Since the TempDB database was relocated for 5 of the 7 instances, we relocated them the the directory as they were before the disaster.
6. Stopped all engines.
7. Backed up all newly installed system databases.(Cut from install directory into a backup folder in the same directory).
8. Copied all "good" system databases into the proper directory structure.
9. Started up each engine.
10. All databases, system and user, started up clean. SQL Server went through its normal boot-up 'recovery' and was fine.
My question is, in Step 3, I re-installed all SQL Server 2000 Instances to the same directory structure as they were located in before the disaster occured. Is this a required step in order to use the "good" system databases? (Master database mainly!!!)
November 14, 2002 at 2:41 am
Yes, if you want to be able to use your existing master whether from the original files or from backup the directory structures must be the same.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 14, 2002 at 5:16 am
Actually, Yes and No. The master database can be whereever you want to put it during the istall, SQL finds it via the reigstry entry made during install. As for the other system databases, they do have to be in the original path as does the user database. The reason for this is that master has the paths stored in the sysdatabases table and this is how SQL Server finds the rest. Even thou sysdatabases also contains the path to master in it, the registry entry overrides. You can even move master after you have set the path. Open EM and select the server to change the path. RIght click it and choose proerties, then Start Up Parameters, there ou will find 3 entries,
-dD:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf-- the path to master db
-lD:\Program Files\Microsoft SQL Server\MSSQL\data\mastlog.ldf-- the path to master log
-eD:\Program Files\Microsoft SQL Server\MSSQL\LOG\SQLServerLog-- the path to the SQL Server log files.
They are found under the registry key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters
You can delete these and replace them with your own new path (keep in mind you have to keep the -d, -l or -e in the front).
Once you have made the change, stop the server, moe the master.mdf and mastlog.ldf to their new locations. If you move the log files they will create new ones themselves.
Now restart the server.
The other system databases can also be moved after you have finished the install. Go here to find out how http://support.microsoft.com/default.aspx?scid=kb;en-us;224071
November 14, 2002 at 6:24 am
You say that for the re-installation to put the master database anywhere I like and then I can use the "good" master database after.
If this it true, then I would stop the server, drop the "good" master in where the installed master is.
1. Will the engine start?
2. If so, then why move the master?
November 14, 2002 at 3:44 pm
Yes, has always worked for me.
The reason to move, is so the install cannot inadvertently overwrite it, once that happens it is gone. It is a safety thing I feel more comfortable with.
November 14, 2002 at 4:22 pm
ok.
Based on my disaster scenario, I have the "good" system databases and all the user databases available. These are copied to another drive.
So,
1. Re-install to any location.
2. Stop Server
3. Move the newly installed system databases to another location(just to preserve the new install db's)
4. Copy the "good" system databases into the same re-install path.
Now,
why would one use the StartUp Parameters and move the newly installed master just after the re-install? This master means nothing.
I would just remove it from the install path to preseve the install. Thats the only reason I can see.
Correct?
November 14, 2002 at 4:27 pm
Sorry I was referring to your question of does it have to be in the same path as before, I only added that you could move them afterwards if yo wanted to.
As for keeping the newly installed master, not sure how that got in the loop, maybe my mistake. There is no reason to keep it once the server is live. I would only save it in case something does turn out to be wrong with the old master, at least that way you will not need to reinstall again.
November 14, 2002 at 5:24 pm
ok now I understand what you were saying.
you were talking about moving the "good" master.
makes sense, but not a needed step for this recovery scenario.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply