Lost transaction log 1

[cvgalante]

Is there any way to recover an MSSQL 2000 database from just the .MDF file when the .LDF file has been irretrievably lost? Backups are not available. Don't even ask the questions I asked when confronted with this - you won't like the answers any more than I did! %-)

If not, there is a 3 week old copy of the MDF and LDF, but they were simply copied, not detached. Any way to attach them at least?

Thanks for any help,

CVGalante

 

[mrdenny]

If they were copied after the SQL Service was stopped then you should be able to attach them.

You should be able to attach the mdf by using sp_attach_single_file_db. Check BOL for the full syntax.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief

 

[cvgalante]

Thanks - actually, the files were not detached properly, so the single file attach still would not work. I ended up doing some fairly bizarre things to get this back up, but was finally able to.

CVG

 

[mrdenny]

Please post what you did to get it back up and running.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief

 

[cvgalante]

Well, I didn't because it's really ugly and I wouldn't recommend it. Plus, I did a lot of things and not all of them were relevant, but here are the things I think actually led to the solution:

When I started working on the problem, someone had managed to remove the database entry from the server so that I couldn't operate on it and no attach option would work. Let's call it MyData. Initially, I:

1. Renamed the MDF file to oldMyData_Data.MDF
2. Recreated the MyData database the normal way in SQL.
3. Stopped the server
4. Deleted the new MyData_Data.MDF
5. Renamed oldMyData_Data.MDF back to MyData_Data.MDF
6. Started the server.

At this point, the MyData database shows up as suspect and you cannot operate on it because the log file doesn't really belong to it. But at least it shows up! I actually tried a number of normal fixes to remove the suspect status (like sp_resetstatus), but the suspect status would not go away. In fact, I opened master..sysdatabases and the actual status value did not have the suspect bit set! So I:

7. Directly modified the status of MyData in sysdatabases to the value that was on Northwind (28, I think). This causes the database to no longer appear as suspect in Enterprise Manager, and indeed you can open it and list tables and data. At this point I thought I was done, but it turned out you could not write to it. So I:

8. Detached the MyData database. This resulted in an error message that the database could not be cleanly detached. When I tried to reattach it, it complained that the log file appeared to be from another database (no big surprise). This told me to:

9. Finally use sp_attach_single_file_db to reattach the MDF file. Actually, it failed because it still tried to read the existing log file when I first did this, so I had to rename the log file and then run this command.

This successfully recreated a new log file and the data was completely accessible. I ran the existing maintenance plan to check integrity, optimize and back up the database.

Awful, wasn't it? But again, thanks for your help.