Thursday, August 4, 2011

Create Backup file for Sql server database

The easy VB.NET function used to take backup of sql server database.

'Reference added

Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common

'Declare the backup object

Dim WithEvents oBackup As New Backup

'Method to take backup

Private Sub BackupSqlDatabase()

'set SQL server connection given the server name, user name and password

Dim conn As New ServerConnection(serverName, userName, passWd)

'create the SMO server object using connection

Dim oSQLServer As New Server(conn)

'set the path where backup file will be stored

Dim OrigBackupPath As String = oSQLServer.Information.MasterDBPath.Replace("\DATA", "\Backup\DB_BACKUP_NAME.BAK")

'create SMO.Backupdevice object

Dim bkDevItem As New BackupDeviceItem(OrigBackupPath, DeviceType.File)

' Set the backup object property

With oBackup
.Action = BackupActionType.Database
.Initialize = True
.Checksum = True
.ContinueAfterError = True
.Incremental = False
.LogTruncation = BackupTruncateLogType.Truncate

'backup SQL database

End With

End Sub

Your comment will be appreciated.