none
Migrating an mdf from old computer to new computer

    Question

  • Hi,

    Im completely inexperienced with SQL databases, and Im in desperate need of help trying to get a database migrated from my Dad's old computer to his new one.

    I apologize in advance for being long winded...

    Brief back story:

    Old computer was running XP and SQL Server 2005 with a proprietary medcal front end program called Chiro8000, which created database called PM_CHIRO.mdf.

    Old PC's motherboard decided it had had enough of life and one morning it simply stopped turning on. I put together a new computer with the exact same software, and installed the old harddrive as a slave so i could access the data.

    The new install of the Chiro8000 program created its own empty database at C:\Program Files\Forte Systems\Chiro8000\Data\PM_CHIRO.mdf

    The old database I need to restore is at D:\Program Files\Forte Systems\Chiro8000\Data\PM_CHIRO.mdf

    The Chiro program has a "database manager tool" but Ive been using MS SQL Server Management Studio Express

    Obviously if I try to just attach the old one I get "Cannot attach a database with the same name as an existing database" error.

    So I try detaching the new blank one and attaching the old version, which gives me:

    ______________________________________________________________________________________________________________

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

     

    The log scan number (9884:437:2) passed to log scan in database 'PM_CHIRO' 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 'PM_CHIRO'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 9003)

    _______________________________________________________________________________________________________________

    There is no .BAK to restore from, and the old database worked fine up until the point that the computer died. For those wondering, it did not crash while running, it shut down politely and then refused to turn on.

    I've also tried to rebuild the log using the syntax:

    USE [master]

    GO

    CREATE DATABASE [PM_CHIRO] ON

    (FILENAME = N'D:\Program Files\Forte Systems\Chiro8000\Data\PM_CHIRO.mdf')

    FOR ATTACH_REBUILD_LOG

    GO

     

    Which gives me the error:

    Msg 5173, Level 16, State 1, Line 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 'C:\Program Files\Forte Systems\Chiro8000\Data\PM_CHIRO_log.LDF' does not match the primary file. It may be from a different database or the log may have been rebuilt previously.

    Msg 1813, Level 16, State 2, Line 1

    Could not open new database 'PM_CHIRO'. CREATE DATABASE is aborted.

    So at this point...no clue... I'm sure all of you SQL people out there could figure this out pretty quick, but Im way over my head as it is.

    I dont have a .BAK

    If its a corruption issue Id love to know how to repair it, even if it means possibly losing some data, because right now Im up a creek without a paddle....and theres a waterfall...made of sharks.

    Thanks in advance.

    Friday, September 20, 2013 12:21 AM

Answers

All replies

  • Could you please run DBCC CHECKPRIMARYFILE('FileNameAndPath.mdf', 3) and share the output?

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Friday, September 20, 2013 1:11 AM
  • Okay, I ran

    DBCC CHECKPRIMARYFILE('D:\Program Files\Forte Systems\Chiro8000\Data\PM_CHIRO.mdf', 3)

    I got:

    Status: 2, fileid: 1,  name:PM_C_Data, filename:F:\Program Files\Forte Systems\Chiro8000\Data\PM_CHIRO.mdf                                                                                                                                                                                                      

    Status:66, fileid: 2, name: PM_C_Log, filename: C:\Program Files\Forte Systems\Chiro8000\Data\PM_CHIRO_log.LDF

    (2 row(s) affected)
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    ______________________________________________________________________________

    I am noticing that its referencing the log as being on the C: drive, even though thats the location of the log for the blank database, and the log for the old database is still in the same folder on the D: drive.

                                                                                                                                                                                                

    Friday, September 20, 2013 8:27 PM
  • Okay, figured it out.

    Had to delete the log file on the C: drive, THEN rebuild the log using

    EXEC sp_attach_single_file_db

    Its all good now!

    Friday, September 20, 2013 10:23 PM