SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


.ldf deleted and now cant attach database


.ldf deleted and now cant attach database

Author
Message
martin.kerr 34088
martin.kerr 34088
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87367 Visits: 45272
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, MVP, M.Sc (Comp Sci)
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


martin.kerr 34088
martin.kerr 34088
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 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
Gianluca Sartori
Gianluca Sartori
SSCrazy Eights
SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)

Group: General Forum Members
Points: 9868 Visits: 13350
Really?
Do you understand what each step of that procedure does?
If so, would you still recommend it?

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
martin.kerr 34088
martin.kerr 34088
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87367 Visits: 45272
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, MVP, M.Sc (Comp Sci)
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


Perry Whittle
Perry Whittle
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19990 Visits: 17243
martin.kerr 34088 (1/11/2012)
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

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" ;-)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87367 Visits: 45272
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, MVP, M.Sc (Comp Sci)
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


jarupan
jarupan
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 388
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87367 Visits: 45272
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, MVP, M.Sc (Comp Sci)
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search