Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

.ldf deleted and now cant attach database Expand / Collapse
Author
Message
Posted Wednesday, January 11, 2012 5:58 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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
Posted Wednesday, January 11, 2012 6:08 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 40,208, Visits: 36,617
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
Posted Wednesday, January 11, 2012 6:23 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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
Posted Wednesday, January 11, 2012 6:30 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 4:28 PM
Points: 4,421, Visits: 10,739
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
Post #1233929
Posted Wednesday, January 11, 2012 6:34 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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
Posted Wednesday, January 11, 2012 6:40 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 40,208, Visits: 36,617
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
Posted Wednesday, January 11, 2012 6:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:12 PM
Points: 6,490, Visits: 13,967
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"
Post #1233944
Posted Wednesday, January 11, 2012 6:57 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 40,208, Visits: 36,617
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
Posted Wednesday, January 11, 2012 7:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 29, 2014 7:16 AM
Points: 39, Visits: 279
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
Posted Wednesday, January 11, 2012 8:05 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 40,208, Visits: 36,617
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 »

Add to briefcase 12»»

Permissions Expand / Collapse