4

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.

  • Someone may be intrigued enough to continue down this crazy path with you, but if I were in your shoes, I'd cut my losses and BCP out (or however else you prefer) every table into a new safe, normal database. – LowlyDBA Oct 23 at 0:55
  • Let's not use stigmatize mental illness by calling it "crazy", I'm just doing everything I can think to do. :-) I appreciate your insight though, I'll give that a shot, too! – William Koplitz Oct 23 at 2:12
1

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/

  • You go to work with the SQL Server you have, not the SQL Server you want to have. ;) Thanks for the answer, I'll give it a try! – William Koplitz Oct 23 at 16:26
  • Yes, do that. My assumption is that your syntax error is because it is not supported in SQL 2000 and you need to look specifically at available DBCC commands. My situation was different, we had the ldf but corrupted after a power outage – vikjon0 Oct 24 at 7:05
  • Thank you for your help, I used a combination of the link at the bottom of your email and the idea of a previous commenter to export the data to a new database. I appreciate your help and thoughtful response! – William Koplitz Oct 25 at 14:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.