Hello,

I'm using SQL Server 2012 and was attempting to move the msdb, model, and tempdb databases to a new location and accidentally gave their log files an mdf extension instead of ldf when providing the new path\filename.  After the server wouldn't start I checked my script and noticed my error.  I have good backups of my system databases, so I was hoping to start the MSSQLSERVER service in single-user mode (using the -m startup parameter) and then just restore master using sqlcmd.  Unfortunately the service was starting but I couldn't connect via sqlcmd using any of the three protocols (it said the server was not found or not accessible each time).  I also tried using the dedicated Admin connection but I got the same error.  Then I went into the Templates folder and copied the master, msdb, model, and tempdb templates into the DATA folder and tried to restart SQL Server but still no luck (now the MSSQLSERVER service won't start at all).  Is there an easy way to fix this mess without having to reinstall from the setup application?

Thanks,

Mike

Mike - Quite a tricky situation for you in here, I suggest you follow these steps and post back results:

• Identify new server (Having same SQL Server version & Build Number) and restore the (Old) master database backup from other machine on this machine with the name say master_new
• Ensure that while restoring, you have not changed the logical name of the files, just change the physical location to whatever you want
• Then, detach this newly restored database (master_new)
• Then, copy the ldf and mdf files and put them on old server in the same location where your original master was located
• Try and restart the service, it should work well for you

Let me know

As you have backup you can try this:

Restore your master database backup to another instance of SQL Server and in the "To database:" section of the Restore Database dialog box, use an alternate database name such as "recover_master" to avoid conflict with the master database on this instance of SQL Server. This will cause the mdf/ldf to be named "recover_master.mdf" and "recover_master_1.ldf". Then detach the recover_master database, go into the file system, copy the recover_master.mdf and recover_master_1.ldf to the instance of SQL Server with the corrupt master database. Delete the corrupt master.mdf and mastlog.ldf files, rename recover_master.mdf to master.mdf, and rename recover_master_1.ldf to mastlog.ldf. Now you're ready to try starting the SQL Server service again!

http://blogs.technet.com/b/fort_sql/archive/2011/02/01/the-easiest-way-to-rebuild-the-sql-server-master-database.aspx

Once master is online you can restore other database.

After checking the error log it looks like the startup process is trying to get to the E: drive to open the resource database (which is the DVD drive I used during SQL Server installation).  Unfortunately I can't do a NET START MSSQLSERVER /f /T3608 because I don't have permission on this server, and since it's in a locked room I can't just go over and pop the installation DVD into the E: drive.  Looks like I'll have to wait until Saturday when a few of the administrators will be in.  Fortunately this is a DEV machine, so as long as it's up by Monday things will be OK.

Mike

Mike - Did you try the steps outlined in my previous post. Let us know if you need further help on this

Hi Manu and Dave,

Sorry I didn't see your posts since I was typing mine.  Sounds like you both have a similar idea, and there is another server I can try this on.  The other server is a VM, so I'll plan on trying it on Saturday and have the VMWare administrator take a snapshot first.  I'll report back then and let you know whether it worked.

Thanks!

Mike

• Edited by SQLServerNovice2 1 hour 59 minutes ago

Hi Manu and Dave,

Sorry I didn't see your posts since I was typing mine.  Sounds like you both have a similar idea, and there is another server I can try this on.  The other server is a VM, so I'll plan on trying it on Saturday and have the VMWare administrator take a snapshot first.  I'll report back then and let you know whether it worked.

Thanks!

Mike

• Edited by SQLServerNovice2 Friday, April 24, 2015 5:06 AM

Hello Mike,

Database file extension doesnt have and have to be MDF and LDF. That is the default extension and we change that to make it secure for security reasons.

What error are you getting when you are restarting SQL Service? you can check windows error log as it will give start up error.

I dont think that you have corrupted Master database so restoring that will not solve your problem.

Hi Vishal,

I was able to fix the problem by copying a good master database and log from another server in order to get the instance started again, and then I restored my master backup over it.

The Windows Error log was giving me Access Denied on the log files that were renamed with .ldf extensions, and then it would throw an error when trying to create the associated .ldf files, saying that they already existed.  I took that to mean that I needed to restore the master database because it contained the incorrect names, and since the server wouldn't start (to allow me to get into the master database to fix the names) that there was no way to fix the problem without somehow recreating and restoring master to its original state.

• Edited by SQLServerNovice2 6 hours 22 minutes ago Corrected a mistake.

Hi Manu,

I was able to get the problem resolved by copying the master database and log from the other server to my "problem" server in order to get the instance running, and then I restored the master database over it.  The posts from both you and Dave were helpful because they gave me the idea to get another server involved.  I only tried my "copy" method first because I didn't want to change anything on the other server even temporarily if I could help it, and restoring master to it would temporarily impact it.  If that had failed then I would have tried your method as you outlined it, but I would've waited until our VM administrator was in so he could take a snapshot of the working machine, and then after I was done he could roll it back.

Thanks again for your help!

Mike

• Marked as answer by SQLServerNovice2 6 hours 36 minutes ago