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
