June 4, 2008 at 12:51 pm
Everything I read about restoring the master database requires the instance to be started in single user mode then restore the database.
What if there is no master database. Assume the data drive crashed. I have a new clean datadrive. How would I get the instance up in order to restore the master database?
Thanks
June 4, 2008 at 1:17 pm
You install SQL Server, this create a master database. Then you overwrite this one with your backup.
June 4, 2008 at 1:17 pm
If all you have is a backup of the master database, you can recover the original Master database from the installation media. There is a switch to recover a master database using the command line install. From there, you could then restore your master database backup.
June 5, 2008 at 6:43 am
I am at a loss about how to do disaster recovery. I have followed everyone's suggestions but nothing has worked. I consider this to be a very serious matter since I feel like the scenario is probable.
Here is what I tried and the results:
ASSUMPTION: Data drive fails on a clustered instance. There is no master DB other than a full back.
TEST 1: Rebuild Master db using SETUP program.
Text taken directly from BOL:
To rebuild system databases for a clustered instance of SQL Server 2005 from the command prompt
1.Insert the SQL Server 2005 installation media into the disk drive.
2.Use the following syntax:
start /wait setup.exe /qn VS=
Important: Always use strong passwords.
3.For the default instance, specify "MSSQLSERVER" for .
4.Important The resource database (mssqlsystemresource) is restored from SQL Server 2005 distribution media. When you rebuild the resource database from distribution media, all Service Packs and QFE updates are lost, and therefore must be reapplied. Before you proceed, see the section below on REBUILDDATABASE for more information.
5.The /qn switch suppresses all Setup dialog boxes and error messages. If the /qn switch is specified, all Setup messages, including error messages, are written to Setup log files. For more information about log files, see How to: View SQL Server 2005 Setup Log Files.
6.The /qb switch allows display of basic Setup dialog boxes. Error messages are also displayed.
I replaced all the parameters and ran the command
ERROR: SQL Server setup has determined that the following property was not specified: “GROUP” …….
I added the GROUP switch (why was this left out of BOL?) and re-ran command
ERROR: There was an unexpected failure during the setup wizard. Review setup logs....
The setup logs ref to the group. I verify everything matches exactly. There are no errors in the event log. I try again, same error.
I give up on this test
Test 2: Copy Master db files from another server
Run the NET START command to start the instance
FAILED. Tries to use resource db from drive of old instance
Has anyone successsfully recovered an instance on a cluster when the master db is missing?
Michael Earl (6/4/2008)
If all you have is a backup of the master database, you can recover the original Master database from the installation media. There is a switch to recover a master database using the command line install. From there, you could then restore your master database backup.
June 5, 2008 at 7:50 am
dump all your logins and any sql login passwords in a file and keep it in a safe place. file server that is backed up, another db somewhere or maybe even send it to yoursel via gmail.
rebuild and install SQL on the server. manually create the logins. restore your databases
June 5, 2008 at 8:39 am
jsciii (6/5/2008)
Has anyone successsfully recovered an instance on a cluster when the master db is missing?
SQL Server 2000, yes, but I haven't faced the torture of having to do it with SQL Server 2005, and the procedure is different. My recommendation is that you go ahead and get on the phone with Microsoft support. Having that flag pop up is unusual. They may ask you to do a manually uninstall and then reinstall, but at least you'll have their folks who support customers day in and day out assisting you each step of the way.
K. Brian Kelley
@kbriankelley
June 5, 2008 at 10:59 am
Progress....
I was able to get the instance up by:
Copying all the system db files from another instance
starting the instance in single user mode
restoring the last master db backup
Now, when I try to start the instance normally I get an error that it cannot access the MODEL db file. It is referencing the location from the other instance.
This does not make sense. I queried the master_files view and everything shows the correct location.
I found if I put the original master db back, it works. It would appear the restore is not restoring the db completely.
Any inpput on why the old location is being referenced after the master db restore and why putting the original master back works?
Thanks
June 6, 2008 at 6:01 am
This will not help now but in the future. This is what I have done. After you install and get it all done shutdown the SQL Server services and make a backup copy of the data folder .mdf and .ldf files and keep them somewhere. Do this whenever you have a chance to reboot the server or an outage window like after more dbs/logins or DTS packages have been created. This way if something disastous happens you have at least a starting point if master gets corrupted.
June 9, 2008 at 12:41 pm
Success
In order to restore an instance when there are no system databases except in backup form:
Copy system databases from another instance to the exact location where the instance expects to find them
Start SQL in single user mode
restore the Master database
stop instance
restore resource database
Start SQL in single user mode
Detach all databases from master
Attached Model and MSDB with new location
stop instance
start instance normally
Restore Model and MSDB
At this point the system should be up and ready to restore user databases.
Thanks to all for helping me work through this.
September 30, 2009 at 11:38 am
--------------------------------------------------------------------
Copy system databases from another instance to the exact location where the instance expects to find them
-------------------------------------------------------------
what do you mean by this? How to do this?
In my case backup master have different location pointer than original. Do i have to use move command on restore for this?
September 30, 2009 at 5:00 pm
What do you mean by restore resource database when instance is stopped?
MJ
October 1, 2009 at 2:58 am
ruchika thapa (9/30/2009)
--------------------------------------------------------------------Copy system databases from another instance to the exact location where the instance expects to find them
-------------------------------------------------------------
what do you mean by this? How to do this?
In my case backup master have different location pointer than original. Do i have to use move command on restore for this?
Hi Ruchita,
After restoring Master Database in SQL Server 2005 it will check for all user database files to be in the same location which was in the old server .
ie if u have server A with all database files in D drive and if u restore the master database backup of server A to another server B(new installation) you cant put the files in C drive...It will throw error that the database files are missing.
October 1, 2009 at 3:13 am
MANU-296622 (9/30/2009)
What do you mean by restore resource database when instance is stopped?MJ
Restoring resource database is just copying the resource database .mdf and .ldf files to location where the master database files are.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply