Here's something that MIGHT help.
First to give proper credit: I got this from another site (
it was in the Administration forum and the subject was: LDF File has been deleted - Help!
This was posted by cchitanu as a solution and it appeared to help the person with the problem. I make no promises as I have never used this.
-SQLBill
------------------------------------------
Restoring databases when only data file available
• shutdown sql
• move the current database file or rename it
• restart sql server
• create a new database of the same name and log file and location as the old database and log file
• get rid of the old database(you may be able to right click delete it in this situation or used sp_removedb)
• create a new database of the right size and shape with correct log and data file locations
• stop sql
• rename the new databases.mdf or delete it if you don't have enough space - do not touch the .ldf
• move back in the old database .mdf file or rename it back again
• restart sql server(it should come up suspect)
-------------------------------------------------------------------------------------------------------------------
• From a query window, set the status so that you can update the system tables by running
the following query:
use Master
go
sp_configure "allow", 1
go
reconfigure with override
go
• Then set the status of the DB that is giving you the problem (XXXXX) into Emergency Mode by running the following query:
update sysdatabases set status = 32768 where name = '<DBName>'
go
checkpoint
go
shutdown with nowait
go
• Go into the data directory (MSSQL7\DATA) and rename the log file associated the DB in question (XXXX.ldf) to some temporary name, such as XXXX.TMP.
• Exit the query window.
• Start up SQL Server from a DOS command window by issuing:
sqlservr -c -T3608 -T4022.
• Bring up another query window and verify that the DB is in emergency mode by issuing:
select Name, Status from Sysdatabases where name = '<DB_Name>'
• Verify that the status is 32768. If it is, then issue the query:
dbcc traceon(3604)
dbcc rebuild_log('<DB_Name>','<log_filename>') <--- You will need the quotation marks
REBUILD_LOG should take less than 5 minutes even on a very large database. It should complete with the message “DBCC execution completed”
• Take the database out of bypass recovery mode by issuing the command :
update sysdatabases set status = 0 where name = '<DBName>'
• Exit the query window and then shutdown (Ctrl-C in the DOS window) and
• restart SQL server.
• Verify the status of the database by running DBCC NEWALLOC and DBCC CHECKDB on the database.
Edited by - cchitanu on 11/17/2003 12:47:00 PM
----------------------------------------------------
