Click here to monitor SSC
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
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45066 Visits: 39904
It's a long shot and it doesn't always work but I've had some success in the past with such a thing.

1. Make sure the database in question is detached and that the MDF file is in a safe place. "Safe place" means NOT on the drive that you normally store your MDF files, in this case.

2. Create a new database with the same name as the database in question. Note that the logical and physical file names should match the old ones.

3. Properly detach the new database.

4. Copy (not move) the old MDF file from Step 1 over the new MDF file created in Steps 2 and 3.

5. Try to reattach the new database.

It doesn't always work but the answer is always "No" unless you "ask".

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8783 Visits: 16557
GilaMonster (1/11/2012)
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.

But it's logically the first place to start, why go to all the effort of hacking the file in if a simple attach may suffice.

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

"Ya can't make an omelette without breaking just a few eggs" ;-)
mo7amed.ashraf
mo7amed.ashraf
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 21
Try This:



ALTER DATABASE EmpDataBase REBUILD LOG ON (NAME=$DatabaseName$,FILENAME=N'$Path\DatabaseName.ldf$')
GO
DBCC CHECKDB
ALTER DATABASE $DatabaseName$ SET MULTI_USER
GO



DiverKas
DiverKas
SSC Veteran
SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)

Group: General Forum Members
Points: 253 Visits: 460
mo7amed.ashraf (6/18/2013)
Try This:



ALTER DATABASE EmpDataBase REBUILD LOG ON (NAME=$DatabaseName$,FILENAME=N'$Path\DatabaseName.ldf$')
GO
DBCC CHECKDB
ALTER DATABASE $DatabaseName$ SET MULTI_USER
GO




Necro post much? Its over a year old this thread.
kevaburg
kevaburg
Mr or Mrs. 500
Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)

Group: General Forum Members
Points: 577 Visits: 920
GilaMonster (1/11/2012)
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.



.....and afterwards run a CHECKDB to ensure that the database is consistent and that its integrity hasn't been compromised.
btzobyd
btzobyd
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 48
I have simillar issue after install SP4 for ms sql server 2008

Starting up database 'mssqlsystemresource'.
The resource database build version is 10.00.5500. This is an informational message only. No user action is required.
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.
Error: 5173, Severity: 16, State: 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 'D:\Microsoft SQL Server INSTANCE_NAME\MSSQL10.INSTANCE_NAME\MSSQL\Binn\mssqlsystemresource.ldf' does not match the primary file. It may be from a different database or the log may have been rebuilt previously.
The log cannot be rebuilt when the primary file is read-only.
Error: 945, Severity: 14, State: 2.
Database 'mssqlsystemresource' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.


Resolution:
I copied both files mssqlsystemresource.mdf and mssqlsystemresource.ldf from other patched server to default location. I copied with overwriting. Then service started.

Version was correct - upgraded.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47239 Visits: 44377
Try rebuilding the system databases. If that fixes the system resource problem you can then restore your backups of master, model (if necessary) and msdb.


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