You are running SQL Server 2005, your master database has got corrupted because of sudden power failure or other reasons. The SQL Server will not start. You have tried registry setting, playing with changing protocol, disabled via protocol but nothing worked. Do do not have backup of the masters, there is no way you can start your server, you are on the versge of loosing all you precious data. Do not panic, you can recover it by copying a fresh master.mdf file but there are tricks so please read on.
1. The first hurdle is you do not have a backup of SQL Server 2005 master database. Well I have attached on master db SQL Server 2005. It is from fresh install and contains master.mdf mastlog.ldf files (both are required). When you copy this to the default location, all you existing logins will be lost which you can recreated. Also all the database will be detached which you will need to re-attached.
2. Depending on which version of OS are you using (32 bit or 64 bit), appropriate changes need to be made. If your SQL Server folder is C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data that means you are using 32 bit OS. In this case, copy the master and master log files in the folder and create another folder C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\DATA (note Program Files (x86) folder. This is required because the mdf files I created was on 64bit OS so the default path for other databases is this folder. If you already have C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\DATA as your current folder, simply copy the unzipped files and you should be all set.
3. If you were using 32 bit OS (no x86 folder), copy all the system databases into (x86) folder. They must be copied here so SQL Server can start. This step is required only for 32 bit OS.
4. Start SQL Configuration Manager and start SQL Server. It should not start.
5. In case of error, got computer management, event viewer and see the error logs. If it is about missing a database etc, simply copy it to x86 folder.
6. In SQL Server Management Studio, attach your database by right clicking on database and click attach. Select the mdf file of the database you want to attached.
7. Create new logins if needed.
Your database should be all set.
Please make sure to backup your database files just in case. Also note that I do not take any responsibility if anything goes wrong.
Error Messages that I encountered during trouble shoot
While troubleshooting my own problem, I received the following errors which I fixed by copying relevant data. The only way to find our the actual error is be looking at event viewer. I will leave the following error for reference purposes.
The first error that means my master database has gone corrupt. Most likely occurred because of power failure.
The log scan number (406:48:1) passed to log scan in database ‘master’ is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
After copying master.mdf and mastlog.ldf, I got this msg in event viewer
FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file ‘C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mssqlsystemresource.ldf’. Diagnose and correct the operating system error, and retry the operation.
After creating x86 folder and copying the two files, I got errors about the other files
FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file ‘C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\modellog.ldf’. Diagnose and correct the operating system error, and retry the operation.
Performance counter shared memory setup failed with error -1. Reinstall sqlctr.ini for this instance, and ensure that the instance login account has correct registry permissions.