October 11, 2001 at 8:09 pm
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"
October 11, 2001 at 8:17 pm
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
October 12, 2001 at 1:23 am
text1.text contains the filename of the backup file to restore from.
yep. this is to hide complexity from the user.
October 12, 2001 at 6:48 am
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
October 12, 2001 at 9:33 pm
yup! tried it.. same error..
i changed
oRestore.Files = Text1.Text
to
oRestore.Files = "d:\jantzenbackup.bak"
October 12, 2001 at 9:36 pm
btw, jantzenbackup.bak is a file i manually created using backup of sql server.
October 12, 2001 at 11:03 pm
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.
October 15, 2001 at 6:20 am
Is the database restore corrupted if you restore through Enterprise Manager?
Patrick Birch
Quand on parle du loup, on en voit la queue
October 16, 2001 at 1:48 am
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.
October 16, 2001 at 6:36 am
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
October 16, 2001 at 10:08 am
October 16, 2001 at 8:51 pm
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."
October 17, 2001 at 7:04 am
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 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply