Sql-server – How to solve the following error on attaching ldf-less database in SQL Server

sql-server-2008

Before you write about the dangers of not having backups, etc., please withhold judgement… perhaps I just inherited the problem?

I have an .mdf which which I am trying to attach to SQL Server 8.
I've tried a number of methods.

For example: 

CREATE DATABASE [foo] ON ( FILENAME = N'D:\Microsoft SQL Server\YourDataPath\Data\foo.mdf' )
FOR ATTACH_REBUILD_LOG
GO

Gives me the following error:
"Server: Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'ATTACH REBUILD LOG'"

If I run the query with only ATTACH, it errors out because of the missing log file.

Furthermore, I have tried to attach it through the Management Console.

I'm reasonably competent (usually) and as I wrote, I inherited this problem.

The database was completely hosed, I ran the emergency procedures, the CBCC CHECKDB commands, etc, and it appeared to be working (zero errors), however only in Read Only. I thought it might have been a problem with permission on the log file, and I read that you could detach it and reattach to start a new log file, and when I detached it – I found that I can't reattach it.

My head hurts. Any help will result in serious karma points. Halp!??!

Update, I did a pretty hack-y thing: I renamed the mdf, created a database with the same name, deleted the new mdf and ldf and then started it back up. It was found in 'suspect' mode.

So I did this:

USE [master]
GO

EXECUTE sp_configure 'allow updates',1
RECONFIGURE with OVERRIDE
GO

UPDATE sysdatabases 
SET status = 32768 
WHERE name = 'foo'
GO

Then this:

USE [master]
DBCC CHECKDB('foo')
GO

Finally this:

USE [master]
GO

ALTER DATABASE [dbname] SET MULTI_USER
GO

UPDATE sysdatabases 
SET status = 0 
WHERE name = 'foo'
GO

EXECUTE sp_configure 'allow updates',0
RECONFIGURE with OVERRIDE
GO

It appears to be back online, I can run SELECT statements and the database appears to be reconnected, however there is no ldf file and if I try to do an INSERT or an UPDATE the server bonks.

I tried to create a table and it says:

'STUFF' table
– Unable to create table.
Invalid transaction state: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not run BEGIN TRANSACTION in database 'foo' because the database is in bypass recovery mode.

I ran:

ALTER DATABASE foo SET MULTI_USER WITH ROLLBACK IMMEDIATE

Still no dice.

Best Answer

EDIT; Version 8, really? That is SQL 2000? I fear that is far too out of support.

I did get a call 10 years ago about a corrupt ldf file in SQL 2000 and fixed it with an undocumented & unsupported command.

I don't remember what command I used, but take a look here https://blog.sqlauthority.com/2007/05/15/sql-server-dbcc-commands-list-documented-and-undocumented/

Possibly one of these will do it, you have to see if anything is left on the google.

  DBCC dbrecover (dbname [, IgnoreErrors])
  DBCC dbrepair (‘dbname’, DROPDB [, NOINIT])

For later versions:

    CREATE DATABASE TestDb ON
    (FILENAME = N'C:\Program Files\....\MSSQL\DATA\TestDb.mdf')
    FOR ATTACH_REBUILD_LOG

or

    EXEC sp_attach_single_file_db @dbname='TestDb',
    @physname=N'C:\....\MSSQL\DATA\TestDb.mdf'

and a third approach

    CREATE DATABASE TestDb ON
    ( FILENAME = N'C:\Program Files\..R\MSSQL\DATA\TestDb.mdf')
    FOR ATTACH

and for future reference, if the is NOT cleanly shutdown https://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/

Related Question