Back up sql server 2005 using SMO
Solution 1
You can use SMO Add these references:
Collapse | Copy Code
'//***** Add these references********** '//Microsoft.SqlServer.Smo '//Microsoft.SqlServer.SmoExtended '//Microsoft.SqlServer.Management.Sdk.Sfc '//Microsoft.SqlServer.ConnectionInfo
Add these Imports:
Collapse | Copy Code
Imports System.Data.SqlClient Imports Microsoft.SqlServer.Management.Smo Imports Microsoft.SqlServer.Management.Common
Then code something like this:
Collapse | Copy Code
Private Sub Backup Dim srv As Server Dim conn As ServerConnection Dim sqlSErverInstance As String = "YourServerInstanceName" Dim fileName As String = "MyBackup.bak" Dim databaseName As String = "MyDatabase" conn = New ServerConnection(sqlSErverInstance, "Username", "Password") srv = New Server(conn) Dim bkp As New Backup() Try
bkp.Action = BackupActionType.Database bkp.Database = databaseName bkp.Devices.AddDevice(fileName, DeviceType.File) bkp.Incremental = False
bkp.PercentCompleteNotification = 10 AddHandler bkp.PercentComplete, AddressOf ProgressEventHandler
bkp.SqlBackup(srv)
MessageBox.Show("Database Backed Up To: " & fileName, "Backup") Catch ex As Exception MessageBox.Show(ex.ToString()) Finally Me.ProgressBar1.Value = 0 End Try End Sub Public Sub ProgressEventHandler(sender As Object, e As PercentCompleteEventArgs) Me.ProgressBar1.Value = e.Percent End Sub
SOFTWARE TECHNOLOGY TIPS
Home Tips Database Backup SQL Database Through Code In VB.NET
Using the SQL SMO object we can easily take backup of SQL database through code. Following is the sample code used take backup.
[VB.NET CODE STARTS] '--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()
Dim conn As New ServerConnection(serverName, userName, passWd) '-set SQL server connection given the server name, user name and password Dim oSQLServer As New Server(conn) '--create the SMO server object using connection
Dim OrigBackupPath As String = oSQLServer.Information.MasterDBPath.Replace("\DATA", "\Backup\DB_BACKUP_NAME.BAK") ' -- set the path where backup file will be stored
Dim bkDevItem As New BackupDeviceItem(OrigBackupPath, DeviceType.File) ' -- create SMO.Backupdevice object
With
oBackup ' Set the backup object property .Action = BackupActionType.Database .Database = YOUR_DATABASE_NAME .Devices.Add(bkDevItem) .Initialize = True .Checksum = True .ContinueAfterError = True .Incremental = False .LogTruncation = BackupTruncateLogType.Truncate .SqlBackup(oSQLServer) ' backup SQL database
End With
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnbackup.Click With SaveFileDialog1 .InitialDirectory = "C:\" .FileName = "yourdatabasename " & Format(Now, "dd-MMMM-yyyy") .Filter = "BAK Files (*.bak)|*.bak" .RestoreDirectory = True If Not .ShowDialog = Windows.Forms.DialogResult.Cancel Then Try If Not Conn.State = ConnectionState.Open Then Conn.Open() End If Using cmd As New SqlCommand cmd.Connection = Conn cmd.CommandText = ("USE master BACKUP DATABASE yourdatabasename TO DISK='" & .FileName & "' WITH FORMAT") cmd.CommandType = CommandType.Text 'connection.Open() cmd.ExecuteNonQuery() Conn.Close() MsgBox("Data has been backup!", vbInformation + vbExclamation) End Using Catch ex As Exception MsgBox(ex.Message) End Try End If End With Yesterday at 12:55pm Unlike 1
Jhayar Bernabe Thanks bro.. but I want to ask if I need to add reference like.. sql.connection. info or .smo Yesterday at 12:58pm Like
Tonny Siauw here is the example .Filter = "txt files (*.txt)|*.txt|All files (*.*)|*.*" Yesterday at 1:05pm Edited Like
