SQL Server Performance Forum – Threads Archive

Lost Transaction Log

Is it possible to get a database up and running when the transaction log to that database is lost or damaged. At the moment the database in question has been detatched from the sql server and I am unable to re-attatch it without the log file. Cheers. Gary J

Without the log file its not possible to access the database in SQL Server.
If the .MDF file is detached properly you can use SP_ATTACH_SINGLE_FILE_DB to attach the file which will create Tlog (.LDF) freshly without any issues. Make sure to maintain regular backups in future. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Cheers m8, I ran the following code and then got an error. EXEC SP_ATTACH_SINGLE_FILE_DB = ‘SiebTestdb’, @physname = ‘D

rogram filesMicrosoft SQL ServerMSSQLDataSiebelTestdb_Data.MDF’ Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ‘=’. Any ideas which part is wrong. Thanks again. Gary J

You are missing @dbname .
EXEC SP_ATTACH_SINGLE_FILE_DB @dbname = ‘SiebTestdb’, @physname = ‘D

rogram filesMicrosoft SQL ServerMSSQLDataSiebelTestdb_Data.MDF’ Bambola.

Thanks Bambola, that is the reason I always suggest to refer BOL for syntax. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Thanks very much guys. Gary J

Ok I tried the new code and I get another error. Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database ‘SiebelTestdb’. CREATE DATABASE is aborted.
Device activation error. The physical file name ‘D

rogram FilesMicrosoft SQL ServerMSSQLdataSiebelTestdb_Log.LDF’ may be incorrect. I thought it created a new log when this code is run, but above seems to be looking for or not found an existing .LDF file which currently does not exist. Cheers again.

You may try this option : Create a new database with the same filenames and locations which had the troubles.
Stop the SQL Services.
Copy the existing .MDF file to the newly created path.
As soon as you start the SQL the database will become suspect.
Then run DBCC REBUILD_LOG which rebuilds the log file and brings back database online. Try this on a test server and see how it goes.
BTW, what is the latest backup for this database? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

you can’t attach the file through the UI, but you should be able to use the following script EXEC SP_ATTACH_DB @dbname = ‘SiebTestdb’, @filename1 = ‘D

rogram filesMicrosoft SQL ServerMSSQLDataSiebelTestdb_Data.MDF’ This will create a new empty log for you and will mean that any transactions that were in your lost log are now rolled back… Cheers
Twan

Thanks for all your help PPL. I will try either or both options above today. Cheers.

For the fresh log file SP_ATTACH_SINGLE_FILE_DB is meant to designed. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

]]>