sqldmo backup/restore problem

  • wsang

    Valued Member

    Points: 71

    I have the following source code to backup database:

    Dim oBackup As New SQLDMO.Backup

    Dim oSQLServer As New SQLServer

    oSQLServer.Connect "WENDY", "sa", ""

    oBackup.Action = SQLDMOBackup_Files

    oBackup.Database = "Jantzen"

    oBackup.DatabaseFiles = "Jantzen_Data"

    ' Example illustrates backup implemented to a single operating system

    ' file. A file naming convention could be easily applied allowing

    ' rapid identification of a specific backup.

    oBackup.Files = App.Path & "\Jantzen.bak"

    ' Call SQLBackup method to perform the backup. In a production

    ' environment, consider wrapping the method call with a wait pointer

    ' or use Backup object events to provide feedback to the user.

    '

    ' Note: Create and connect of SQLServer object used is not

    ' illustrated in this example.

    oBackup.SQLBackup oSQLServer

    However, if I try to restore the backup file created, there was an

    error.

    On the other hand, I have a restore function:

    ' frmDataEntry_Main.Hide

    ' frmBackup.Show

    Dim oRestore As New SQLDMO.Restore

    Dim oSQLServer As New SQLServer

    oSQLServer.Connect "WENDY", "sa", ""

    oRestore.Action = SQLDMORestore_Files

    oRestore.Database = "Jantzen"

    oRestore.ReplaceDatabase = True

    oRestore.Files = Text1.Text

    oRestore.SQLRestore oSQLServer

    it in turn, returns an error message: "You must specify filegroups or

    files for backup or restore"

  • Andy Warren

    SSC Guru

    Points: 119655

    I guess the first question is - do you have a requirement to backup the individual files and not the entire database? Is the value of text1.text = "Jantzen_Data"?

    I'll have to admit to not having tried the restore object before, I usually do restores manually when required, spend my time automating backups:-) I have an article or two posted on the site about using DMO that has some sample code, including how to do a full db backup.

    Why do you need to do a restore via code? Hiding complexity for the user?

    Andy

  • wsang

    Valued Member

    Points: 71

    text1.text contains the filename of the backup file to restore from.

    yep. this is to hide complexity from the user.

  • PBirch

    SSCrazy Eights

    Points: 8483

    I have just coded a Restore for the same reason (this actually runs in the backgound).

    Your code looks right, but it seems to not like the Text1.text. Just for a test, if you put a string value there does the restore work?

    Patrick Birch

    Quand on parle du loup, on en voit la queue

  • wsang

    Valued Member

    Points: 71

    yup! tried it.. same error..

    i changed

    oRestore.Files = Text1.Text

    to

    oRestore.Files = "d:\jantzenbackup.bak"

  • wsang

    Valued Member

    Points: 71

    btw, jantzenbackup.bak is a file i manually created using backup of sql server.

  • wsang

    Valued Member

    Points: 71

    i tried changing the restore code to include this:

    oRestore.DatabaseFiles = "Jantzen_Data"

    ------------------------------------------

    Dim oRestore As New SQLDMO.Restore

    Dim oSQLServer As New SQLServer

    oSQLServer.Connect "WENDY", "sa", ""

    oRestore.Action = SQLDMORestore_Files

    oRestore.Database = "Jantzen"

    oRestore.DatabaseFiles = "Jantzen_Data"

    oRestore.ReplaceDatabase = True

    oRestore.Files = Text1.Text

    oRestore.SQLRestore oSQLServer

    MsgBox "Restore Successful!"

    -----------------------------------------

    it doesnt flag an error anymore but the database becomes corrupted.. please help me.. i need to give the system on friday next week. and i'm stucked with restore/backup for 3 days now.

  • PBirch

    SSCrazy Eights

    Points: 8483

    Is the database restore corrupted if you restore through Enterprise Manager?

    Patrick Birch

    Quand on parle du loup, on en voit la queue

  • wsang

    Valued Member

    Points: 71

    if you use the backup file generated by the application i've coded, yes it's corrupted.. but if i do it manually, and then restore again, theres no problem.

  • PBirch

    SSCrazy Eights

    Points: 8483

    wsang,

    I'm not sure where your problem is. I think, because the code corrupts your database in the backup, you should first work through the backup part, to be sure that works. You could test by using EM to restore it. When you have that, then work on the restore side. Otherwise, you have no way to test if the restore is working properly through code.

    For the backup - there are many ways to do this through code. Your code looks fine. You don't have

    obackup.Initialize = True

    which controls the overwrite or append behavior of the backup.

    You also might try something besides App.Path, maybe using an XML or INI form to control the path.

    Just some thoughts,

    Patrick Birch

    Quand on parle du loup, on en voit la queue

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714668

    Nothing to add, but I think Patricks response is the best advice.

    Steve Jones

    steve@dkranch.net

  • wsang

    Valued Member

    Points: 71

    thanks patrick, i've done that and heres my code:

    Dim oBackup As New SQLDMO.Backup

    Dim oSQLServer As New SQLServer

    oSQLServer.Connect "WENDY", "sa", ""

    oBackup.Action = SQLDMOBackup_Files

    oBackup.Database = "Jantzen"

    oBackup.DatabaseFiles = "Jantzen_Data"

    ' Example illustrates backup implemented to a single operating system

    ' file. A file naming convention could be easily applied allowing

    ' rapid identification of a specific backup.

    oBackup.Files = Text1.Text

    oBackup.Initialize = True

    ' Call SQLBackup method to perform the backup. In a production

    ' environment, consider wrapping the method call with a wait pointer

    ' or use Backup object events to provide feedback to the user.

    '

    ' Note: Create and connect of SQLServer object used is not

    ' illustrated in this example.

    oBackup.SQLBackup oSQLServer

    MsgBox "Full Back Up successfully saved to " & Text1.Text & "!"

    but whenever i restore from the backup file generated by my app using enterprise manager,

    i get this error message:

    "cannot use backup set in file 'd:\jantzen_2\testbackup.bak' for this restore operation. Backup or restore operation terminating abnormally."

  • PBirch

    SSCrazy Eights

    Points: 8483

    wsang,

    Again, your code looks ok. What you might do is create another variable to capture the Text1.Text so that you can see what is really being passed, and then use that variable instead. This line seems to be the problem.

    You might also check into creating a restore object and performing a sqlverify on the backup. This would, at least, tell you if you have a readable and complete backup.

    Patrick Birch

    Quand on parle du loup, on en voit la queue

Viewing 13 posts - 1 through 13 (of 13 total)

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