How do I attach a MSSQL 2000 database with only an MDF file

Question

I have an old server with a defunct evaluation version of SQL 2000 on it (from 2006), and two databases which were sitting on it. For some unknown reason, the LDF log files are missing. Presumed deleted. I have the mdf files (and in one case an ndf file too) for the databases which used to exist on that server, and I am trying to get them up and running on another SQL 2000 box I have sitting around. sp_attach_db complains that the logfile is missing, and will not attach the database. Attempts to fool it by using a logfile from a database with the same name failed miserably. sp_attach_single_file_db will not work either. The mdf files have obviously not been cleanly detached. How do I get the databases attached and readable? JavaScript questions and answers, JavaScript questions pdf, JavaScript question bank, JavaScript questions and answers pdf, mcq on JavaScript pdf, JavaScript questions and solutions, JavaScript mcq Test , Interview JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)

Answer 1

I found this answer, which worked with my SQL 2000 machines: How to attach a database with a non-cleanly detached MDF file. Step 1: Make a new database with same name, and which uses the same files as the old one on the new server. Step 2: Stop SQL server, and move your mdf files (and any ndf files you have) over the top of the new ones you just created. Delete any log files. Step 3: Start SQL and run this to put the DB in emergency mode. sp_configure 'allow updates', 1 go reconfigure with override GO update sysdatabases set status = 32768 where name = 'TestDB' go sp_configure 'allow updates', 0 go reconfigure with override GO Step 4: Restart SQL server and observe that the DB is successfully in emergency mode. Step 5: Run this undocumented dbcc option to rebuild the log file (in the correct place) DBCC REBUILD_LOG(TestDB,'D:\SQL_Log\TestDB_Log.LDF') Step 6: You might need to reset the status. Even if you don't, it won't do any harm to do so. exec sp_resetstatus TestDB Step 7: Stop and start SQL to see your newly restored database.