Solved

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

Posted on 2010-09-06
13
731 Views
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.
0
Comment
Question by:Netstore
  • 4
  • 3
  • 2
  • +3
13 Comments
 
LVL 57

Accepted Solution

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

http://technet.microsoft.com/en-us/library/ms190679.aspx

And for user databases, follow the steps given below:

http://technet.microsoft.com/en-us/library/ms177429.aspx
0
 
LVL 8

Expert Comment

by:infolurk
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.
0
 

Expert Comment

by:sreekanthdora
ID: 33615468
Create a blank database with the old data base name. Now restore the database using the .bak file.
The database is restored.
0
 
LVL 5

Author Comment

by:Netstore
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)".

0
 
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

//Marten
0
 
LVL 5

Author Comment

by:Netstore
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)
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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
0
 
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..
0
 
LVL 5

Author Comment

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

Expert Comment

by:ScottPletcher
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.
0
 
LVL 57

Expert Comment

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

Netstore,

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

Expert Comment

by:ScottPletcher
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.
0
 
LVL 5

Author Closing Comment

by:Netstore
ID: 34103811
Fixed issue
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now