SQL Server Performance

Database restoration error

Discussion in 'General DBA Questions' started by sachinsamuel, Feb 14, 2008.

  1. sachinsamuel New Member

    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:sqldatapsi_dbPSI_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:sqldatapsi_dbPSI_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:sqldatapsi_dbPSI_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:sqldatapsi_dbPSI_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:sqldatapsi_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
  2. satya Moderator

    Is the database used during the restore time?
    The error indicates that SQL Server does not allow you to attach a single file of a database that contains multiple files. The attach process looks for all files that belong to the database during the attach and if it cannot find the files with the corresponding GUID, it fails the attach.
  3. sachinsamuel New Member

    Thanks satya,
    I am not attaching the database or any file. I am restoring the entire database on a standby mode. Below is the code which I am using.
    ---------------- Code starts
    restore database psi_db from disk = '\sdnetapp1web_app_logsSQLDBBackupsSDPROD-DB1psi_dbSET-6-0_psi_db_FULL_200802140255.bak' with Move 'PSI_DB_Log3' to 'I:sqldatapsi_dbPSI_DB_Log3.ldf',Move 'PSI_DB_Log2' to 'I:sqldatapsi_dbPSI_DB_Log2.ldf',Move 'PSI_DB_Log1' to 'I:sqldatapsi_dbPSI_DB_Log1.ldf',Move 'TCCFI_FG_File4' to 'I:sqldatapsi_dbTCCFI_FG_File4.mdf',Move 'TCCFI_FG_File3' to 'I:sqldatapsi_dbTCCFI_FG_File3.mdf',Move 'TCCFI_FG_File2' to 'I:sqldatapsi_dbTCCFI_FG_File2.mdf',Move 'TCCFI_FG_File1' to 'I:sqldatapsi_dbTCCFI_FG_File1.mdf',Move 'CAAV_FG_File3' to 'I:sqldatapsi_dbCAAV_FG_File3.mdf',Move 'CAAV_FG_File2' to 'I:sqldatapsi_dbCAAV_FG_File2.mdf',Move 'CAAV_FG_File1' to 'I:sqldatapsi_dbCAAV_FG_File1.mdf',Move 'CTIO_FG_File2' to 'I:sqldatapsi_dbCTIO_FG_File2.mdf',Move 'CTIO_FG_File1' to 'I:sqldatapsi_dbCTIO_FG_File1.mdf',Move 'IA_FG_File3' to 'I:sqldatapsi_dbIA_FG_File3.mdf',Move 'IA_FG_File2' to 'I:sqldatapsi_dbIA_FG_File2.mdf',Move 'IA_FG_File1' to 'I:sqldatapsi_dbIA_FG_File1.mdf',Move 'TestCenter_FG_File2' to 'I:sqldatapsi_dbTestCenter_FG_File2.mdf',Move 'TestCenter_FG_File1' to 'I:sqldatapsi_dbTestCenter_FG_File1.mdf',Move 'CTA_FG_File4' to 'I:sqldatapsi_dbCTA_FG_File4.mdf',Move 'CTA_FG_File3' to 'I:sqldatapsi_dbCTA_FG_File3.mdf',Move 'CTA_FG_File2' to 'I:sqldatapsi_dbCTA_FG_File2.mdf',Move 'CTA_FG_File1' to 'I:sqldatapsi_dbCTA_FG_File1.mdf',Move 'TestCenterDS_FG_File2' to 'I:sqldatapsi_dbTestCenterDS_FG_File2.mdf',Move 'TestCenterDS_FG_File1' to 'I:sqldatapsi_dbTestCenterDS_FG_File1.mdf',Move 'CTTS_FG_File2' to 'I:sqldatapsi_dbCTTS_FG_File2.mdf',Move 'CTTS_FG_File1' to 'I:sqldatapsi_dbCTTS_FG_File1.mdf',Move 'CTIR_FG_File4' to 'I:sqldatapsi_dbCTIR_FG_File4.mdf',Move 'CTIR_FG_File3' to 'I:sqldatapsi_dbCTIR_FG_File3.mdf',Move 'CTIR_FG_File2' to 'I:sqldatapsi_dbCTIR_FG_File2.mdf',Move 'CTIR_FG_File1' to 'I:sqldatapsi_dbCTIR_FG_File1.mdf',Move 'LOG_FILEGROUP_File4' to 'I:sqldatapsi_dbLOG_FILEGROUP_File4.mdf',Move 'LOG_FILEGROUP_File3' to 'I:sqldatapsi_dbLOG_FILEGROUP_File3.mdf',Move 'LOG_FILEGROUP_File2' to 'I:sqldatapsi_dbLOG_FILEGROUP_File2.mdf',Move 'LOG_FILEGROUP_File1' to 'I:sqldatapsi_dbLOG_FILEGROUP_File1.mdf',Move 'Candidate_Payments_FG_File4' to 'I:sqldatapsi_dbCandidate_Payments_FG_File4.mdf',Move 'Candidate_Payments_FG_File3' to 'I:sqldatapsi_dbCandidate_Payments_FG_File3.mdf',Move 'Candidate_Payments_FG_File2' to 'I:sqldatapsi_dbCandidate_Payments_FG_File2.mdf',Move 'Candidate_Payments_FG_File1' to 'I:sqldatapsi_dbCandidate_Payments_FG_File1.mdf',Move 'Candidate_Scores_FG_File4' to 'I:sqldatapsi_dbCandidate_Scores_FG_File4.mdf',Move 'Candidate_Scores_FG_File3' to 'I:sqldatapsi_dbCandidate_Scores_FG_File3.mdf',Move 'Candidate_Scores_FG_File2' to 'I:sqldatapsi_dbCandidate_Scores_FG_File2.mdf',Move 'Candidate_Scores_FG_File1' to 'I:sqldatapsi_dbCandidate_Scores_FG_File1.mdf',Move 'Test_Regn_FG_File4' to 'I:sqldatapsi_dbTest_Regn_FG_File4.mdf',Move 'Test_Regn_FG_File3' to 'I:sqldatapsi_dbTest_Regn_FG_File3.mdf',Move 'Test_Regn_FG_File2' to 'I:sqldatapsi_dbTest_Regn_FG_File2.mdf',Move 'Test_Regn_FG_File1' to 'I:sqldatapsi_dbTest_Regn_FG_File1.mdf',Move 'Candidate_photo_File3' to 'I:sqldatapsi_dbCandidate_photo_File3.mdf',Move 'Candidate_photo_File2' to 'I:sqldatapsi_dbCandidate_photo_File2.mdf',Move 'Candidate_Photo_File1' to 'I:sqldatapsi_dbCandidate_Photo_File1.mdf',Move 'Candidate_Status_File2' to 'I:sqldatapsi_dbCandidate_Status_File2.mdf',Move 'Candidate_Status_File1' to 'I:sqldatapsi_dbCandidate_Status_File1.mdf',Move 'CandidateSiteDat' to 'I:sqldatapsi_dbCandidateSiteDat.mdf',Move 'PracticeTestDat' to 'I:sqldatapsi_dbPracticeTestDat.mdf',Move 'psi_db_licensedata' to 'I:sqldatapsi_dbpsi_db_licensedata.mdf',Move 'PSI_DB_HISTORYDATA' to 'I:sqldatapsi_dbPSI_DB_HISTORYDATA.mdf',Move 'PSI_DB_ANALYSISDATA' to 'I:sqldatapsi_dbPSI_DB_ANALYSISDATA.mdf',Move 'PSI_DB_OTHERDATA' to 'I:sqldatapsi_dbPSI_DB_OTHERDATA.mdf',Move 'PSI_DB_ITEMDATA' to 'I:sqldatapsi_dbPSI_DB_ITEMDATA.mdf',Move 'PSI_TestData_File2' to 'I:sqldatapsi_dbPSI_TestData_File2.mdf',Move 'PSI_DB_TESTDATA' to 'I:sqldatapsi_dbPSI_DB_TESTDATA.mdf',Move 'PSI_DB_Index_File2' to 'I:sqldatapsi_dbPSI_DB_Index_File2.mdf',Move 'PSI_DB_INDEX2' to 'I:sqldatapsi_dbPSI_DB_INDEX2.mdf',Move 'PSI_DB_INDEX1' to 'I:sqldatapsi_dbPSI_DB_INDEX1.mdf',Move 'PSI_DB_LOOKUPDATA' to 'I:sqldatapsi_dbPSI_DB_LOOKUPDATA.mdf',Move 'PSI_DB_CD_File2' to 'I:sqldatapsi_dbPSI_DB_CD_File2.mdf',Move 'PSI_DB_CANDIDATEDATA' to 'I:sqldatapsi_dbPSI_DB_CANDIDATEDATA.mdf',Move 'PSI_DB_ACCOUNTDATA_FILE3' to 'I:sqldatapsi_dbPSI_DB_ACCOUNTDATA_FILE3.mdf',Move 'PSI_DB_ACCOUNTDATA_FILE2' to 'I:sqldatapsi_dbPSI_DB_ACCOUNTDATA_FILE2.mdf',Move 'PSI_DB_ACCOUNTDATA' to 'I:sqldatapsi_dbPSI_DB_ACCOUNTDATA.mdf',Move 'PSI_DB_DATA_File2' to 'I:sqldatapsi_dbPSI_DB_DATA_File2.mdf',Move 'PSI_DB_Data' to 'I:sqldatapsi_dbPSI_DB_Data.mdf', replace,
    standby='\sdnetapp1web_app_logsSQLDBBackupsSDPROD-DB1psi_dbpsi_dbundo_full.bak'
    ---------------- Code ends
    Moreover the path 'I:sqldatapsi_db' exists. The server is running on a domain user name so that I can access lan folders.
    Please help. Its little urgent. Thanks a million.
    Regards
    Sachin

Share This Page