Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SMO Backup issue

Status
Not open for further replies.

TiltingCode

Programmer
Apr 10, 2009
61
0
0
US
Hi, I've been trying to use SMO in VB.Net 2010 to do a backup of a 2008 SQL Server DB. I tried this a while back could have sworn it worked the first time but not sure. Anyway, below is my code (I have to read the ini file due to an other legacy system we're using):

Code:
Private Function BackUpDB(strFileName As String) As Boolean

    Dim strPath As String = Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly.Location)
    Dim objiniFile As New iniFile(strPath & "\INFO.INI")

    objiniFile.PWD = objiniFile.PWD ' Decrypt password.
   
    Dim objConnection As New ServerConnection(objiniFile.Server, objiniFile.UID, objiniFile.PWD) ' Set SQL server connection given the server, user and password
    objConnection.DatabaseName = objiniFile.Database

    Dim objSQLServer As New Server(objConnection)
    Dim objBackup As New Backup()
    With objBackup ' Set the backup object property
      .Action = BackupActionType.Database
      '.BackupSetDescription = "Full backup of database."
      '.BackupSetName = "Backup done on " & DateTime.Now.ToString
      .Database = objConnection.DatabaseName

      'Declare a BackupDeviceItem by supplying the backup device file name in the constructor, and the type of device is a file.
      Dim bkDeviceItem As New BackupDeviceItem(strFileName, DeviceType.File)
      'Add the device to the Backup object.
      .Devices.Add(bkDeviceItem)
      .Initialize = True
      .Checksum = True
      .ContinueAfterError = True
      .Incremental = False ' Full database backup.
  
      Try
        .SqlBackup(objSQLServer) ' Backup SQL database
      Catch ex As FailedOperationException
       Debug.WriteLine("Message: " & ex.Message.ToString & " for user " & objiniFile.UID & Environment.NewLine & _
          "SmoExceptionType: " & ex.SmoExceptionType.ToString & Environment.NewLine & Environment.NewLine & _
          "StackTrace: " & ex.StackTrace.ToString)
      Catch ex As Exception
        MsgBox("The following unknown error occurred: " & Environment.NewLine & ex.ToString)
      End Try

    End With

    BackUpDB = True

  End Function

I've researched and have installed the SMO DLLs as well as either updated or verified I have the most current version(s) of:
Microsoft Core XML Services (MSXML) 6.0
Microsoft SQL Server Native Client
Microsoft SQL Server System CLR Types

The message I'm getting is the backup failed for Server 'PMCDANIEL-PC'. The Server name in SQL is 'PMCDANIEL-PC\MSSQL2K8'. I've tried both names in SMO and get the same error message. The StackTrace message says something about "StackTrace: at Microsoft.SqlServer.Management.Smo.Backup.SqlBackup(Server srv)
at BackupPIDB.PIABackup.BackUpDB(String strFileName) in C:\Users\pmcdaniel\documents\visual studio 2010\Projects\VB\Backup_PI_DB\Backup_PI_DB\PIBackup.vb:line 83".


The .BAK file is created every time with a file size of zero, but after that it crashes at the .SqlBackup(objSQLServer) line.

I have also gone straight to SQL Server and successfully backuped the database.

Looking forward to answers and thanks for taking the time to read my question.
 
I realized I was using the Stream class and was creating the file first which I believe is wrong. Anyway, I'm still having the same problem. I also found out the actual Server name but am still getting the same error.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top