Attach DB with corrupt .ldf? - SQL Server Forums

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)

Attach DB with corrupt .ldf?

Author

Topic

Kolyana
Starting Member

3 Posts

Posted - 2004-09-13 : 15:44:04

Hey gang,

I've been trying to restore a DB from it's MDF and LDF all morning; the catch? The LDF appears to be corrupt.

When I first started the SQL Server, the db in question was marked as "Suspect'. I did some research on this and it has caused me to attempt detaching, backing up, deleting the LDF, using ATTACH DB and even sp_attach_single_file_db, and sp_add_data_file_recover_suspect_db.

Nothing works.

So, with an MDF and no LDF ... is it possible to recreate this database somehow? Can I attach the MDF to another (empty) database to retrieve it's contents? (Tried it, couldn't get it to work), can I extract the contents of the MDF - even if it's just the objects and not the data itself - in some capacity?

I've read in various places about attaching an MDF with no LDF and the system will recreate the LDF as needed, but that doesn't work either.

Ideas are most, most, most welcome.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-13 : 15:46:42

Don't you a have a full backup and maybe the tlogs after that to restore?

Tara

Kolyana
Starting Member

3 Posts

Posted - 2004-09-13 : 15:58:12

Unfortunately nothing but the mdf and ldf, and the ldf appears to be corrupt. (It was the system policy to use ldf and mdf files as backups - not my idea, but the way that things were.)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-13 : 16:02:49

Using the ldf and mdf files can not be used as backups. You can't expect to restore from them unless you detached it or stopped the MSSQLSERVER service when you copied that out to backup. Otherwise, they are hot files. Have they fired whoever made that policy? Betcha it was someone who didn't understand databases. It's funny how they make policies about something that they don't even understand.

Anyway, what is the error that you get when you run sp_attach_single_file_db with the MDF?

How big is the MDF?

Tara

Kolyana
Starting Member

3 Posts

Posted - 2004-09-13 : 16:27:41

Solution:
LISTING 1: Undocumented DBCC Command REBUILD_LOG
EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO

BEGIN TRAN

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

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

-- Restart SQL Server at this point.

DBCC REBUILD_LOG('MyDatabase','C:\MyDatabase.ldf')

/*Perform physical and logical integrity checks at this point.
Bcp data out if your integrity checks demonstrate that problems exist.
*/

ALTER DATABASE MyDatabase SET MULTI_USER
GO

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

---

Incidentally, I realize that "detatching" a database should be utilized before one should ever hope to be able to reliably follow with "attach db", but these were the cirumstances that I was given: Two files, mdf and ldf, and the ldf is corrupt so all attach methods were failing.

The above REBUILD_LOG works WONDERFULLY, although there are obviously data integrity caveats. It has, however, successfully taken the database out of 'suspect' and replaced the .ldf file nicely in this instance.

jackyy
Starting Member

4 Posts

Posted - 2008-11-19 : 16:45:57

Tried the above procedure on SQL2005 to try and rebuild a corrupt LDF. Here is the result of running the first query:

Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.
Msg 259, Level 16, State 1, Line 4
Ad hoc updates to system catalogs are not allowed.
Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install.

Anyone have an hints on what is next?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-11-19 : 17:27:16

Your first step would be to start a new thread rather than replying to one that is 4 years old. You can always link to this one in your new thread.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Cosminm30
Starting Member

1 Post

Posted - 2008-11-26 : 09:42:30

@Kolyana thk's. It work's for me also.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources