renaming db's in dmo

  • 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

  • This was removed by the editor as SPAM

  • 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

  • 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

    http://www.sqlservercentral.com/columnists/awarren/

  • If I understand you right then take a look at this thread and the code I posted.

    http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=4238&FORUM_ID=22&CAT_ID=2&Topic_Title=Restore%20database%20with%20new%20name%20using%20SQLDMO&Forum_Title=DMO

    At least for the part of restoring to the same server but a different DBname and leaving the old intact.

  • 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

    http://www.sqlservercentral.com/columnists/awarren/

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

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