“Could you help me, we deleted the database’s transaction log file and now that database is stuck in ‘Recovery Pending’?”
This was a panicked call that I received a few weeks ago.
“Sure, no problem” said I, “we’ll have to restore back to your last backup”
And then things went silent for a while before the inevitable, “it’s only a development database, we don’t take backups”.
Now, just let me drop this in here. Regardless of a database only being a development database, if it’s important enough to have you coming to me in a flap, then it’s important enough to be backed up. It may only be that you need a weekly full backup, but please make sure that you’ve always got some recovery point.
So the situation is this, the database is inaccessible, there’s no transaction log and there’s no backup. What can we do? We only really have one option, we’re going to have to rebuild the transaction log.
Big Disclaimer: Rebuilding the transaction log is the absolute last resort, only even think about it when you’ve exhausted all other options for recovery. You will most likely lose data and data integrity will most likely be impacted.
With the above disclaimer in mind, you need to realise that by rebuilding the transaction log in the way we’re going to look at, there is no way to recover any transactions that hadn’t been hardened to disk.
If you’re happy with these risks then we’ll continue.
The state that we’re in here is that someone, for some reason has deleted the transaction log. And this might not be as stupid and far fetched as it sounds. It was the situation that I was presented with and I’ve also heard plenty of times from people that might not know SQL all that well, the suggestion of deleting transaction log files when a drive might be running out of space. Some people just don’t realise the importance of them.
Recovery Pending, The First Sign that Something’s Wrong
If that has happened, the first sign that you’re likely to see is the database stuck in ‘Recovery Pending’. This indicates that SQL wasn’t able to roll forward or backwards transactions on yet hardened to disk on a restart. The usual reason for this is that there’s an issue with the transaction log.
You can confirm the cause of this by checking out the error log. You’ll find a number of entries along the lines of,
Starting up database ‘SQLUndercover’.
FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file ‘E:SQLLogsSQLUndercover_log.ldf’. Diagnose and correct the operating system error, and retry the operation.
File activation failure. The physical file name “‘E:SQLLogsSQLUndercover_log.ldf'” may be incorrect.
Let’s Get Rebuilding that Log
The first thing that we’re going to need to do is offline the database
ALTER DATABASE SQLUndercover SET OFFLINE
We can now use ALTER DATABASE REBUILD LOG to generate a new log file. You’ll need to know the logical name and the file path of the log files. These can easily be obtained from sys.master_files with the following query (obviously change the database name to whatever’s appropriate to you.
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('SQLUndercover')
AND type = 1
Now you’ve got the logical and physical names, let’s look at that ALTER DATABASE statement, for my database, it would be…
ALTER DATABASE SQLUndercover REBUILD LOG ON (NAME = SQLUndercover_log, FILENAME = 'E:SQLLogsSQLUndercover_log.ldf')
Running that will return a little warning about all the things that you need to think about now you’ve rebuild the log.
Warning: The log for database ‘SQLUndercover’ 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.
Bring the Database Online
The database at this point is still in an offline state so we’ll need to bring it back online.
ALTER DATABASE SQLUndercover SET ONLINE
Now let’s look at the database in SSMS, you’ll notice that something’s still not right.
The database is in Restricted User mode, that means that at the moment, only sysadmin and dbo users can access it. That’s to allow admins to get in there and check things over before allowing the unwashed masses access. After all, we know that the data could be in a bit of a mess, so this just let’s you see quite how much of a mess it is in and have a go at tidying things up.
Once we’re happy with the database, or as happy as we’re ever going to be, we can open up the doors and bring that database back into multi user mode.
ALTER DATABASE SQLUndercover SET MULTI_USER
One Last Thing to Do
Now that the database is back up and running, there’s still one last thing to do…. start scheduling regular backups. 
This can also be useful if you’re suffering from a corrupt transaction log and don’t have any backups.
Thanks for reading and I hope you’ve found this useful.