restoring a backupdevice on another machine

Question

restoring a backupdevice on another machine

Inquisitor Jax

SSC Enthusiast

Points: 193
More actions
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

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

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

Gordon Pollokoff SSCommitted Points: 1930 More actions · Answer 1

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

Inquisitor Jax SSC Enthusiast Points: 193 More actions · Answer 2

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

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

Inquisitor Jax SSC Enthusiast Points: 193 More actions · Answer 3

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

JonnoJ SSChasing Mays Points: 632 More actions · Answer 4

Here is a related topic:

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

Glenn Reynolds Newbie Points: 7 More actions · Answer 5

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.