Hi, I'm testing my recovery on a sql server 2005 database. The server has been restored, which includes the OS and sql server 2005 installation (binaries, full tape restore). The tape restore didn't put back the master.mdf and other .mdf files (can't be backed up when open), so I need to rebuild the master and do a database recovery on it and the other supporting databases (yes, I have database backups). I have installed the sql 2005 setup CDs in a folder and run the following command to rebuild the master, but nothing happens, it pauses for a couple of minutes before it returns to the prompt, but it does not put and new master.mdf files in the folder and I can't start the service in single user mode. "start /wait setup.exe /qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=mypassword" Anybody have any procedures for a full sql server 2005 recovery or suggestions on this, please post.
Thanks, Mike
- Follow via:
- RSS
- Email Alert
Question
0
Votes
How to rebuild/restore the master database in sql 2005
23rd Aug 2007
Answers (6)
0
Votes
Command seems right
taking off qn switch which supresses the dialogs might give you a clue or two.
If all else fails simply uninstalll sql server and install it again then add your other DBs and then restore them.
When you've got it working, add a scheduled job to back up Master, MSDB etc to the local drive. That will be on your tape then.
Just in case
http://msdn2.microsoft.com/en-us/library/ms144259.aspx
If all else fails simply uninstalll sql server and install it again then add your other DBs and then restore them.
When you've got it working, add a scheduled job to back up Master, MSDB etc to the local drive. That will be on your tape then.
Just in case
http://msdn2.microsoft.com/en-us/library/ms144259.aspx
Updated - 23rd Aug 2007
0
Votes
More Details on this
Here is some more help and information on this:
http://www.mshelpzone.com/Forums/topic.asp?TOPIC_ID=6
http://www.mshelpzone.com/Forums/topic.asp?TOPIC_ID=6
14th Nov 2007
0
Votes
Had some success
I had to recreate all system databases as part of a DR exercices, on a machine that had just the system & user backups and a restore of the filesystem.
After a lot of headscratching and trying this and that, the following worked a treat:
start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=mypassword REINSTALLMODE=omu
The operative command is REINSTALLMODE, which is hidden in the depths of
http://msdn.microsoft.com/en-us/library/ms144259.aspx
under the chapter, "To repair all files, rebuild the registry, and replace all SQL Server shortcuts from the command prompt".
The main issue I had (and which you probably have) is that the SQL Server has been upgraded to SP1 and I was restoring an earlier version of the system databases, which setup was not in favour of. The REINSTALMODE=o (letter Oh, not zero) indicates to setup that I want it to "Reinstall if the file is missing or if it is an older version". For other options, please see the above link, which points to "How to: Install SQL Server 2005 from the Command Prompt".
After a lot of headscratching and trying this and that, the following worked a treat:
start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=mypassword REINSTALLMODE=omu
The operative command is REINSTALLMODE, which is hidden in the depths of
http://msdn.microsoft.com/en-us/library/ms144259.aspx
under the chapter, "To repair all files, rebuild the registry, and replace all SQL Server shortcuts from the command prompt".
The main issue I had (and which you probably have) is that the SQL Server has been upgraded to SP1 and I was restoring an earlier version of the system databases, which setup was not in favour of. The REINSTALMODE=o (letter Oh, not zero) indicates to setup that I want it to "Reinstall if the file is missing or if it is an older version". For other options, please see the above link, which points to "How to: Install SQL Server 2005 from the Command Prompt".
6th May 2008
0
Votes
Rebuild master database in sql server 2005
Here are the instructions on how to rebuild master database. This would be useful during DR.
SQL SERVER 2005 install media should be restored to the same location from where sql server was installed originally.
The location can be found from SQLSetup001_ _SQL.log ? Search for Product in this log file. This point to the location from where the sql server 2005 was installed from.
Copy the software to this location
Go the sql server configuration and change the properties to startup manual and stop the sql server services
Then run the command after changing to location where sql server media exists
start /wait setup.exe /qn INSTANCENAME= REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=
Default instance name is MSSQLSERVER
Again stop the sql server services and bring up the instance in single user mode by executing
sqlservr.exe -m ( Need to change directory to location where sql server was installed. generally it will be in D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn)
Now log on sql server management studio and restore the master database from backup. After the restore is completed,
error message will be displayed. Ignore this and close the management studio.
Start the sql server services from sql server configuration manager or from services in administration tools.
Restore user databases. If the user databases were part of replication then
Right click databases.
Restore database.
Then select the location where the backup resides.
Select the database name from drop down and select option to override the data files.
Bharath Reddy.
bharath2985@gmail.com.
SQL SERVER 2005 install media should be restored to the same location from where sql server was installed originally.
The location can be found from SQLSetup001_ _SQL.log ? Search for Product in this log file. This point to the location from where the sql server 2005 was installed from.
Copy the software to this location
Go the sql server configuration and change the properties to startup manual and stop the sql server services
Then run the command after changing to location where sql server media exists
start /wait setup.exe /qn INSTANCENAME= REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=
Default instance name is MSSQLSERVER
Again stop the sql server services and bring up the instance in single user mode by executing
sqlservr.exe -m ( Need to change directory to location where sql server was installed. generally it will be in D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn)
Now log on sql server management studio and restore the master database from backup. After the restore is completed,
error message will be displayed. Ignore this and close the management studio.
Start the sql server services from sql server configuration manager or from services in administration tools.
Restore user databases. If the user databases were part of replication then
Right click databases.
Restore database.
Then select the location where the backup resides.
Select the database name from drop down and select option to override the data files.
Bharath Reddy.
bharath2985@gmail.com.
29th Jul 2008
Replies
the media you need has to be the same versions and edition e.g. SQL 2005 Desktop Edition, which drive you loaded it from might niot be that useful.
There's another step to the above as well, you've gone back to the initiial state, this means you need to redo any service packs and patches.
There's another step to the above as well, you've gone back to the initiial state, this means you need to redo any service packs and patches.
Tony Hopkinson
2nd Feb 2011
0
Votes
You can Successfully Restore the master database in sql 2005
Hello Mike,
Just for your help I can simply suggest you that to restore the master database in sql 2005 you can try SQL Recovery Tool, it is a fantastic utility which help you to rebuild the master.mdf...get the tool from the below link
http://www.sqlserverdatabase.com
The choice is yours, I used it and quite satisfied with this tool, hope it will solve your problem too
all the best
Regards,
Jenny
Just for your help I can simply suggest you that to restore the master database in sql 2005 you can try SQL Recovery Tool, it is a fantastic utility which help you to rebuild the master.mdf...get the tool from the below link
http://www.sqlserverdatabase.com
The choice is yours, I used it and quite satisfied with this tool, hope it will solve your problem too
all the best
Regards,
Jenny
2nd Feb 2011
Replies
If he hasn't already done it by now, he's probably been sacked....
Tony Hopkinson
2nd Feb 2011
0
Votes
You can Use Kernel for SQL Repair Software
Repairing corrupt and damaged SQL Server database has never been so easy before. For excellent and accurate database recovery, use Kernel for SQL Recovery software that is not only easy to use but its interactive and user-friendly screens makes this tool a favorite among novice computer users as well. It is the most effective tool to deal with any corruption issues and retrieve MDF files along with their inaccessible data. you can download this software from here http://www.sqldatabaserepair.net
17th Jul
