locked
How to reconnect old log file to mdf file? RRS feed

  • Question

  • I have an MDF (Orig.mdf) file and LDF (Orig_log.ldf) file from before. I was encountering an out-of-memory error and I thought the reason was because of the large log file. So I ended up renaming the old log file (Orig_log.ldf.old) and attempting to attach the MDF file and having the log-file be re-built (since I didn't need the transaction logs anyway). This resulted in an error though "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."

    Of course, what I did was to delete the re-built log file (RebuiltLog.ldf) and renamed the old LDF file to its original name (Orig_log.ldf).

    Now, when I try to re-attach it again, it gives me the same error! I think SQL Server configured the MDF file to not match my original LDF file.

    So now without a way to attach the MDF, either through re-building the log or using the old log, how can I fix the MDF so that it goes back to being able process attachment and only encountering out-of-memory error?

    Thursday, May 23, 2019 6:20 AM

Answers

  • I just re-built the DB using SQL Scripts I was able to recover. I only needed the schema anyway. But I'll be performing regular backups in case I need it again.
    • Marked as answer by OCS.New Monday, May 27, 2019 2:59 AM
    Monday, May 27, 2019 2:59 AM

All replies

  • Which SQL commands exactly have you run in this process? Had you manually detached the database? Was MDF&LDF located on the same drive when it run out of space?

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, May 23, 2019 6:58 AM
  • Yes MDF & LDF are on the same drive. The drive didn't run out of space but rather the RAM probably. This was the previous error message: "Restore operation failed for database ‘InMemoryDB’ due to insufficient memory in the resource pool ‘default’."

    I've attempted the following to no avail:

    • Renamed the old log file and used SQL Server Management Studio "Attach" function to try and attach the MDF File -> attempted to re-build the log file but still threw out the error I mentioned above
    • Run the following query: CREATE DATABASE mydbname ON (FILENAME = 'c:\mydbname.mdf') FOR ATTACH_REBUILD_LOG ; -> threw the same error as above with additional details
    • Run the following query: sp_attach_single_file_db @dbname='mydbname',@physname='C:\mydbname.MDF' -> threw the same error as above with additional details
    • Created a blank DB using Management Studio with mydbname -> detached the DB -> replaced the MDF file with my own file -> attempted to Attach the DB -> still threw the same error
    • Created a blank DB using Management Studio with mydbname -> take offline the DB -> replaced the MDF file -> then attempted to take online -> threw the same error above with additional details

    The additional details error says: "Could not restart database "OQ.Primary.Mem". Reverting to the previous status.
    ALTER DATABASE statement failed.
    Log file 'C:\mydbname_log.ldf' does not match the primary file.  It may be from a different database or the log may have been rebuilt previously. (Microsoft SQL Server, Error: 5173)"

    Please note that I still have a copy of the original LDF file (since I just renamed it). However, I don't have any backups and only have the MDF and LDF files.


    • Edited by OCS.New Thursday, May 23, 2019 7:19 AM
    Thursday, May 23, 2019 7:19 AM
  • Thank you for the link. However, I was already able to try that to no avail when I was encountering the 1st error. Since it did not work, I tried to attach the MDF file ONLY, without the LDF file. I did this using the SQL Server Management Studio, renaming the old LDF file (Orig_log.ldf.old), and just selecting the MDF file in the Attach DB pane.

    After doing that, it tried to rebuild a NEW log file but threw out the error: "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." 

    I then deleted the NEW log file, renamed the old one back to the original file (Orig_log.ldf) and when I tried to attach the MDF and LDF again (using both original files), I am still encountering the "One or more files do not match error...", instead of the "Insufficient memory..." error.

    Thursday, May 23, 2019 8:25 AM
  • You cannot use database files from different logical point in times. SQL server uses LSN (Log Sequence Numbers) stored in the file header to keep the files in sync. If these don't match, then you have a no-go. So your option is to restore from a backup, or try to rebuild the log. The error you got when you tried to rebuild the log seems strange, so if you provide us with more details on the command you run, then we might be able to comment further.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Thursday, May 23, 2019 9:09 AM
  • Here's what happens if I try to attach just the MDF file and have the log automatically re-built:

    Here is the full error message:

    ===================================
    
    Attach database failed for Server 'VM1'.  (Microsoft.SqlServer.Smo)
    
    ------------------------------
    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=12.0.6024.0+((SQL14_PCU_Main).180907-0056)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476
    
    ------------------------------
    Program Location:
    
       at Microsoft.SqlServer.Management.Smo.Server.AttachDatabase(String name, StringCollection files)
       at Microsoft.SqlServer.Management.SqlManagerUI.AttachDatabaseData.PrimaryFile.Attach()
       at Microsoft.SqlServer.Management.SqlManagerUI.AttachDatabase.SendDataToServer()
    
    ===================================
    
    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
    
    ------------------------------
    Program Location:
    
       at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)
       at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType, Boolean retry)
       at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries, Boolean retry)
       at Microsoft.SqlServer.Management.Smo.Server.AttachDatabaseWorker(String name, StringCollection files, String owner, AttachOptions attachOptions)
       at Microsoft.SqlServer.Management.Smo.Server.AttachDatabase(String name, StringCollection files)
    
    ===================================
    
    The log scan number (806:58792:0) passed to log scan in database 'Primary.Mem' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
    Could not open new database 'Primary.Mem'. CREATE DATABASE is aborted.
    File activation failure. The physical file name "E:\MSSQL\Data\Primary.Mem_log.ldf" may be incorrect.
    New log file 'E:\MSSQL\Data\Primary.Mem_log.ldf' was created. (.Net SqlClient Data Provider)
    
    ------------------------------
    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=9003&LinkId=20476
    
    ------------------------------
    Server Name: VM1
    Error Number: 9003
    Severity: 20
    State: 9
    Line Number: 1
    
    
    ------------------------------
    Program Location:
    
       at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
       at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)
    
    

    Thursday, May 23, 2019 9:37 AM
  • You already tried so many options which I usually suggest in this situation. 

    Please note that I still have a copy of the original LDF file (since I just renamed it). However, I don't have any backups and only have the MDF and LDF files.

    As you do not have a backup and this is not good news. If there is corruption in MDF file then, you can give a try to DBCC CHECKDB repair options. Read, this TechNet article, how to repair SQL database when recovery via TSQL might not be possible

    Regards,

    Robin

    Thursday, May 23, 2019 11:09 AM
  • I just re-built the DB using SQL Scripts I was able to recover. I only needed the schema anyway. But I'll be performing regular backups in case I need it again.
    • Marked as answer by OCS.New Monday, May 27, 2019 2:59 AM
    Monday, May 27, 2019 2:59 AM