Solved

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

Posted on 2010-09-06
13
745 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Suggested Solutions

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 this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

733 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