I have an MDF file and no LDF files for a database created in MS SQL Server 2005. When I try to attach the MDF file to a different SQL Server, I get the following error message.
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
I would like to accomplish any one of the following options:
What SQL commands can I try to get my database going again?
FROM a post at SQL Server Forums Attaching MDF without LDF:
If you want to attach a MDF without LDF you can follow the steps below It is tested and working fine
Create a new database with the same name and same MDF and LDF files
Stop sql server and rename the existing MDF to a new one and copy the original MDF to this location and delete the LDF files.
Start SQL Server
Now your database will be marked suspect 5. Update the sysdatabases to update to Emergency mode. This will not use LOG files in start up
Sp_configure "allow updates", 1 go Reconfigure with override GO Update sysdatabases set status = 32768 where name = "BadDbName" go Sp_configure "allow updates", 0 go Reconfigure with override GO
Restart sql server. now the database will be in emergency mode
Now execute the undocumented DBCC to create a log file
DBCC REBUILD_LOG(dbname,'c:dbname.ldf') -- Undocumented step to create a new log file.
(replace the dbname and log file name based on ur requirement)
Restart SQL server and see the database is online.
UPDATE: DBCC REBUILD_LOG does not existing SQL2005 and above. This should work:
USE [master] GO CREATE DATABASE [Test] ON (FILENAME = N'C:MSSQLDataTest.mdf') FOR ATTACH_REBUILD_LOG GO