How to solve Micrososft SQL Server 2008 R2 Error: 1813

Recently I had a machine go down. I was able to get into recovery mode and transfer off most of the files. When I rebuilt the machine and reinstalled SQL server, I tried to attached my old database from the backup I took.

When I went to attach my log file I got the following error:

Attach database filed for Server 'SERVERNAME\INSTANCE'. (Microsoft.SqlServer.Smo)

Additional Information:

An excepetion occured while excuting a Trasact-SQL statement or batch.

(Microsoft.SQLServer.ConnectionInfo)

Could not open new database 'YOURDATABASENAME'. CREATE DATABASE is aborted.

File activation failure. The physical file name 'LOG FILE LOCATION' 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 environmental failure. (Microsoft SQL Server, Error: 1813)

Unfortunatly, the SQL log file was currupt and I didn't have a copy due to the server going down. So now what!?!?

The Solution:

Create a new database with the same exact name.

CREATE DATABASE YOURDATABASENAME

GO

Now stop Micrososft SQL Server.

Replace the MDF file with the old MDF file.

Start Micrososft SQL Server.

If everything went well the database should be in "Suspect" mode, which you check by running this:

SELECT DATABASEPROPERTYEX ('YOURDATABASENAME', 'STATUS') AS 'Status';

GO

Your database should now be in 'SUSPECT' mode.

Let's put her in emergency mode:

ALTER DATABASE YOURDATABASENAME SET EMERGENCY;

GO

Then single user mode:

ALTER DATABASE YOURDATABASENAME SET SINGLE_USER;

GO

And Finally:

DBCC CHECKDB (YOURDATABASENAME, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;

GO

Buy Me a Beer