Restore database with new name using SQLDMO
-
I'm developing Restore database with VB & SqlDmo for someone who does not know TSQL. Everything is working fine as soon as I'm using the same database name but If I try to restore Northwind database from Northwind.bac as Northwind2 ( With new name) Using FILERELOCATE then I get an error telling me to use RESTORE WITH FILELISTONLY. This command is recognized only by TSQL. Can someone help me in this issue?
-
You state FILERELOCATE , do you mean RelocateFiles?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
Yes I mean Relocatefiles. Sorry
-
The problem you are running into is you must include the logical file names of the original files and the path of the new files to get restore to work.
Ex. I have a db FCDB with 2 files
The logical files names are FCDB_data and FCDB_log so I must do
.RelocateFiles = "[FCDB_Data],[D:\Test.mdf]" _
+ ",[FCDB_Log],[D:\testlog.ldf]"
Tp get to build into a new database. However I haven't figured out exactly how to get the information from the file which should be possible but SQLDMO is very limited in help and good examples. If I figure it out or come across I will let you know, or maybe someoen else here will have already done.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
Never mind I figured it out.
The key is in the Restore Object ReadFileList method.
Thsi Ex. Worked for me, based on Andy Warrens code except added ReadFileList
Hope it helps.
--------------------------Code
Private Sub Form_Load()
Call RestoreDBFromFile("NOTTELLING", "NEWDB", "d:\olddbbackup.bak")
End Sub
Sub RestoreDBFromFile(ServerName As String, DBName As String, BackupToRestore As String)
Dim oServer As SQLDMO.SQLServer
Dim oRestore As SQLDMO.Restore
Dim qRest As QueryResults
Dim LDNames(1) As String
On Error GoTo Handler
'simple err checking
If ServerName = "" Or DBName = "" Or BackupToRestore = "" Then
MsgBox "You MUST provide server name, database name, and the name of the bak file you want to restore", vbInformation + vbOKOnly, "Error"
Exit Sub
End If
'open connection to server
Set oServer = New SQLDMO.SQLServer
With oServer
.LoginSecure = True
.Connect ServerName
End With
'''''''''''''''''''''''Testing
Dim objDevice As SQLDMO.BackupDevice
Dim iCount As Integer
Dim xCount As Integer
'also need a restore object
Set oRestore = New SQLDMO.Restore
'use the 'with' construct to minimize property lookups
With oRestore
'this is where your backup will be restored to
.Database = DBName
'same as EM or TSQL, you can restore database, file, or log, here we're going to
'use database
.Action = SQLDMORestore_Database
'this is the "force restore over existing database" option
.ReplaceDatabase = True
'this does a restore from a file instead of a device - note that we're still
'restoring a database, NOT a file group
.Files = BackupToRestore
'Try this too.
Set qRest = .ReadFileList(oServer)
LDNames(0) = qRest.GetColumnString(1, 1)
LDNames(1) = qRest.GetColumnString(2, 1)
'Try this
.RelocateFiles = "[" & LDNames(0) & "],[D:\Test.mdf]" _
+ ",[" & LDNames(1) & "],[D:\testlog.ldf]"
'do it
.SQLRestore oServer
End With
'standard clean up
Set oRestore = Nothing
oServer.DisConnect
Set oServer = Nothing
Exit Sub
Handler:
If MsgBox(Err.Description & ". Would you like to continue?", vbInformation + vbYesNo) = vbYes Then
Resume Next
End If
End Sub
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
quote:
Never mind I figured it out.The key is in the Restore Object ReadFileList method.
Thsi Ex. Worked for me, based on Andy Warrens code except added ReadFileList
Hope it helps.
--------------------------Code
Private Sub Form_Load()
Call RestoreDBFromFile("NOTTELLING", "NEWDB", "d:\olddbbackup.bak")
End Sub
Sub RestoreDBFromFile(ServerName As String, DBName As String, BackupToRestore As String)
Dim oServer As SQLDMO.SQLServer
Dim oRestore As SQLDMO.Restore
Dim qRest As QueryResults
Dim LDNames(1) As String
On Error GoTo Handler
'simple err checking
If ServerName = "" Or DBName = "" Or BackupToRestore = "" Then
MsgBox "You MUST provide server name, database name, and the name of the bak file you want to restore", vbInformation + vbOKOnly, "Error"
Exit Sub
End If
'open connection to server
Set oServer = New SQLDMO.SQLServer
With oServer
.LoginSecure = True
.Connect ServerName
End With
'''''''''''''''''''''''Testing
Dim objDevice As SQLDMO.BackupDevice
Dim iCount As Integer
Dim xCount As Integer
'also need a restore object
Set oRestore = New SQLDMO.Restore
'use the 'with' construct to minimize property lookups
With oRestore
'this is where your backup will be restored to
.Database = DBName
'same as EM or TSQL, you can restore database, file, or log, here we're going to
'use database
.Action = SQLDMORestore_Database
'this is the "force restore over existing database" option
.ReplaceDatabase = True
'this does a restore from a file instead of a device - note that we're still
'restoring a database, NOT a file group
.Files = BackupToRestore
'Try this too.
Set qRest = .ReadFileList(oServer)
LDNames(0) = qRest.GetColumnString(1, 1)
LDNames(1) = qRest.GetColumnString(2, 1)
'Try this
.RelocateFiles = "[" & LDNames(0) & "],[D:\Test.mdf]" _
+ ",[" & LDNames(1) & "],[D:\testlog.ldf]"
'do it
.SQLRestore oServer
End With
'standard clean up
Set oRestore = Nothing
oServer.DisConnect
Set oServer = Nothing
Exit Sub
Handler:
If MsgBox(Err.Description & ". Would you like to continue?", vbInformation + vbYesNo) = vbYes Then
Resume Next
End If
End Sub
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Thanks for help.Your code is fine and restore is running fine.
-
Thanks for help.
Restore is running fine.
Aziz
-
hi
Thank you for help me
-
hello,
the code works fine when there are no spaces in the link:
"D:\test.bak" --> works fine
"D:\sql backups\database backup\test.bak" --> always error
error says: cannot open backup device 'D:\sql'.
so it seems he can't handle the white space, is there a possibility to
fix this problem
-
brielpotje (5/22/2008)
hello,the code works fine when there are no spaces in the link:
"D:\test.bak" --> works fine
"D:\sql backups\database backup\test.bak" --> always error
error says: cannot open backup device 'D:\sql'.
so it seems he can't handle the white space, is there a possibility to
fix this problem
Try using "[D:\sql backups\database backup\test.bak]"
Cheers...
-
Very useful ........ Thnak you very much
-
Thanx a lot. Very usefull..:)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply