Post reply

RESTORE: How do I obtain the Path of the MDF and Log files

  • February 15, 2009 at 6:17 pm

    #383941

    Hello

    I have an VB.NET application that uses an SQL database. I am about to publish a major upgrade with significant database changes. I am creating an application to upgrade the database. My plan is to do the following within the VB code: create a new database, backup the existing database, restore the backup to the new database, then execute the commands to make the database changes. I am coming unstuck on the Restore phase.

    When I try and restore I get the following error message for the MDF and Log files:

    The file 'J:\Customer Databases\Data\CV5_QSCU_2_Data.mdf' cannot be overwritten. It is being used by database 'C_51_QSCU_2'. File

    'CV5_QSCU_Data' cannot be restored to 'J:\Customer Databases\Data\CV5_QSCU_2_Data.mdf'. Use WITH MOVE to identify a valid location

    for the file.

    The application will be run on each customer's site so I can't pre-code the path for the MOVE as that will vary.

    Can anyone tell me how (if) I can obtain the Path for the files of a database.

    Regards

    James

  • February 15, 2009 at 10:25 pm

    #944335

    I have solved the problem (from the MSDN Forums) and provide the answer here for information:

    cmd.CommandText = "sp_helpdb [" & txtOldCatalog.Text & "]"

    Dim dr As SqlDataReader

    dr = cmd.ExecuteReader()

    If dr.Read() Then

    dr.NextResult()

    dr.Read()

    fN = dr("filename")

    End If

    Regards

    James

  • February 16, 2009 at 12:34 pm

    #944779

    select * from sysfiles

  • February 17, 2009 at 6:52 am

    #945149

    What is the command you're running within your VB code? In T-SQL, so long as the logical file names of both databases are the same the following syntax should work:

    Restore database from disk = 'path' with replace

  • February 17, 2009 at 7:28 am

    #945183

    Look also at RESTORE FILELISTONLY

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply