November 11, 2002 at 3:41 pm
I was wondering if anyone had a suggestion or code for restoring db's on a server with a new name.
So for example: I want to back-up TEST_DB on server1 to server1, leaving
TEST_DB intact, and renaming and restoring it as TEST_DB_RMICHEL.
or: I want to back up TEST_DB from server1, then restore it with the new
name TEST_DB_AWARREN on server2.
any help would be great! robbmichel@hotmail.com
November 14, 2002 at 8:00 am
This was removed by the editor as SPAM
November 14, 2002 at 11:38 am
The code that i have for the problem I stated, is below. Hopefully it may offer a better perspective. My task is basically:I want to backup a DB and restore it with a new name on the same or another server. As you can see I based my code on Andy Warren's examples.
code:
Private Sub Command2_Click()
Dim oServer As SQLDmo.SQLServer
Dim oDatabase As SQLDmo.Database
Dim obackup As SQLDmo.Backup
Dim orestore As SQLDmo.Restore
Dim sBAKFilePath
sBAKFilePath = "C:\Backup"
'we need a backup object in addition to the sqlserver one
Set oServer = New SQLDmo.SQLServer
Set obackup = New SQLDmo.Backup
oServer.LoginSecure = True
oServer.Connect Combo1.Text
'this will do a full backup of every database except TempDB to a file (not a
'device
For Each oDatabase In oServer.Databases
'list 1 lists the DB's on the selected server
If oDatabase.Name = List1.Text Then
obackup.Database = oDatabase.Name
'remove any previous backup - same as using T-SQL with init
obackup.Initialize = True
'dynamically create the name of the backup file
'oBackup.Files = sBAKFilePath & "\" & oDatabase.Name & ".bak"
obackup.Files = "\\MyServerName\backups" & "\" & oDatabase.Name & "_test4ROBB" & ".bak"
'set the action property as needed
obackup.Action = 0
obackup.SQLBackup oServer
End If
Next
Set obackup = Nothing
'----------------------------------------
'restore statement
Set orestore = New SQLDmo.Restore
'Property
With orestore
.Database = "TEST_robb"
.Action = SQLDMORestore_Database
'.ReplaceDatabase = True
.Files = "\\MyServerName\backups" & "\" & oDatabase.Name & "_test4ROBB" & ".bak"
.SQLRestore oServer
End With
'----------------------------------------
'clean up
'Set obackup = Nothing
oServer.DisConnect
Set oServer = Nothing
\---------------------------------------------------------
End Sub
November 14, 2002 at 3:47 pm
This looks close, I think you'd have to rename the data/log files to get it to work (unless you did replace). I'll work on it later tonight and post some code.
Andy
November 14, 2002 at 4:01 pm
If I understand you right then take a look at this thread and the code I posted.
At least for the part of restoring to the same server but a different DBname and leaving the old intact.
November 14, 2002 at 4:24 pm
Cool, had forgotten you posted that! Restoring to another server is just a bit more complicated, have to have the files accessible and you may still need to do the relocate if the folder structure is different and/or you have a file name conflict.
Andy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply