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

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.

Create a blank database with the old data base name. Now restore the database using the .bak file.
The database is restored.

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)".

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

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)

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

>> 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..

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

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.

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.

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.

Fixed issue