URGENT: Recovery DB with no log file

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

URGENT: Recovery DB with no log file

Author

Topic

BusmasterJones
Starting Member

4 Posts

Posted - 2006-08-17 : 15:08:49

Server crashed and with it went the log files which were on the drive that failed. We are currently having trouble getting BAK files off the tape. The MDF files are all intact, but efforts to do any tricky reattaches are failing. I have tried:

sp_attach_single_file_db @dbname = 'Op' ,
@physname = 'F:\Program Files\Microsoft SQL Server\MSSQL\Data\Op.MDF'

which gives the error:

Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'Op'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'F:\Program Files\Microsoft SQL Server\MSSQL\Data\Op_log.ldf' may be incorrect.

There is a no log file, but I guess it is looking in the same directory as a default.

Plan B: I create an Op db and dettach it. I copy the log file over to the location listed above and try the same command:

sp_attach_single_file_db @dbname = 'Op' ,
@physname = 'F:\Program Files\Microsoft SQL Server\MSSQL\Data\Op.MDF'

which gives the error:

Server: Msg 5173, Level 16, State 1, Line 1
Cannot associate files with different databases.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'Op'. CREATE DATABASE is aborted.
Log file 'F:\Program Files\Microsoft SQL Server\MSSQL\Data\Op_log.ldf' does not match the primary file. It may be from a different database or the log may have been rebuilt previously.

I just ran across db_rebuild_log() and DBCC REBUILD_LOG but need to read up on how these work. I plan to try them.

In the meantime, can anyone given me a path they have used to take an MDF file with no LDF file and get it back up and running again?

Thanks for any help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-17 : 15:21:44

You need to call MS about this. You've already tried the correct command for an MDF without an LDF.

Tara Kizer

BusmasterJones
Starting Member

4 Posts

Posted - 2006-08-17 : 15:58:31

Who do you call for such things?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-17 : 16:20:41

MS stands for Microsoft. They may be able to help you recover. No guarantees though without backups.

Tara Kizer

BusmasterJones
Starting Member

4 Posts

Posted - 2006-08-17 : 18:46:24

tkizer, I know that MS = Microsoft, but they have a bazillion ways to contact them and I was hoping to get to the right person without being passed around a lot.

In any event, I found a command from SQL Server Mag that does the trick. This is what worked for me:

-- Undocumented DBCC Command REBUILD_LOG

/* Create a new db named the same as the inaccessible one (in this case "Ops") */

EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO

BEGIN TRAN

UPDATE master..sysdatabases
SET status = status | 32768
WHERE name = 'Ops'

IF @@ROWCOUNT = 1
BEGIN
COMMIT TRAN
RAISERROR('emergency mode set', 0, 1)
END
ELSE
BEGIN
ROLLBACK
RAISERROR('unable to set emergency mode', 16, 1)
END

GO

EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
GO

-- 3. Stop SQL Server
-- 4. Replace the newly created Ops.mdf file with the old good one
-- 5. Rename the newly created Ops_log.ldf file
-- 6. Start SQL Server

-- 7. Run the following DBCC command:
DBCC REBUILD_LOG('Ops','E:\Program Files\Microsoft SQL Server\MSSQL\Data\Ops_log.LDF')

-- You should get the message:
-- Warning: The log for database 'Ops' has been rebuilt. Transactional consistency has been lost.

-- DBCC CHECKDB should be run to validate physical consistency. Database options will have to be reset,

-- and extra log files may need to be deleted.
-- DBCC execution completed. If DBCC printed error messages, contact your system administrator.

-- 8. Run data consistancy check
/*Perform physical and logical integrity checks at this point.
Bcp data out if your integrity checks demonstrate that problems exist.
*/
DBCC CHECKDB (Ops)

ALTER DATABASE Ops SET MULTI_USER
GO

-- Set database options and recovery model as desired.
GO

-- Make a backup and start to use the DB again

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-17 : 18:58:43

quote:
Originally posted by BusmasterJones

tkizer, I know that MS = Microsoft, but they have a bazillion ways to contact them and I was hoping to get to the right person without being passed around a lot.

Here is the number that I use: 800-936-3100. You don't get to decide who gets assigned to your ticket. But as long as you answer their questions, they'll assign your case to a SQL Server engineer.

I opened a ticket with them yesterday on our SQL Server 2005 test cluster.

Tara Kizer

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2006-08-18 : 03:09:03

I read the article! It is really nice and new to me.
http://www.sqlmag.com/Article/ArticleID/26044/sql_server_26044.html

------------------------
I think, therefore I am - Rene Descartes

nasirpinnacle
Starting Member

1 Post

Posted - 2009-12-30 : 05:58:34

Excellent Code.. has done a great job.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources