>> sqldmo restore

[Jonathan]

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

[Andrea Montanari]

hi,

Post by Jonathan
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..

[Jonathan]

Ahh, that's it. Thanks.

Jonathan

Post by Andrea Montanari
hi,

Post by Jonathan
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