Question Locked

How to rebuild/restore the master database in sql 2005

0 helpful
9 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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

9 replies

by tony hopkinson

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

by defigueiredog

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".

by pbbr_29

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.

by tony hopkinson

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.

by jenny16

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

by tony hopkinson

If he hasn't already done it by now, he's probably been sacked....

by rodyroon

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

This discussion is closed to new replies.