Where oh where did my LDF go?

This is a follow-up to my previous post on recovering system databases. This will cover several ways to recover missing LDF files.

For this and some other testing I have used the Chinook database found at Codeplex. The convention used in this post is SQL Data files are on the S: drive and LDF files are on the L: drive.

If the database has been cleanly detached and the LDF gets deleted it can be recreated when attaching by using any of the following scripts:

CREATE DATABASE Chinook ON (FILENAME = 'S:\SQL_DATA\Chinook.mdf') FOR ATTACH;

CREATE DATABASE Chinook ON (FILENAME = 'S:\SQL_DATA\Chinook.mdf') FOR ATTACH_REBUILD_LOG;

EXEC sp_attach_single_file_db @dbname = 'Chinook' , @physname = 'S:\SQL_DATA\Chinook.mdf';

If SQL Server has been cleanly shutdown and an LDF gets deleted testing with the Chinook database shows that when SQL Server is restarted it will recreate the LDF.

To see what happens when SQL Server crashes I ran an update query with a BEGIN TRANSACTION but no COMMIT to create an open transaction. I then stopped the SQL Server database engine causing an error.

(1 row(s) affected) Msg 596, Level 21, State 1, Line 0 Cannot continue the execution because the session is in the kill state. Msg 0, Level 20, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded.

The LDF files is then deleted and when SQL Server database engine is restarted the Chinook database shows Recovery Pending.

Best case is that the location for the LDF is still present or can be recreated by adding a new L: drive. If no backup set is available to restore and get back to as close as the time of crash as possible the database can be made useable by running the following scripts however any transactions in the LDF that were not written to the MDF (NDFs) will be lost.

ALTER DATABASE [Chinook] SET EMERGENCY; ALTER DATABASE [Chinook] SET SINGLE_USER; DBCC CHECKDB([Chinook], REPAIR_ALLOW_DATA_LOSS); ALTER DATABASE [Chinook] SET MULTI_USER;

You will see the following messages before the DBCC results:

File activation failure. The physical file name "L:\SQL_LOGS\Chinook_log.ldf" may be incorrect. 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. Warning: The log for database 'Chinook' has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files. DBCC results for 'Chinook'.

If the database data files must be moved to a different server a “hack attach” must be used.

To do a hack attach you first create a new database with the same name as the broken database. Create the same file structure as the broken database. Take the database offline then replace the data files with the ones from the broken database then bring the database online; you may receive an error message; just click OK. It will show as recovery pending; you may need to refresh the databases list to see this. Now you can run the scripts previously listed to make the database usable. You will see this in the messages window informing you that the files didn’t come from same database originally.

Msg 5173, Level 16, State 1, Line 8 One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup. Log file 'L:\SQL_LOGS\Chinook_log.ldf' does not match the primary file. It may be from a different database or the log may have been rebuilt previously. Warning: The log for database 'Chinook' has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files. DBCC results for 'Chinook'.

If the database previously had service broker enabled you will not be able to simply enable it since the database service broker guid will not match that stored in master.sys.databases. If you look at the database properties it will appear to match but that simply tells us that SQL Server pulls the database properties from master database. If you try to enable service broker you will see the following:

ALTER DATABASE Chinook SET ENABLE_BROKER

Msg 9776, Level 16, State 1, Line 8 Cannot enable the Service Broker in database "Chinook" because the Service Broker GUID in the database (AD93393D-7798-486E-8406-1D887CE9655A) does not match the one in sys.databases (D49E0203-E48D-46CE-ABA9-3585BA768ECA). Msg 5069, Level 16, State 1, Line 8 ALTER DATABASE statement failed.

You can fix this by running the following statement:

ALTER DATABASE Chinook SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

This will reset the guid in both places and enable the broker. You can check this with this query:

SELECT [name] ,[service_broker_guid] ,[is_broker_enabled] FROM [master].[sys].[databases]