Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Feb 2008
    Posts
    8

    Unanswered: Error Backing UP SQL Database Using SQLDMO and VB.NET

    Hi,

    I developed an application in Vb.net and SQLDMO that backs up a database and performs certain operations on it once this is done. I have set the backup to backup to file which the user selects. However when i execute the backup i get an error message that " Backup device not found or error finding device" .THis error baffles me because i have not specified the backup to be to device can anyone help?

  2. #2
    Join Date
    Nov 2003
    Location
    London
    Posts
    169
    Hi there,

    Please post the code so we can get an idea of what your issue is

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    "Backup device not found" most likely means that the backup device you have specified is not listed in SQL Server. Most commonly, this is a typo at one end or the other. Run
    Code:
    sp_helpdevice
    in Query Analyzer to see what devices you have defined on the SQL Server side.

  4. #4
    Join Date
    Feb 2008
    Posts
    8
    please see code below. I wish to backup to file but this has proved impossible even when i remove the device property and replce it with file.help!!!!

    Private Sub doBackup()
    bkp = New SQLDMO.Backup
    Dim oDevice As New SQLDMO.BackupDevice
    oDevice.Name = "RMS_Archive"
    oDevice.PhysicalLocation = Me.SaveFileDialog1.FileName
    server.Connect(Me.cmbServers.Text, Me.TxtLogIn.Text,me.txtpassword.Text)
    Dim devctr As New BackupDevice
    For Each devctr In server.BackupDevices
    If devctr.Name = oDevice.Name Then
    Exit For
    Else
    server.BackupDevices.Add(oDevice)
    End If
    Next

    With bkp

    .device=oDevice.Name
    .Database = cmbDb.Text
    .SQLBackup(server)
    End With
    MessageBox.Show("Backup Completed Sucessfully", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)

    end sub

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    If I am reading this right, the code is potentially trying to create the RMS_Archive device multiple times? What do you get when you run this in Query Anylyzer?
    Code:
    sp_helpdevice
    One thing about backup devices, you can think of it as a pointer to a file. Once that pointer is created, you don't need to re-create it for each backup.

  6. #6
    Join Date
    Feb 2008
    Posts
    8
    No, Im using that loop to check if it exists among the devices and if not it should add it.

  7. #7
    Join Date
    Nov 2003
    Location
    London
    Posts
    169
    Hi there,

    I dont know how to fix your SQLDMO problem but...

    Is there some specific reason you are using SQLDMO for this?

    I am wondering why you dont create a normal sql server backup job, then execute that from your VB.net application using 'sp_start_job'?

  8. #8
    Join Date
    Feb 2008
    Posts
    8
    because it the user who determines what database is to be backed up and where it is to be backed up to

  9. #9
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    So use two variables.

    declare @dbname sysname, @filelocation varchar(255)

    Backup database @dbname to disk = @filelocation

    -- This is all just a Figment of my Imagination --

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Suppose I have three backup devices on my server named masterBkup, msdbBkup, and userDBBkup, and I run this code with oDevice.Name = userDBBkup:
    Code:
    Dim devctr As New BackupDevice
    For Each devctr In server.BackupDevices
      If devctr.Name = oDevice.Name Then
        Exit For
      Else
        server.BackupDevices.Add(oDevice)
      End If
    Next
    The foreach loop sees masterBkup, and says "nope, not equal. Go to the Else statement", where it attempts to create a device called userDBBkup. But that already exists, so it tosses an error.

    On to the next item in the list we go. msdbBkup <> userDBBkup, so again, the code tries to create the userDBBkup backup device. Again, it errors out. Why? It already exists.

    On to the next. This one happens to be named userDBBkup, which equals oDevice.Name. Now it exits the FOR loop, as desired.

    Question. Do the first two errors get caught in any way? Or is this type of overhead standard in any application?

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Who are you asking?

    BTW - it shouldn't be a New BackupDevice since you are looping through a collection of existing devices (OP not you MCrowley).

  12. #12
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Is the For Each loop even entered, if the server.BackupDevices collection is empty? If not, there is the problem, right there.

  13. #13
    Join Date
    Feb 2008
    Posts
    8
    McCrowley you raise a good point . So how would u suggest i loop through the ackup devices to seeif the one i want to add exists?

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Personally I would do this with a boolean variable. Loop through and set it to true if ever it matches. After the loop check the boolean - if the loop never initiated (there are no devices) it will be false. If the device does not exist it will be false. There are other ways by trapping for errors but they are not very elegent and arguably poor practice.

  15. #15
    Join Date
    Feb 2008
    Posts
    8
    this code worked. Thanks guys ooh and this procedure is called within a try/catch block so (Mccrowley) yes i have included error detection.


    bkp = New SQLDMO.Backup2

    Dim oDevice As New SQLDMO.BackupDevice
    oDevice.Name = Me.txtDeviceName.Text
    oDevice.PhysicalLocation = Me.txtDestination.Text
    oDevice.Type = SQLDMO_DEVICE_TYPE.SQLDMODevice_DiskDump
    server.Connect(Me.cmbServers.Text, Me.TxtLogIn.Text, Me.txtPassword.Text)
    Dim devctr As BackupDevice
    If server.BackupDevices.Count > 0 Then
    For Each devctr In server.BackupDevices
    If devctr.Name = oDevice.Name Then
    Exit For
    Else
    server.BackupDevices.Add(oDevice)
    Exit For
    End If
    Next
    Else
    server.BackupDevices.Add(oDevice)
    End If
    With bkp


    .Devices = oDevice.Name
    .Database = cmbDb.Text
    .BackupSetName = "MyRMS_ArchiveBkp1"
    .BackupSetDescription = "RMS Backup1"

    .SQLBackup(server)
    End With
    MessageBox.Show("Backup Completed Sucessfully", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •