Minggu, 29 Maret 2015

membuat backup restore database sql server




Salah satu pertanyaan yang sering diajukan adalah bagaimana cara melakukan backup dan restore database sql server menggunakan coding (vb.net). hal ini dilakukan untuk memudahkan user dalam penggunaan aplikasi tanpa harus "menyentuh" server. Langkah awal adalah membuat form seperti gambar di bawah ini :

Langkah selanjutnya adalah membuat Coding sebagai berikut :

Imports System.Data.SqlClient
'imports Microsoft.SqlServer

Public Classbackuprestore

    Dim con, con1 As SqlConnection
    Dim cmd As SqlCommand
    Dim dread As SqlDataReader
    Private Sub backuprestore_Load(ByValsender As System.Object, ByVal e AsSystem.EventArgs) Handles MyBase.Load
        server(".")
        server(".\sqlexpress")
    End Sub
    Sub server(ByVal str As String)
        con = NewSqlConnection("Data Source=" & str & ";Database=Master;integrated security=SSPI;")
        con.Open()
        cmd = NewSqlCommand("select *  from sysservers  where srvproduct='SQL Server'", con)
        dread = cmd.ExecuteReader
        Whiledread.Read
            cmbserver.Items.Add(dread(2))
        End While
        dread.Close()
    End Sub
    Subconnection()
        con = NewSqlConnection("Data Source=" & Trim(cmbserver.Text) & ";Database=Master;integrated security=SSPI;")
        con.Open()
        cmbdatabase.Items.Clear()
        cmd = NewSqlCommand("select * from sysdatabases", con)
        dread = cmd.ExecuteReader
        Whiledread.Read
            cmbdatabase.Items.Add(dread(0))
        End While
        dread.Close()
    End Sub

    Private Sub cmbserver_SelectedIndexChanged(ByVal sender AsSystem.Object, ByVal e AsSystem.EventArgs) Handlescmbserver.SelectedIndexChanged
        connection()
    End Sub
    Sub query(ByVal que As String)
        On Error Resume Next
        cmd = NewSqlCommand(que, con)
        cmd.ExecuteNonQuery()
    End Sub
    Private Sub Timer1_Tick(ByValsender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
        IfProgressBar1.Value = 100 Then
            Timer1.Enabled = False
            ProgressBar1.Visible = False
            MsgBox("Successfully Done")
        Else
            ProgressBar1.Value = ProgressBar1.Value + 5
        End If
    End Sub
    Sub blank(ByVal str As String)
        Ifcmbserver.Text = "" Or cmbdatabase.Text = ""Then
            MsgBox("Server Name & Database Blank Field")
            ExitSub
        Else
           
            Ifstr = "backup" Then
                SaveFileDialog1.FileName = CmbDatabase.Text
                SaveFileDialog1.ShowDialog()
                Timer1.Enabled = True
                ProgressBar1.Visible = True
                Dims As String
                s = SaveFileDialog1.FileName
                query("backup database " & CmbDatabase.Text & " to disk='" & s & "'")
            ElseIfstr = "restore" Then
                OpenFileDialog1.ShowDialog()
                Timer1.Enabled = True
                ProgressBar1.Visible = True
                query("RESTORE DATABASE " & CmbDatabase.Text & " FROM disk='" & OpenFileDialog1.FileName & "'")
            EndIf
        End If
    End Sub

    Private Sub cmbbackup_Click(ByValsender As System.Object, ByVal e AsSystem.EventArgs) Handles CmdBackup.Click
        blank("backup")
    End Sub

    Private Sub cmdrestore_Click(ByValsender As System.Object, ByVal e AsSystem.EventArgs) Handles cmdrestore.Click
        blank("restore")
    End Sub

End Class

Tidak ada komentar:

Posting Komentar