September 10, 2010 at 2:55 am
Hi,
I am DR testing one of our mssql servers and I am running into problems with recovering the master database. I have recovered the server (Windows 2003) using 'bare-metal' restores. When I come to recover the master database (equivalent to using the 2000 "rebuildm") using the following command, it is failing because the SQL installation is at SP2 level, and I am using the base DVD to install. This process is failing because of the release levels - it won't let me use a 2005 DVD when the installation is 2005 SP2...
start /wait z:\servers\setup.exe /qn INSTANCE=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SKUUPGRADE=1 SAPWD=xxxxxxxxxx
I have recoverd several SQL servers using this method but they have all been at the base release of SQL 2005.
Anyone have any ideas how to get round this problem?
September 10, 2010 at 10:09 am
Assuming the installation of SQL SP2 you managed to recover via your bare metal restore isn't working, have you thought of installing the base copy of SQL as a fresh install, then upgrading that to SP2 to get yourself back to the previous patch level, and then finally restoring your data files.
There may be a neater way to do it, but I'm afraid I can't think of one, and after having a look everyone talking online about how to do it seems to reference the same command you list unfortunately.
September 13, 2010 at 3:09 am
Hi Keith,
Thanks for your reply.
Yes, I have thought of doing another reinstall of SQL 2005 and SP2. I'm trying to go the "bare-metal" route as this way I don't have to worry about the setup of the SQL installation - i.e. what is actually installed, such as Reporting Services etc, the location of the system .mdfs & .ldfs, authentication mode, etc. etc.
As mentioned, I have no problem with servers where the SQL installation has no service packs. It appears that the "SKUUPGRADE=1" makes no difference when SP2 is in place.
At the ent of the day, I am trying to create recovery procedures that should a disaster occur, anyone can recover the server even though they may not have any SQL knowledge.
Anyway, thanks for your input. Back to the drawing board...
September 13, 2010 at 4:29 am
rob take a look at this thread on the same issue:
RE: Restoring Master database different versions of same service pack
in that thread, i stated that there is nothing of critical value in the master database itself, except for the logons; it's easy to restore the old master as a user database and then script out the logins from there, but i would never bother to replace the core master on your newly recovered, freshly installed and known to be working DR machine;
you just needlessly wade through error after error of database not ready and all the other issues, which are easier to resolve simply by attaching or restoring each of the user databases to your clean, working machine.
backups are stored in a standard format, that mostly respect base version, and not the specific version of the server;
after the databases are installed and working, you could then start upgrading service packs and cumulitive updates as needed .
Lowell
September 13, 2010 at 7:01 am
Hi Lowell,
Thanks for your input to my problem.
The problem I have is not restoring the master database, but rebuilding it. As I am doing a bare-metal recovery, everything has been restored to the server except for the .mdfs and .ldfs, for both system and user databases. In the good old days of SQL2000, at this stage I would load the installation CD and run the rebuildm command. This installs the default databases over which I would restore the master db etc. As you are no doubt aware, rebuildm is no longer available. I have to recreate the master database from DVD at this stage as it doesn't exist. If there were no service packs installed, I would have no problems doing this using the setup command, but as I have SP2, the installation DVD is at a lower level, hence the problem I'm having.
September 13, 2010 at 7:21 am
In that case it sounds to me like your best option would still be to re-install SQL RTM and then patch it to the previous level. Since the installation is mainly in place you can already determine before hand which components were installed, eg Server, Reporting Services etc, to ensure you install the same ones as before. I can't see how using the equivalent of rebuildm would make a difference really, since it would still just give you a master db with the defaults in place (after all it has no way to know what settings you had before). Once you have restored your old master db over the top of it you would then recover all the other server settings which are stored within master.
September 13, 2010 at 7:32 am
rob.shaw (9/13/2010)
Hi Lowell,Thanks for your input to my problem.
The problem I have is not restoring the master database, but rebuilding it. As I am doing a bare-metal recovery, everything has been restored to the server except for the .mdfs and .ldfs, for both system and user databases. In the good old days of SQL2000, at this stage I would load the installation CD and run the rebuildm command. This installs the default databases over which I would restore the master db etc. As you are no doubt aware, rebuildm is no longer available. I have to recreate the master database from DVD at this stage as it doesn't exist. If there were no service packs installed, I would have no problems doing this using the setup command, but as I have SP2, the installation DVD is at a lower level, hence the problem I'm having.
i guess that is my point. if you've just created an instance of SQL from the original install DVD, there is absolutely no reason to rebuild master, or restore it from a previous source.
it's up. it's working. I understand the knee jerk reaction of "i have a backup of master, lets restore it", but there is no need to do so.
you start your Disaster Recovery with a brand new machine, that has a vanilla, default, master that is at RTM service pack.
at that point, you can completely skip any attempts to restore master from anywhere else...simply start attaching databases or restoring. user databases are not dependant on the service packs, with some rare exceptions(2005 SP2 added a new datatype).
you can have your DR server back up and running at RTM, with the databases attached, and THEN start upgrading to service packs at your convenience; i think it is a waste of time and ytour resources to try to try and restore the SP2 binaries so that you can then restore an SP2 databases, instead of having scripted out the only items you really need; the logon info.
I admit you could simply install the latest service pack and cumulative release right after the original DVD install, and then start attaching user databases, but getting the databases up was the priority i was thinking of.
Lowell
September 13, 2010 at 8:09 am
Hi Lowell,
Thanks for your speedy response once again.
I'm probably not making myself clear here. The fact that a "bare-metal" restore has been done means that all the SQL binaries have been restored. The only bits that are missing are the .mdfs and .ldfs. As a consequence, SQL will not start (there are no system databases) so it is almost like having a corrupt master database. If this can be rebuilt then great, it places SQL in a state where the master db can be restored, if it can't then it looks like an install of SQL.
Our standard recovery procedure for Windows servers is "bare-metal". That way I don't have to worry about what applications/software is installed - it all gets recovered as part of the restore. It's been the same with SQL up to this point. SP2 has got me beat I guess.
September 13, 2010 at 8:47 am
rob.shaw (9/13/2010)
Hi Lowell,Thanks for your speedy response once again.
I'm probably not making myself clear here. The fact that a "bare-metal" restore has been done means that all the SQL binaries have been restored. The only bits that are missing are the .mdfs and .ldfs. As a consequence, SQL will not start (there are no system databases) so it is almost like having a corrupt master database. If this can be rebuilt then great, it places SQL in a state where the master db can be restored, if it can't then it looks like an install of SQL.
Our standard recovery procedure for Windows servers is "bare-metal". That way I don't have to worry about what applications/software is installed - it all gets recovered as part of the restore. It's been the same with SQL up to this point. SP2 has got me beat I guess.
i definitely misunderstood; for me i was thinking "bare metal" was a fresh, clean machine with a new operating system just installed, not a restored image; now i see the dilemma. lemme think on it a minute or two.
Lowell
September 13, 2010 at 9:12 am
If you are doing a bare metal restore with some software, where are the MDF/LDF files? The bare metal restores I've seen (and they rarely work, in my experience), include all files.
I'm not sure what you can do here without them, other than reinstall and get the master back from an SP2 level.
September 13, 2010 at 9:29 am
Hi,
At the time of the backup, all MDFs and LDFs are locked so they are backed up using either native SQL commands or a backup agent. As already mentioned, following the bare-metal portion of the recovery, everything is back except the MDFs and LDFs and as a consequence, SQL will not start. The next step (which works with 2000 and 2005 rtm) is to rebuild the master database from CD/DVD. This creates the system databases as though they have just been installed. At this stage I can start SQL in single-user mode, restore the master database, start again in multi-user mode and restore the rest of the databases. I have done this time after time without any problems, but never with SP2.
Thanks for your help.
September 13, 2010 at 9:37 am
I take you do have a backup of your system dbs.
I would suggest you restore them from your backups on any available SQL2005 instance as a user database so you get the .mdf and .ldf files for the master (or any other system dbs). Then copy those files onto your DR server to the location where it should be and then you should be able to start the SQL service. You can find the file location needs to be on the DR server from the startup parameters via Configuration Manager. Hope this helps.
September 14, 2010 at 8:16 am
Hi John,
Thanks for your help with this problem.
I'm sure your solution would work, but what I am trying to achieve is a simplistic way of recovering a SQL server in the event of a disaster. I have already tested and documented many servers using bare-metal recovery and the only one that has got me beat is this with SP2. What I'm struggling to understand is that in effect, what I am doing is almost replicating the loss of the master database, be it either through corruption or loss of the mdf and ldf, and this should be simple to recover from. Yes, there are instructions in bol etc, but they fail with SP2.
I don't really want to go down the route of reinstalling SQL, or installing another instance and restoring to this, or using another server to restore the DBs to. At the end of the day, all I'm trying to do is write instructions so that anyone (who probably don't know SQL at all) can follow and recover this server.
September 14, 2010 at 8:45 am
What do you mean by "bare-metal recovery" ?
September 14, 2010 at 9:01 am
"bare-metal" recovery is our standard way of recovering Intel servers to differing hardware. It also allows us to recover any application (except SQL2005 SP2 it seems!) without having to reload or install it. We run a scheduled "NTbackup" of the system state to a disk image which gets backed up to tape via our backup solution - Data Protector - along with all the other files, drives, etc. As regards SQL, this either gets backed up natively as .BAK files or via a Data Protector agent.
To recover the server, it first has to have the same OS and SP level installed and the same disk config. The server is brought up into safe mode, the C drive & system state are restored, drivers are applied, profiles are restored and finally the rest of the drives. At this stage SQL will not be available as none of the databases, including the system ones, are there. So, the next step (usually) is to rebuild the system databases from CD/DVD using rebuildm (2000) or setup.exe (2005). Once they are back, SQL is put in single user mode so that the master database can be restored. Following that, the rest of the system databases and user databases are restored. We then end up with a recovered server!
As mentioned in some of my posts, I have done this time and time again without any problems, but SP2 has got me stumped.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply