Restore Database Fails

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Neil
    #1

    Restore Database Fails

    I'm trying to use ADO to restore a SQL 7 database using a backup file. The
    database already exists on the target computer, and is named the same as on
    the source computer, and the MDF and LDF files are named the same as well.

    I am running the below in a stored procedure:

    RESTORE DATABASE DB1
    FROM DISK = 'c:\mssql7\back up\DB1.bak'
    WITH MOVE 'DB1' TO 'c:\mssql7\data \DB1.mdf',
    MOVE 'DB1_log' TO 'c:\mssql7\data \DB1_log.ldf'

    And I get an error message "File 'DB1' is not a database file for database
    'DB1' ".

    I also tried it without the LDF file in the expression. Same results.

    Thanks.



    Tags: None
    • Erland Sommarskog
      #2
      Re: Restore Database Fails

      Neil (nospam@nospam. net) writes:
      I'm trying to use ADO to restore a SQL 7 database using a backup file.
      The database already exists on the target computer, and is named the
      same as on the source computer, and the MDF and LDF files are named the
      same as well.
      >
      I am running the below in a stored procedure:
      >
      RESTORE DATABASE DB1
      FROM DISK = 'c:\mssql7\back up\DB1.bak'
      WITH MOVE 'DB1' TO 'c:\mssql7\data \DB1.mdf',
      MOVE 'DB1_log' TO 'c:\mssql7\data \DB1_log.ldf'
      >
      And I get an error message "File 'DB1' is not a database file for database
      'DB1' ".
      Add ", REPLACE" to your command.


      --
      Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

      Books Online for SQL Server 2005 at
      http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

      Books Online for SQL Server 2000 at
      http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

        Comment

        • Neil
          #3
          Re: Restore Database Fails

          OK, replaced the original command with:

          RESTORE DATABASE DB1
          FROM DISK = 'c:\mssql7\back up\DB1.bak'
          WITH MOVE DB1 TO 'c:\mssql7\data \DB1.mdf',
          MOVE DB1_log' TO 'c:\mssql7\data \DB1_log.ldf',
          REPLACE

          Got the same error message.



          "Erland Sommarskog" <esquel@sommars kog.sewrote in message
          news:Xns9945EC5 3F9A4BYazorman@ 127.0.0.1...
          Neil (nospam@nospam. net) writes:
          >I'm trying to use ADO to restore a SQL 7 database using a backup file.
          >The database already exists on the target computer, and is named the
          >same as on the source computer, and the MDF and LDF files are named the
          >same as well.
          >>
          >I am running the below in a stored procedure:
          >>
          >RESTORE DATABASE DB1
          >FROM DISK = 'c:\mssql7\back up\DB1.bak'
          >WITH MOVE 'DB1' TO 'c:\mssql7\data \DB1.mdf',
          >MOVE 'DB1_log' TO 'c:\mssql7\data \DB1_log.ldf'
          >>
          >And I get an error message "File 'DB1' is not a database file for
          >database
          >'DB1' ".
          >
          Add ", REPLACE" to your command.
          >
          >
          --
          Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
          >
          Books Online for SQL Server 2005 at
          http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

          Books Online for SQL Server 2000 at
          http://www.microsoft.com/sql/prodinf...ons/books.mspx

            Comment

            • Neil
              #4
              Re: Restore Database Fails

              As a followup to my previous message, I also tried the modified command
              directly from Query Analyzer, and got the same message. I then tried to
              restore the backup file from Enterprise Manager, and it worked fine. But
              couldn't get the SQL to work.

              However, I still need to get it to work, as I have an associate who needs to
              restore the same backup set, but he doesn't have QA or EM. He's running the
              SQL in a stored procedure using ADO.

              Thanks,

              Neil


              "Erland Sommarskog" <esquel@sommars kog.sewrote in message
              news:Xns9945EC5 3F9A4BYazorman@ 127.0.0.1...
              Neil (nospam@nospam. net) writes:
              >I'm trying to use ADO to restore a SQL 7 database using a backup file.
              >The database already exists on the target computer, and is named the
              >same as on the source computer, and the MDF and LDF files are named the
              >same as well.
              >>
              >I am running the below in a stored procedure:
              >>
              >RESTORE DATABASE DB1
              >FROM DISK = 'c:\mssql7\back up\DB1.bak'
              >WITH MOVE 'DB1' TO 'c:\mssql7\data \DB1.mdf',
              >MOVE 'DB1_log' TO 'c:\mssql7\data \DB1_log.ldf'
              >>
              >And I get an error message "File 'DB1' is not a database file for
              >database
              >'DB1' ".
              >
              Add ", REPLACE" to your command.
              >
              >
              --
              Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
              >
              Books Online for SQL Server 2005 at
              http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

              Books Online for SQL Server 2000 at
              http://www.microsoft.com/sql/prodinf...ons/books.mspx

                Comment

                • Neil
                  #5
                  Re: Restore Database Fails

                  OK, I got it to work. Seems I was using MOVE to move the MDF and LDF to
                  those locations; but the MDF and LDF for the database that was being
                  overwritten were already at those locations. So I removed the MOVE commands,
                  and it worked fine. The final version was:

                  RESTORE DATABASE DB1
                  FROM DISK = 'c:\mssql7\back up\DB1.bak'
                  WITH RESTORE

                  Thanks!

                  Neil


                  "Erland Sommarskog" <esquel@sommars kog.sewrote in message
                  news:Xns9945EC5 3F9A4BYazorman@ 127.0.0.1...
                  Neil (nospam@nospam. net) writes:
                  >I'm trying to use ADO to restore a SQL 7 database using a backup file.
                  >The database already exists on the target computer, and is named the
                  >same as on the source computer, and the MDF and LDF files are named the
                  >same as well.
                  >>
                  >I am running the below in a stored procedure:
                  >>
                  >RESTORE DATABASE DB1
                  >FROM DISK = 'c:\mssql7\back up\DB1.bak'
                  >WITH MOVE 'DB1' TO 'c:\mssql7\data \DB1.mdf',
                  >MOVE 'DB1_log' TO 'c:\mssql7\data \DB1_log.ldf'
                  >>
                  >And I get an error message "File 'DB1' is not a database file for
                  >database
                  >'DB1' ".
                  >
                  Add ", REPLACE" to your command.
                  >
                  >
                  --
                  Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
                  >
                  Books Online for SQL Server 2005 at
                  http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

                  Books Online for SQL Server 2000 at
                  http://www.microsoft.com/sql/prodinf...ons/books.mspx

                    Comment

                    • Erland Sommarskog
                      #6
                      Re: Restore Database Fails

                      Neil (nospam@nospam. net) writes:
                      OK, replaced the original command with:
                      >
                      RESTORE DATABASE DB1
                      FROM DISK = 'c:\mssql7\back up\DB1.bak'
                      WITH MOVE DB1 TO 'c:\mssql7\data \DB1.mdf',
                      MOVE DB1_log' TO 'c:\mssql7\data \DB1_log.ldf',
                      REPLACE
                      >
                      Got the same error message.

                      The the problem is that the logical names of the files are not DB1 and
                      DB1_log. You can retrieve these names with RESTORE FILELISTONLY.


                      --
                      Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

                      Books Online for SQL Server 2005 at
                      http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

                      Books Online for SQL Server 2000 at
                      http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

                        Comment

                        Previous template Next
                        Working...