Rebuild lost or deleted transaction log files

SQL Server Administration: Every so often someone will call with a terrible problem because a drive was lost or someone mistakenly deleted transaction log files.  When this happens, the affected databases are not useable and are usually marked as SUSPECT or IN RECOVERY in Management Studio.  Usually the best option is to restore the database from a backup however, sometimes backup files may be lost or simply not set up and therefore do not exist.  If the transaction log files cannot be recovered and a database backup is not available a last ditch option is to force SQL Server to rebuild the lost transaction log files.  This option should be the last to be considered but there are cases where doing this is justified.  Since the transaction log file is built as a brand new file, any transactions that were in the old transaction log file would be lost so it would be possible that data corruption would occur.  You will obviously need to create folders for the transaction logs on a drive with enough space to hold the new log files.

Force a new transaction log to be built

Use this statement to get the names to substitute in the appropriate places in the ALTER DATABASE statements that will follow:

select d.name as dbname, m.name as logicalname, m.physical_name

from sys.master_files m

join sys.databases d on (d.database_id = m.database_id)

where m.type = 1 — Log

order by 1, 2

You may want to use the actual file name from the physical_name column in the FILENAME value below.  Use the new folder path as the path in the FILENAME value.  Run each statement one-by-one and especially pay attention to what the CHECKDB output returns if there are errors.  If it says you need to run REPAIR_ALLOW_DATA_LOSS then uncomment the command to do so and run that.  When you get to no errors from the DBCC CHECKDB then you can put the database in MULTI_USER and can use the database then.

USE master

GO

ALTER DATABASE [dbname] SET EMERGENCY

GO

ALTER DATABASE [dbname] SET SINGLE_USER

GO

ALTER DATABASE [dbname] REBUILD LOG ON

(NAME= logicalname, FILENAME=’Drive path to new file location\Log file name.ldf’) — Like ‘E:\MSSQL\LOGS\DB_Log.ldf’

GO

DBCC CHECKDB ([dbname])

GO

— DBCC CHECKDB ([dbname], REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;

— GO

ALTER DATABASE [dbname] SET MULTI_USER

GO