Post reply

restoring a backupdevice on another machine

  • October 7, 2004 at 9:40 am

    #102232

    When restoring a backup device on another machine, I get error:

    "Device activation error. The physical file name....may not exist"

    I know this is because the file structure does not exist.

    Is there any way (VB.NET) to read the file locations for the .mdf/.ldf files

    which the backupdevice is going to use to restore the database,

    so that I may create the file structure if it doesn't exist??

    Please help!

    Jax

  • October 8, 2004 at 1:17 am

    #525889

    try executing xp_fileexist to see if the file exists.  Alternatively, you can select from syslogs to find out the expected locations of the .mdf/.ldf files.

    Gordon Pollokoff

    "Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones

  • October 8, 2004 at 2:17 am

    #525902

    Solution:

    First check if the backupdevice exists on the machine (which it shouldn't, because the restore is taking place on a machine other than the one that the backup was done on.)

    oBackupDeviceCheck = CreateObject("sqldmo.BackupDevice")  

    For Each oBackupDeviceCheck In oServer.BackupDevices

        If oBackupDeviceCheck.Name = BackupFileName Then

            blnBackupDeviceExists = True

            Exit For

        End If

    Next

    If the backupdevice does not exist, create it...

      If Not blnBackupDeviceExists Then

      oBackupDevice = CreateObject("sqldmo.BackupDevice") 

         With oBackupDevice

            .Name = BackupFileName

            .Type = 2

            'add the physical location of the device (file device)

            .PhysicalLocation = "C:\Data\BackupFileName.zzz"

         End With

         oServer.BackupDevices.Add(oBackupDevice)

      End If

    Finally, create the SQLRestore object, and get the physical file name locations of the .mdb/.ldf files from the QueryResults object returned by the .ReadFileList method of the restore object:

    Try

      oRestoreDB = CreateObject("sqldmo.Restore")    'New sqldmo.Restore

      With oRestoreDB

         .devices() = strDeviceNameToRestore

         .database = DBName

         .medianame = BackupFileName

         .ReplaceDatabase = True

         QueryResults = .ReadFileList(oServer)

         strDBRestorePath = QueryResults.GetColumnString(1, 2)

         'create the directory tree...

         oFile = New clsFile

         If oFile.CreateDirectoryTree(strDBRestorePath) Then

           .sqlverify(oServer)

           .SQLRestore(oServer)

         Else

           Return False

           Exit Function

         End If

      End With

      SQLRestore = True

    Catch ex As Exception

       MsgBox("Error Restoring database: " & ex.Message)

       SQLRestore = False

    End Try

    Regards,

    Jax

  • October 8, 2004 at 2:31 am

    #525905

    uggg, great.

    Now I get the following error: (only on one DB though - the rest works - thankfully)

    "Error restoring database: [SQL-DMO] This cache contains no result sets, or the current result set contains no rows"

    Any ideas what could be causing this?

    Maybe QueryResults is empty?

     

    Jax

  • February 28, 2007 at 4:28 am

    #692263

    Here is a related topic:

    http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=22&messageid=223817

  • March 8, 2007 at 7:48 pm

    #693981

    Hellom I had the same problem this morning.  I found that if you copy the backup to the local machine that you are trying to restore it on it will work.

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

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