Tag: missing data log file

  • How to attach a SQL Server database with a missing transaction log file?

    There may be situation where the transaction log file is corrupted or delete due to some issue, there is still hope to recover the database using data file. In the below script we will create the scenario and recover the database using data file only.

    -- create the below sample database
create database [SQLDBPool_5] 

-- execute the command to get the data log file location
sp_helpdb sqlhelpdesk

-- Copy the location of log file and data file from the output of the above command
-- C:\JSpace\sqldbpool_5_log.ldf, C:\JSpace\sqldbpool_5.mdf

-- Detatch the database
USE [master]
GO
ALTER DATABASE [SQLDBPool_5] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'SQLDBPool_5'
GO

-- Delete the log file
xp_cmdshell 'del "c:\JSpace\SQLDBPool_5_log.ldf"'


-- attach the database using MDF file only SQL Server will create the log file its self
USE [master]
GO
CREATE DATABASE [SQLDBPool_5]  ON 
( FILENAME = N'C:\JSpace\sqldbpool_5.mdf' )
FOR ATTACH
GO

-- You will the below message on the execution of the above command
/*
File activation failure. The physical file name "c:\JSpace\SQLDBPool_5_log.ldf" may be incorrect.
New log file 'c:\JSpace\SQLDBPool_5_log.ldf' was created.
*/