none
Database restoration error - Urgent

    Question

  •  

    Hi Experts,

    I am setting up new production box. I have 2 separate boxes with SQL server 2000 Standard Edition with sp4.

    As SQL server standard edition don't have logshipping in it, I created manual jobs to ship logs every 10 mins and Full Backup once in a day from Server A to Server B.

    Database on server A have multiple filegroups. When I try restoring database backup on Server B which already has the database on standby, I get an error.


    Server: Msg 5173, Level 16, State 1, Line 1
    Cannot associate files with different databases.
    Server: Msg 5105, Level 16, State 1, Line 1
    Device activation error. The physical file name 'I:\sqldata\psi_db\PSI_DB_TESTDATA.mdf' may be incorrect.
    Server: Msg 5173, Level 16, State 1, Line 1
    Cannot associate files with different databases.
    Server: Msg 5105, Level 16, State 1, Line 1
    Device activation error. The physical file name 'I:\sqldata\psi_db\PSI_DB_ITEMDATA.mdf' may be incorrect.
    Server: Msg 5173, Level 16, State 1, Line 1
    Cannot associate files with different databases.
    Server: Msg 5105, Level 16, State 1, Line 1
    Device activation error. The physical file name 'I:\sqldata\psi_db\PSI_DB_OTHERDATA.mdf' may be incorrect.
    Server: Msg 5173, Level 16, State 1, Line 1
    Cannot associate files with different databases.
    Server: Msg 5105, Level 16, State 1, Line 1
    Device activation error. The physical file name 'I:\sqldata\psi_db\PSI_DB_ANALYSISDATA.mdf' may be incorrect.
    Server: Msg 5173, Level 16, State 1, Line 1
    Cannot associate files with different databases.
    Server: Msg 5105, Level 16, State 9, Line 1

    .
    .
    .
    .
    .
    .
    .
    This error continues for all the files I created on server A


    But the path I:\sqldata\psi_db\ exists.


    Restoration works fine when I don’t have any database on Server B.
    I am using the "replace" keyword while doing the restoration

    Please help, this is little urgent.

    Thanks in advance.

    Regards
    Sachin

    Friday, February 15, 2008 7:42 AM

