>> sqldmo restore

Hi, I am using VBA in an Access 2003 UI to restore a database in SQLExpress
2005.
The following code snipet fails on the line oSQLServer.ExecuteImmediate with
error -2147199229... The backup set holds a backup of a database other than
the existing 'MATTestdata' database.

The .bak file is a backup of another database MATdata and I am restoring
into a database named MATTestdata.

'Create a backup device
Dim oBackupDevice As New SQLDMO.BackupDevice
oBackupDevice.Name = NewDatabase & "Restore"
oBackupDevice.Type = SQLDMODevice_DiskDump
oBackupDevice.PhysicalLocation = backupFile
oSQLServer.BackupDevices.Add oBackupDevice

'Restore source database to new database.
Dim cmdText As String
Const SQLDMOExec_ContinueOnError As Byte = 2

cmdText = "USE MASTER " & vbNewLine & _
"RESTORE FILELISTONLY " & vbNewLine & _
"FROM " & oBackupDevice.Name & "; " & vbNewLine & _
"RESTORE DATABASE " & NewDatabase & vbNewLine & _
"FROM " & oBackupDevice.Name & vbNewLine & _
"WITH MOVE '" & SourceDatabase & "_Data' TO '" & DbPath &
NewDatabase & ".mdf'," & vbNewLine & _
"Move '" & SourceDatabase & "_Log' TO '" & DbPath & NewDatabase
& ".ldf'"

oSQLServer.ExecuteImmediate cmdText, SQLDMOExec_ContinueOnError

Problem is then, how to restore a backup of one database to another database?

Any ideas or suggestions appreciated

Many thanks,
Jonathan

hi,
Jonathan wrote:
> Hi, I am using VBA in an Access 2003 UI to restore a database in
> SQLExpress 2005.
> The following code snipet fails on the line
> oSQLServer.ExecuteImmediate with error -2147199229... The backup set
> holds a backup of a database other than the existing 'MATTestdata'
> database.
>
> The .bak file is a backup of another database MATdata and I am
> restoring into a database named MATTestdata.
>
> 'Create a backup device
> Dim oBackupDevice As New SQLDMO.BackupDevice
> oBackupDevice.Name = NewDatabase & "Restore"
> oBackupDevice.Type = SQLDMODevice_DiskDump
> oBackupDevice.PhysicalLocation = backupFile
> oSQLServer.BackupDevices.Add oBackupDevice
>
> 'Restore source database to new database.
> Dim cmdText As String
> Const SQLDMOExec_ContinueOnError As Byte = 2
>
> cmdText = "USE MASTER " & vbNewLine & _
> "RESTORE FILELISTONLY " & vbNewLine & _
> "FROM " & oBackupDevice.Name & "; " & vbNewLine & _
> "RESTORE DATABASE " & NewDatabase & vbNewLine & _
> "FROM " & oBackupDevice.Name & vbNewLine & _
> "WITH MOVE '" & SourceDatabase & "_Data' TO '" & DbPath &
> NewDatabase & ".mdf'," & vbNewLine & _
> "Move '" & SourceDatabase & "_Log' TO '" & DbPath &
> NewDatabase & ".ldf'"
>
> oSQLServer.ExecuteImmediate cmdText, SQLDMOExec_ContinueOnError
>
> Problem is then, how to restore a backup of one database to another
> database?
>
> Any ideas or suggestions appreciated

try adding REPLACE as long as MOVE..
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://www.hotelsole.com
DbaMgr2k ver 0.21.1 - DbaMgr ver 0.65.1 and further SQL Tools
--------- remove DMO to reply