I've a classic server having a defunct evaluation version of SQL 2000 onto it (from 2006), and 2 databases that have been looking at it.

For many unknown reason, the LDF log files are missing. Presumed erased.

I've the mdf files (as well as in one situation an ndf file too) for that databases which accustomed to exist on that server, and i'm looking to get them ready to go on another SQL 2000 box I've hanging out.

sp_attach_db gripes the logfile is missing, and won't attach the database. Tries to fool it using a logfile from the database with similar title unsuccessful totally. sp_attach_single_file_db won't work either. The mdf files have clearly not been cleanly detached.

How do you obtain the databases attached and readable?

I discovered this answer, which labored with my SQL 2000 machines:

How you can fasten a database having a non-cleanly detached MDF file.

Step One: Create a new database with same title, and which utilizes exactly the same files because the old one around the new server.

Step Two: Stop SQL server, and move your mdf files (and then any ndf files you've) over the top brand new ones you simply produced. Remove any log files.

Step Three: Start SQL and run this to place the DB in emergency mode.

sp_configure 'allow updates', 1
reconfigure with override
update sysdatabases set status = 32768 where name = 'TestDB'
sp_configure 'allow updates', 0
reconfigure with override

Step Four: Restart SQL server and realize that the DB is effectively in emergency mode.

Step Five: Run this undocumented dbcc choice to rebuild the log file (within the correct place)


Step Six: You will need to totally reset the status. Even when you do not, it will not inflict harm to do this.

exec sp_resetstatus TestDB

Step 7: Stop and begin SQL to visit your recently restored database.

In Enterprise Manager, right-click on the server and select Attach Database. Choose the MDF file and click on Ok. It'll then request you if you wish to produce a new log file or otherwise. Say Yes.

With help next software-mdf recovery,out of the box known tool is free of charge,plus it repair data from corrupted databases within the MS SQL Server format (files using the *.mdf extension),supports data extraction through the lan,can help to save retrieved data as SQL scripts, it's also easy to split data into files associated with a size,suitable for all supported versions of Microsoft Home windows, for example Home windows 98, Home windows Me, Home windows NT 4., Home windows 2000, Home windows XP, Home windows XP SP2, Home windows 2003 Server, Home windows Vista,tool props up following database formats: Microsoft SQL Server 7., 2000, 2005,may also repair .mdf files of Microsoft SQL Server 2005, repair mdf file of Microsoft SQL Server 2005 (64-bit).