All replies

  • Could you put in the sysatlfiles output for the database from both servers.

     

    And also the RESTORE command that you are using.

     

    Essentially from what I understand from your scenario, you need to be using RESTORE with "MOVE" and "NORECOVERY" keywords.

     

    HTH

     

    Friday, February 15, 2008 9:27 AM
  •  

    If the file locations on the source and destination server do not have the same folder structure, then a REPLACE will not help as REPLACE only REPLACES the existing files.

    Code Snippet

     

    REPLACE defn from BOL:

     

    REPLACE also allows RESTORE to overwrite an existing file that cannot be verified as belonging to the database being restored. Normally, RESTORE refuses to overwrite pre-existing files. WITH REPLACE can also be used in the same way for the RESTORE LOG option.

     

     

     

    However, a MOVE option is required in the RESTORE command because if the file locations between the two servers are different, then the backup from the source server will have the file information in the backup header listed as a location which essentially is not the correct location.

     

    Code Snippet

    MOVE defn from BOL:

     

    REPLACE also allows RESTORE to overwrite an existing file that cannot be verified as belonging to the database being restored. Normally, RESTORE refuses to overwrite pre-existing files. WITH REPLACE can also be used in the same way for the RESTORE LOG option.

     

     

     

     

    HTH

     

    Friday, February 15, 2008 9:33 AM
  •  

    Thanks you so much Amit for your reply. Happy that at least somebdy thought of helping me.

     

     

    -----Code starts here

     

    restore database psi_db from disk = '\\sdnetapp1\web_app_logs\SQLDBBackups\SDPROD-DB1\psi_db\SET-6-0_psi_db_FULL_200802140255.bak' with Move 'PSI_DB_Log3' to 'I:\sqldata\psi_db\PSI_DB_Log3.ldf',Move 'PSI_DB_Log2' to 'I:\sqldata\psi_db\PSI_DB_Log2.ldf',Move 'PSI_DB_Log1' to 'I:\sqldata\psi_db\PSI_DB_Log1.ldf',Move 'TCCFI_FG_File4' to 'I:\sqldata\psi_db\TCCFI_FG_File4.mdf',Move 'TCCFI_FG_File3' to 'I:\sqldata\psi_db\TCCFI_FG_File3.mdf',Move 'TCCFI_FG_File2' to 'I:\sqldata\psi_db\TCCFI_FG_File2.mdf',Move 'TCCFI_FG_File1' to 'I:\sqldata\psi_db\TCCFI_FG_File1.mdf',Move 'CAAV_FG_File3' to 'I:\sqldata\psi_db\CAAV_FG_File3.mdf',Move 'CAAV_FG_File2' to 'I:\sqldata\psi_db\CAAV_FG_File2.mdf',Move 'CAAV_FG_File1' to 'I:\sqldata\psi_db\CAAV_FG_File1.mdf',Move 'CTIO_FG_File2' to 'I:\sqldata\psi_db\CTIO_FG_File2.mdf',Move 'CTIO_FG_File1' to 'I:\sqldata\psi_db\CTIO_FG_File1.mdf',Move 'IA_FG_File3' to 'I:\sqldata\psi_db\IA_FG_File3.mdf',Move 'IA_FG_File2' to 'I:\sqldata\psi_db\IA_FG_File2.mdf',Move 'IA_FG_File1' to 'I:\sqldata\psi_db\IA_FG_File1.mdf',Move 'TestCenter_FG_File2' to 'I:\sqldata\psi_db\TestCenter_FG_File2.mdf',Move 'TestCenter_FG_File1' to 'I:\sqldata\psi_db\TestCenter_FG_File1.mdf',Move 'CTA_FG_File4' to 'I:\sqldata\psi_db\CTA_FG_File4.mdf',Move 'CTA_FG_File3' to 'I:\sqldata\psi_db\CTA_FG_File3.mdf',Move 'CTA_FG_File2' to 'I:\sqldata\psi_db\CTA_FG_File2.mdf',Move 'CTA_FG_File1' to 'I:\sqldata\psi_db\CTA_FG_File1.mdf',Move 'TestCenterDS_FG_File2' to 'I:\sqldata\psi_db\TestCenterDS_FG_File2.mdf',Move 'TestCenterDS_FG_File1' to 'I:\sqldata\psi_db\TestCenterDS_FG_File1.mdf',Move 'CTTS_FG_File2' to 'I:\sqldata\psi_db\CTTS_FG_File2.mdf',Move 'CTTS_FG_File1' to 'I:\sqldata\psi_db\CTTS_FG_File1.mdf',Move 'CTIR_FG_File4' to 'I:\sqldata\psi_db\CTIR_FG_File4.mdf',Move 'CTIR_FG_File3' to 'I:\sqldata\psi_db\CTIR_FG_File3.mdf',Move 'CTIR_FG_File2' to 'I:\sqldata\psi_db\CTIR_FG_File2.mdf',Move 'CTIR_FG_File1' to 'I:\sqldata\psi_db\CTIR_FG_File1.mdf',Move 'LOG_FILEGROUP_File4' to 'I:\sqldata\psi_db\LOG_FILEGROUP_File4.mdf',Move 'LOG_FILEGROUP_File3' to 'I:\sqldata\psi_db\LOG_FILEGROUP_File3.mdf',Move 'LOG_FILEGROUP_File2' to 'I:\sqldata\psi_db\LOG_FILEGROUP_File2.mdf',Move 'LOG_FILEGROUP_File1' to 'I:\sqldata\psi_db\LOG_FILEGROUP_File1.mdf',Move 'Candidate_Payments_FG_File4' to 'I:\sqldata\psi_db\Candidate_Payments_FG_File4.mdf',Move 'Candidate_Payments_FG_File3' to 'I:\sqldata\psi_db\Candidate_Payments_FG_File3.mdf',Move 'Candidate_Payments_FG_File2' to 'I:\sqldata\psi_db\Candidate_Payments_FG_File2.mdf',Move 'Candidate_Payments_FG_File1' to 'I:\sqldata\psi_db\Candidate_Payments_FG_File1.mdf',Move 'Candidate_Scores_FG_File4' to 'I:\sqldata\psi_db\Candidate_Scores_FG_File4.mdf',Move 'Candidate_Scores_FG_File3' to 'I:\sqldata\psi_db\Candidate_Scores_FG_File3.mdf',Move 'Candidate_Scores_FG_File2' to 'I:\sqldata\psi_db\Candidate_Scores_FG_File2.mdf',Move 'Candidate_Scores_FG_File1' to 'I:\sqldata\psi_db\Candidate_Scores_FG_File1.mdf',Move 'Test_Regn_FG_File4' to 'I:\sqldata\psi_db\Test_Regn_FG_File4.mdf',Move 'Test_Regn_FG_File3' to 'I:\sqldata\psi_db\Test_Regn_FG_File3.mdf',Move 'Test_Regn_FG_File2' to 'I:\sqldata\psi_db\Test_Regn_FG_File2.mdf',Move 'Test_Regn_FG_File1' to 'I:\sqldata\psi_db\Test_Regn_FG_File1.mdf',Move 'Candidate_photo_File3' to 'I:\sqldata\psi_db\Candidate_photo_File3.mdf',Move 'Candidate_photo_File2' to 'I:\sqldata\psi_db\Candidate_photo_File2.mdf',Move 'Candidate_Photo_File1' to 'I:\sqldata\psi_db\Candidate_Photo_File1.mdf',Move 'Candidate_Status_File2' to 'I:\sqldata\psi_db\Candidate_Status_File2.mdf',Move 'Candidate_Status_File1' to 'I:\sqldata\psi_db\Candidate_Status_File1.mdf',Move 'CandidateSiteDat' to 'I:\sqldata\psi_db\CandidateSiteDat.mdf',Move 'PracticeTestDat' to 'I:\sqldata\psi_db\PracticeTestDat.mdf',Move 'psi_db_licensedata' to 'I:\sqldata\psi_db\psi_db_licensedata.mdf',Move 'PSI_DB_HISTORYDATA' to 'I:\sqldata\psi_db\PSI_DB_HISTORYDATA.mdf',Move 'PSI_DB_ANALYSISDATA' to 'I:\sqldata\psi_db\PSI_DB_ANALYSISDATA.mdf',Move 'PSI_DB_OTHERDATA' to 'I:\sqldata\psi_db\PSI_DB_OTHERDATA.mdf',Move 'PSI_DB_ITEMDATA' to 'I:\sqldata\psi_db\PSI_DB_ITEMDATA.mdf',Move 'PSI_TestData_File2' to 'I:\sqldata\psi_db\PSI_TestData_File2.mdf',Move 'PSI_DB_TESTDATA' to 'I:\sqldata\psi_db\PSI_DB_TESTDATA.mdf',Move 'PSI_DB_Index_File2' to 'I:\sqldata\psi_db\PSI_DB_Index_File2.mdf',Move 'PSI_DB_INDEX2' to 'I:\sqldata\psi_db\PSI_DB_INDEX2.mdf',Move 'PSI_DB_INDEX1' to 'I:\sqldata\psi_db\PSI_DB_INDEX1.mdf',Move 'PSI_DB_LOOKUPDATA' to 'I:\sqldata\psi_db\PSI_DB_LOOKUPDATA.mdf',Move 'PSI_DB_CD_File2' to 'I:\sqldata\psi_db\PSI_DB_CD_File2.mdf',Move 'PSI_DB_CANDIDATEDATA' to 'I:\sqldata\psi_db\PSI_DB_CANDIDATEDATA.mdf',Move 'PSI_DB_ACCOUNTDATA_FILE3' to 'I:\sqldata\psi_db\PSI_DB_ACCOUNTDATA_FILE3.mdf',Move 'PSI_DB_ACCOUNTDATA_FILE2' to 'I:\sqldata\psi_db\PSI_DB_ACCOUNTDATA_FILE2.mdf',Move 'PSI_DB_ACCOUNTDATA' to 'I:\sqldata\psi_db\PSI_DB_ACCOUNTDATA.mdf',Move 'PSI_DB_DATA_File2' to 'I:\sqldata\psi_db\PSI_DB_DATA_File2.mdf',Move 'PSI_DB_Data' to 'I:\sqldata\psi_db\PSI_DB_Data.mdf', replace,
     standby='\\sdnetapp1\web_app_logs\SQLDBBackups\SDPROD-DB1\psi_dbpsi_dbundo_full.bak'

     

     

    --Code ends here

     

    I am using with move and replace command.

     

    Please let me know. Thanks a million.

     

    Regards

    Sachin

     

    Friday, February 15, 2008 10:09 AM
  • Can you please cross check if the data and log file location (I mean to say the drive and folder structure) are same  or not on both the server.If the drive and folder structure are different for both data and log files then you will not be able to restore the backup from Server1 to Server2.

    In log shipping this the common issue.If the folder structure are not same on both the servers then you need to restore manually by giving the exact path and filegroup description.


    Please click "Propose As Answer" if a post solves your problem. OR "Vote As Helpful" if a post has been useful to you. Thanks, Debasish Mohanty Blog:http://sqlcodebank.blogspot.com

    Wednesday, April 25, 2012 1:07 PM