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?

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.

Answer 2

In Enterprise Manager, right-click the server and choose Attach Database. Select the MDF file and click Ok. It will then ask you if you want to create a new log file or not. Say Yes.

Answer 3

With help next software-mdf recovery,as is known tool is free,also it repair data from corrupted databases in the MS SQL Server format (files with the *.mdf extension),supports data extraction via the local area network,can save recovered data as SQL scripts, it is also possible to split data into files of any size,compatible with all supported versions of Microsoft Windows, such as Windows 98, Windows Me, Windows NT 4.0, Windows 2000, Windows XP, Windows XP SP2, Windows 2003 Server, Windows Vista,tool supports the following database formats: Microsoft SQL Server 7.0, 2000, 2005,also can repair .mdf files of Microsoft SQL Server 2005, repair mdf file of Microsoft SQL Server 2005 (64-bit).

Answer 4

i dont know how to thank you.... i was searching for this problem since last five hours... finally found it here... thnx a ton again....