How to reconnect old log file to mdf file?

SQL Server Administration
1

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?

EDIT (more details):

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. It's fine if I can't recover the rows, I just need the DB Schema.

2

You need a copy of the original mdf file. Any attempt to attach with a given mdf file will modify the file.

I haven't been able to find any way to consistently re-attach an mdf after it's been modified by a failed attempt.

For just the schema structure, presumably any back up file would do, so I'm guessing you don't have a backup of the db at all?

3

Unfortunately, I don' t have a backup but at least I have the SQL Scripts for creating the DB and Tables. It's more work but at least I can still start fresh since I don't need the data anyway.

4

It is possible. You've to follow the below steps:

  1. First Put the .mdf and new .ldf file in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ folder
  2. Then go to sql software , Right-click “Databases” and click the “Attach” option to open the Attach Databases dialog box
  3. Click on the “Add” button to open and Locate Database Files From C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ folder
  4. Now, click the "OK" button. SQL Server Management Studio loads the database from the .MDF file.