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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)

Group: General Forum Members
Points: 114134 Visits: 41362
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Perry Whittle
Perry Whittle
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26591 Visits: 17335
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
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 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
Old Hand
Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)

Group: General Forum Members
Points: 367 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
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2259 Visits: 1021
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
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

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

Group: General Forum Members
Points: 113786 Visits: 45440
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