.ldf deleted and now cant attach database

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » .ldf deleted and now cant attach database

12 posts, Page 1 of 2

1 2 »»

.ldf deleted and now cant attach database

Rate Topic

Display Mode

Topic Options

Author

Message

martin.kerr 34088

Posted Wednesday, January 11, 2012 5:58 AM

Valued Member

Group: General Forum Members
Last Login: Monday, May 13, 2013 8:46 AM
Points: 56, Visits: 94

Hi All,

I had deleted my .ldf as it was getting far too large! and then when i went back to use that database i recieve the following error when trying to create a new database with the existing .mdf

Msg 5173, Level 16, State 1, Line 1
One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
Log file 'H:\Logs\360MetaVerse_log.ldf' does not match the primary file. It may be from a different database or the log may have been rebuilt previously.
Msg 1813, Level 16, State 2, Line 1
Could not open new database '360MetaVerse'. CREATE DATABASE is aborted.

Thanks in advance

Post #1233912

GilaMonster

Posted Wednesday, January 11, 2012 6:08 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 37,732, Visits: 29,996

Restore from backup is probably the best option here.

The transaction log is not an optional piece of the database and you can't expect to just delete it with no effects.

p.s. Where did that ldf mentioned come from? An earlier date?

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1233914

martin.kerr 34088

Posted Wednesday, January 11, 2012 6:23 AM

Valued Member

Group: General Forum Members
Last Login: Monday, May 13, 2013 8:46 AM
Points: 56, Visits: 94

Hi,

I found this from googleing my problem and it worked!
Create database with same name as MDF file you have.
Stop SQL Server and swap MDF files. Make sure you also keep new database you just created.
Start SQL Server. Database will be now in suspect state because log file is not correct.
Run the following script:

USE [master]
GO
ALTER DATABASE [MyDatabase] SET EMERGENCY
GO
ALTER DATABASE [MyDatabase] SET SINGLE_USER
GO
DBCC CHECKDB ([MyDatabase], REPAIR_ALLOW_DATA_LOSS)
GO
ALTER DATABASE [MyDatabase] SET MULTI_USER
GO
ALTER DATABASE [MyDatabase] SET ONLINE
GO

Thanks anyway

Post #1233927

spaghettidba

Posted Wednesday, January 11, 2012 6:30 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:39 PM
Points: 4,804, Visits: 8,073

Really?
Do you understand what each step of that procedure does?
If so, would you still recommend it?

Get your two-cent-answer quickly
The Spaghetti DBA

Post #1233929

martin.kerr 34088

Posted Wednesday, January 11, 2012 6:34 AM

Valued Member

Group: General Forum Members
Last Login: Monday, May 13, 2013 8:46 AM
Points: 56, Visits: 94

To be fair, it wasnt critical data and i could have rebuilt manually but would have taken alot longer than using that script.

Post #1233931

GilaMonster

Posted Wednesday, January 11, 2012 6:40 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 37,732, Visits: 29,996

What you did there was the absolute, very, final last resort for this kind of problem. You could have transactional inconsistencies, you could have structural inconsistencies.

Stuff that needs doing:
Run CheckDB with no_infomsgs and all_errormsgs and hope there are no errors
Sort out your log maintenance. See - Managing Transaction Logs
Also, see the consequences for deleting a log: http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1233934

Perry Whittle

Posted Wednesday, January 11, 2012 6:55 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 5:53 AM
Points: 5,204, Visits: 11,158

martin.kerr 34088 (1/11/2012)

To re attach a database with a missing log file just use either

sp_attach_single_file_db

CREATE DATABASE .... FOR ATTACH_REBUILD_LOG

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" Wink

Post #1233944

GilaMonster

Posted Wednesday, January 11, 2012 6:57 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 37,732, Visits: 29,996

Perry Whittle (1/11/2012)

To re attach a database with a missing log file just use either

sp_attach_single_file_db

CREATE DATABASE .... FOR ATTACH_REBUILD_LOG

Which only works if the database was shut down cleanly before the log was deleted, not in all cases.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1233948

jarupan

Posted Wednesday, January 11, 2012 7:58 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 23, 2013 12:24 PM
Points: 29, Visits: 201

Hi Gail
Just curious about your comment
"Which only works if the database was shut down cleanly before the log was deleted, not in all cases"

If I do.
1) Copy mdf out (suppose I can stop SQL server).
2) Restart SQL server.
3) Delete that database
4) Copy step#1 to normal location.
4) use GUI to attach the database with remove log file out (it can not find it anyhow)
(I always use this way to attached learning database, normally it will not come with ldf).

Will this way work in all cases?

JJ

Note: Thanks you for all your topics and comments, I always learn from you.

Post #1234001

GilaMonster

Posted Wednesday, January 11, 2012 8:05 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 37,732, Visits: 29,996

jarupan (1/11/2012)

Will this way work in all cases?

No. It will only work if the database was cleanly shut down before the log was deleted/removed/not included. Shutting down SQL does not guarantee that the database will have been cleanly shut down (though SQL will try). Detach and offline first are safer, but it's still possible to get a DB that's not cleanly shutdown afterwards (full log is the easiest way to get that)

If the database was cleanly shut down a simple attach will work. If it wasn't, attach will fail and you'd have to hack the DB in and do an emergency mode repair

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1234005

« Prev Topic | Next Topic »

12 posts, Page 1 of 2

1 2 »»

Permissions