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 Thursday, January 12, 2012 5:02 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 35,769, Visits: 32,437
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1234671
Posted Thursday, January 12, 2012 5:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:55 AM
Points: 6,752, Visits: 14,397
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"
Post #1234689
Posted Tuesday, June 18, 2013 4:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, January 5, 2014 12:55 AM
Points: 18, Visits: 16
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


Post #1464550
Posted Tuesday, June 18, 2013 6:30 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 16, 2013 8:28 AM
Points: 249, 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.
Post #1464611
Posted Wednesday, June 19, 2013 5:44 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, December 14, 2014 11:59 PM
Points: 333, Visits: 555
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.
Post #1465100
Posted Saturday, December 13, 2014 2:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, December 13, 2014 4:32 AM
Points: 1, Visits: 42
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.
Post #1643957
Posted Saturday, December 13, 2014 12:28 PM


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:36 AM
Points: 40,615, Visits: 37,080
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 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 #1643987
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse