Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


I have lost the master.ldf for SQl 2005 - help - I do have a .BAK file

Posted on 2010-09-06
Medium Priority
Last Modified: 2012-05-10
I have also lost all of the other databases .LDF but do have the MDF's any help greatly appreciated.
Question by:Netstore
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +3
LVL 57

Accepted Solution

Raja Jegan R earned 1500 total points
ID: 33615436
To restore your master database, follow the steps given below:


And for user databases, follow the steps given below:


Expert Comment

ID: 33615444
You should be able to restore from the .bak file. Its a backup. The .ldf's are the log files. The .mdf's are the database files. The .mdf's are pretty much useless without the matching .ldf's. The .bak file will restore both .mdf and .ldf files.

Expert Comment

ID: 33615468
Create a blank database with the old data base name. Now restore the database using the .bak file.
The database is restored.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 33615523
Confused I've tried opening in single user mode from the command line sqlservr.exe /m and via the configuration manager by added -m; on the startup paramaters but as there is no master .ldf I get the following:

2010-09-07 08:27:36.87 spid5s      Warning ******************
2010-09-07 08:27:36.87 spid5s      SQL Server started in single-user mode. This
an informational message only. No user action is required.
2010-09-07 08:27:36.87 spid5s      Starting up database 'master'.
2010-09-07 08:27:36.88 spid5s      Error: 17207, Severity: 16, State: 1.
2010-09-07 08:27:36.88 spid5s      FCB::Open: Operating system error 2(error not
 found) occurred while creating or opening file 'E:\MSSQL.1\MSSQL\DATA\mastlog.l
df'. Diagnose and correct the operating system error, and retry the operation.
2010-09-07 08:27:36.88 spid5s      Error: 17204, Severity: 16, State: 1.
2010-09-07 08:27:36.88 spid5s      FCB::Open failed: Could not open file E:\MSSQ
L.1\MSSQL\DATA\mastlog.ldf for file number 2.  OS error: 2(error not found).
2010-09-07 08:27:36.88 spid5s      Error: 5120, Severity: 16, State: 101.
2010-09-07 08:27:36.88 spid5s      Unable to open the physical file "E:\MSSQL.1\
MSSQL\DATA\mastlog.ldf". Operating system error 2: "2(error not found)".

LVL 20

Expert Comment

by:Marten Rune
ID: 33615546
first you need a working sql server to do a restore. SQL server needs the master db, so step one is to rebuild it: http://msdn.microsoft.com/en-us/library/ms143269(SQL.90).aspx
Step two: Restore the datbase from a good backup: http://technet.microsoft.com/en-us/library/ms175535.aspx


Author Comment

ID: 33616297
Great stuff I now have a recovered master and msdb after re-installing and then using BAKs.

Looking there are no .BAK files for the user databases but I do have the MDF's any idea's?

BTW I didnt set this up or break it - just trying to recover it 8)
LVL 20

Expert Comment

by:Marten Rune
ID: 33617119
If your backup were up to date, restoring the master database ought to be enough to see your databases, msdb should take care of maintenance and jobs. see: http://www.dotnetspider.com/resources/16740-What-basic-functions-for-master-msdb.aspx (applies to sql2005 and 2008 aswell, but there are more systemdatabases)

If it didn't then you had an old backup (better fix this for future needs). Then you can attach databases using sp_attach_db http://technet.microsoft.com/en-us/library/ms179877.aspx look at example on the bottom of this page

check if logins exist in master database, otherwise you'll need to recreate these, and remap them agains databases. One database at a time using exec sp_change_users_login see http://msdn.microsoft.com/en-us/library/aa259633(SQL.80).aspx example on middle of the page.

Check if you have jobs and maintenanceplans. You may not have these, it depends. You should definately build maintenance plans for taking backup and handling index rebuild/reorganise and soforth. For both user and system databases. If you dont like maintenance plans (a good start if you're an accidental DBA), there is a solution by Ola Hallengren that I can highly recommend, see http://ola.hallengren.com/

Regards Marten
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33618430
>> Looking there are no .BAK files for the user databases but I do have the MDF's any idea's?

Ok, just follow the steps below:

1. Copy all *.mdf files to a new location.
2. Detach all user databases from SQL Server
3. Create new databases with user name as required
4. Stop SQL Server services.
5. Replace user database.mdf with the copied *.mdf files
6. Start SQL Server services.

This should help you get all user databases working..

Author Comment

ID: 33618746
Doesnt work it thinks they are in recovery mode and I can't do anything with them? Thanks for the help btw
LVL 70

Expert Comment

by:Scott Pletcher
ID: 33621715
Hopefully you have a copy of the physical .mdf files *before* you attempted to attach them.  An attach will modify the file.

So, first, if you can, make a backup copy of the existing physical .mdf files.

Before trying to "tri ck" SQL, try to work within its established methods.

If the dbs have only a single data file, attempt to attach them using:

EXEC sp_attach_single_file_db

That should recreate a log file to use.

If the dbs have multiple data files, you will have to use another method, but most dbs use only a single data file.
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33624438
Thanks ScottPletcher for reminding me about sp_attach_single_file_db.


In case sp_attach_single_file_db didn't worked, then try out the alternative method posted in my comment.
LVL 70

Expert Comment

by:Scott Pletcher
ID: 33628696
Btw, you can DROP the existing dbs to free them from recovery mode.  That is safe as long as you have a ** SEPARATE COPY ** of the original .MDF file.

Author Closing Comment

ID: 34103811
Fixed issue

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question