The Undocumented ATTACH_FORCE_REBUILD_LOG Command

on

 By now we know that if we use the CREATE DATABASE..FOR ATTACH statement to recover the database it will fail because we do not have the transaction log file. Also, since there were open transactions in the database, we cannot use the ATTACH_ REBUILD_LOG command. There is an undocumented ATTACH_FORCE_REBUILD_LOG Command that as its name suggests, forces the CREATE DATABASE..FOR ATTACH statement to recover the database even when there were orphaned opened transactions. Basically, it creates a new log file.

USE master;

CREATE DATABASE [TestDB]
    ON  ( FILENAME = N'E:\MSSQL\TestDB_1.mdf' )
    FOR ATTACH_FORCE_REBUILD_LOG;
GO

In the next image we can see the execution of the previous command.

The Database was recovered successfully.

Attaching the Damaged SQL Server Database Without Using Undocumented Features

The first step is to create a new database.

USE [master]
GO

CREATE DATABASE [TestDB_Repair]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'TestDB_Repair_file1', 
			FILENAME = N'E:\MSSQL\TestDB_Repair_1.mdf',
			SIZE = 8MB , 
			MAXSIZE = UNLIMITED, 
			FILEGROWTH = 64MB) 
 LOG ON 
( NAME = N'TestDB_Repair_log_file1',
			 FILENAME = N'E:\MSSQL\TestDB_Repair_1.ldf',
			 SIZE = 8MB,
			 MAXSIZE = 2048GB,
			 FILEGROWTH = 32MB)
GO

Now we set the database offline.

USE master
GO

ALTER DATABASE [TestDB_Repair] SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

Comments

Post a Comment