TiltingCode
Programmer
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):
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.
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.