How to Attach a SQL Server Database without a Transaction Log and with Open Transactions
Issue description:You want to attach a SQL Server database that does not have the transaction log file and you get the following error when you try to attach the data file:
"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."
In this tip I will show how you can successfully attach a database when you get this error.
Solution:
Here I will cover the not so uncommon scenario where someone gives you a SQL Server database to attach to your instance, but only gives you the *.mdf file. Unfortunately, when you try to attach the database the SQL Server engine complains about the missing transaction log and aborts the attachment process
Test Environment Setup
First we will create our sample database and set the recovery model to Full by running the scripts below in SQL Server Management Studio.
USE [master]
GO
CREATE DATABASE [TestDB]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'TestDB_file1',
FILENAME = N'E:MSSQLTestDB_1.mdf',
SIZE = 128MB ,
MAXSIZE = UNLIMITED,
FILEGROWTH = 64MB)
LOG ON
( NAME = N'TestDB_log_file1',
FILENAME = N'E:MSSQLTestDB_1.ldf',
SIZE = 8MB,
MAXSIZE = 2048GB,
FILEGROWTH = 8MB)
GO
ALTER DATABASE TestDB SET RECOVERY FULL
GO
The next script will create our sample table.
USE TestDB GO SELECT * INTO TestTable FROM sys.objects
Now we are going to add some sample data. We want the insert statement to take enough time to let us force the shutdown of the test instance while it is still running. This will let the database be in an inconsistent state needing to perform recovery at the next database startup.
USE TestDB; GO INSERT INTO dbo.TestTable SELECT a.* FROM TestTable a CROSS JOIN sys.objects b CROSS JOIN sys.objects c CROSS JOIN sys.objects d
In another window in SQL Server Management Studio execute the following statement to force the instance shutdown.
SHUTDOWN WITH NOWAIT
After stopping the instance, delete the log file then start up the SQL Server service. If you refresh the Databases view in SQL Server Management Studio you will see that our test database is inaccessible because it is marked as Recovery Pending.
At this point we have an orphaned and inconsistent database file.
First, let’s clean the system catalog by dropping the database. We must set the database offline to copy or rename the data file that will be the subject for our tests.
USE master GO ALTER DATABASE TestDB SET OFFLINE GO
Then we must clean the system catalog metadata by dropping the database.
USE master GO DROP DATABASE TestDB GO
Trying to Attach the Damaged SQL Server Database
When you are asked to attach a database with one data file and no log, the first thing that may come to mind is the old and deprecated sp_attach_single_file_db.
USE master GO EXEC sys.sp_attach_single_file_db @dbname = 'TestDB', @physname = N'E:MSSQLTestDBCopy.mdf' GO
But after you execute the previous script you will see that it fails with this error message:
"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."
See the image below as a point of reference.
Since sp_attach_single_file_db is deprecated and has been replaced with CREATE DATABASE..FOR ATTACH, let’s try this to see if we have more luck.
USE [master] GO CREATE DATABASE [TestDB] ON ( FILENAME = N'E:MSSQLTestDBCopy.mdf' ) FOR ATTACH_REBUILD_LOG GO
We face the same error message telling us that the log of the database cannot be rebuilt.
At this point we tried everything, but there is another way, make the engine believe that the database is already attached.
Attaching the Damaged SQL Server Database
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:MSSQLTestDB_Repair_1.mdf', SIZE = 8MB , MAXSIZE = UNLIMITED, FILEGROWTH = 64MB) LOG ON ( NAME = N'TestDB_Repair_log_file1', FILENAME = N'E:MSSQLTestDB_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
At this point we can change the file location of our new database to point to our orphaned mdf file and set the location of the log file to a non-existent file.
USE master GO ALTER DATABASE [TestDB_Repair] MODIFY FILE(NAME='TestDB_Repair_file1', FILENAME= 'E:MSSQLTestDBCopy.mdf') ALTER DATABASE [TestDB_Repair] MODIFY FILE(NAME='TestDB_Repair_log_file1', FILENAME= 'E:MSSQLTestDBCopy.ldf') GO
Let’s bring the database back online.
USE master GO ALTER DATABASE [TestDB_Repair] SET ONLINE GO
We don’t have to be SQL Server gurus to know that the previous script will fail. But if you take a look at the error message of the next screen capture you will see that when SQL Server didn’t find the transaction log file (remember that we changed the system catalog to point to a file that doesn’t exist) it tries to rebuild it. And of course its attempt to rebuild the log fails with the same error message we had while trying to attach our orphaned *.mdf file, only in this case the *.mdf was successfully attached leaving us one step closer to our objective.
Rebuilding the SQL Server Transaction Log
Now you will see that it isn’t very complicated to rebuild the SQL Server transaction log, but you must accept the fact that you will lose data. In fact, you should use this method to recover a damaged database if restoring the database from a backup is not possible. The reason behind this is that you can lose data other than the last user activity. For example, if a transaction was updating an index and the update operation performed a page split, you may lose previously committed transactions that were no longer in the transaction log because page splits are a logged operation.
The next script includes several commands that I put together to bring our sample database back online. Further on I will explain the commands and why I decided to execute all of them in a single script, but first let's take a look.
USE master GO DBCC TRACEON(3604) GO ALTER DATABASE TestDB_Repair SET EMERGENCY GO ALTER DATABASE TestDB_Repair SET SINGLE_USER GO DBCC CHECKDB('TestDB_Repair', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS GO ALTER DATABASE TestDB_Repair SET MULTI_USER GO
The first step in the previous script is to send all output from the DBCC commands to the query results instead of to the error log. The next two steps set the database to emergency mode and single user mode respectively, so we can execute DBCC CHECKDB with the REPAIR_ALLOW_DATA_LOSS option. Finally the last command is to bring the database back to multi user mode.
On the next image you can see a screen capture of the execution of the previous script. I marked in red one of the output messages which states that the error log has been rebuilt.