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.
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
USE [master] DBCC CHECKDB('foo') GO
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:
- 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.
ALTER DATABASE foo SET MULTI_USER WITH ROLLBACK IMMEDIATE
Still no dice.