Solved

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

Posted on 2010-09-06
13
752 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
[X]
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
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

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

Author Closing Comment

by:Netstore
ID: 34103811
Fixed issue
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

688 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