How to connect an MSSQL 2000 database with an MDF file only

Question

I have an old server with a non-existent evaluation version of SQL 2000 on it (since 2006) and two databases that were sitting on it.

For some unknown reason, the LDF log files are missing. Presumably deleted.

I have mdf files (and in one case an ndf file) for databases that previously existed on this server, and I try to run them and run them in another SQL 2000 box where I am sitting.

sp_attach_db complains that the log file is missing and it will not attach the database. Attempts to trick him using a log file from a database of the same name failed. sp_attach_single_file_db will not work either. The mdf files have obviously not been cleaned.

How can I connect and read databases?

Answer 1

I found this answer that worked with my SQL 2000 machines:

How to mount a database with an uncleaned MDF file.

Step 1: Create a new database with the same name and using the same files as the old ones on the new server.

Step 2: Stop the SQL server and transfer the mdf files (and any ndf files) on top of the new ones you just created. Delete all log files.

Step 3: Run SQL and run this to put the database 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 the SQL server and make sure that the database is working successfully in emergency mode.

Step 5: Run this undocumented dbcc option to rebuild the log file (in the right place)

 DBCC REBUILD_LOG(TestDB,'D:\SQL_Log\TestDB_Log.LDF') 

Step 6: You may need to reset the status. Even if you do not, it will not harm you.

 exec sp_resetstatus TestDB 

Step 7: Stop and run SQL to see the recently restored database.

Answer 2

In Enterprise Manager, right-click the server and select Attach Database. Select the MDF file and click OK. He will then ask you if you want to create a new log file or not. Say yes.