Click here to Skip to main content
Click here to Skip to main content
Add your own
alternative version

SQL Server Database Backup Utility using VB.NET and SQL-DMO (New version)

, 17 Mar 2008 CPOL
A Windows application to backup and restore SQL server tables,views,user defined functions and stored procedures
'   Database backup utility:
'   ========================
'   Copyright (C) 2007  Shabdar Ghata (Email : ghata2002@gmail.com)

'   This program is free software: you can redistribute it and/or modify
'   it under the terms of the GNU General Public License as published by
'   the Free Software Foundation, either version 3 of the License, or
'   (at your option) any later version.

'   This program is distributed in the hope that it will be useful,
'   but WITHOUT ANY WARRANTY; without even the implied warranty of
'   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
'   GNU General Public License for more details.

'   You should have received a copy of the GNU General Public License
'   along with this program.  If not, see <http://www.gnu.org/licenses/>.

'   This program comes with ABSOLUTELY NO WARRANTY.

Imports System.Data
Imports System.Data.OleDb

Public Class frmGenerateScripts
    Dim OledbConnectionString As String

    Private Sub frmBackupDatabase_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Load_Default_Values()
    End Sub

    Private Sub cmdExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdGenerateScripts.Click
        Dim file As System.IO.StreamWriter
        file = My.Computer.FileSystem.OpenTextFileWriter("c:\SQLBackup.bat", False)
        If Not IsNothing(dgExportTables) Then
            Dim DT As DataTable = CType(dgExportTables.DataSource, DataTable)
            Dim i As Integer
            For i = 0 To DT.Rows.Count - 1
                Dim sBCPCommand As String = Generate_BCPCommand(txtServerName.Text, txtDatabaseName.Text, txtUsername.Text, txtPassword.Text, CStr(DT.Rows(i)("TABLENAME")), "", txtNoOfRows.Text)
                DT.Rows(i)("BCPCommand") = sBCPCommand
                file.WriteLine(sBCPCommand)
            Next
        End If
        file.Close()
        'MessageBox.Show("Export complete", "Export", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Shell("c:\sqlbackup.bat", AppWinStyle.NormalFocus)
    End Sub

    Sub Fill_Export_Table_Names()
        Dim tableNames As New DataTable
        Dim oScript As New clsScript
        'Dim fieldNames As New DataTable
        'OledbConnectionString = Get_Connection_String()
        'Dim CN As New OleDbConnection(OledbConnectionString)
        Try
            'CN.Open()
            'Dim n() As Object = {Nothing, Nothing, Nothing, "TABLE"}
            ''Dim n() As Object = {System.DBNull.Value, System.DBNull.Value, System.DBNull.Value, "TABLE"}
            ''tableNames = CN.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, n)
            'tableNames = oScript.GetTableNames(txtServerName.Text, txtDatabaseName.Text, txtUsername.Text, txtPassword.Text)
            'n(3) = Nothing
            'fieldNames = CN.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, n)
            'CN.Close()
            Generate_Table_Names_Grid(tableNames)
            'dgExportTables.DisplayMember = "TABLE_NAME"
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        End Try
    End Sub
    Sub Load_Default_Values()
        txtServerName.Text = "SHABDAR\SQLEXPRESS"
        txtDatabaseName.Text = "SMS"
        txtUsername.Text = "safdar"
        txtPassword.Text = "ghata"
        rdSQLServerAuthentication.Checked = True
        txtBackupDir.Text = "C:\DBBackup\"
    End Sub
    Function Get_Connection_String()
        Return "Provider=SQLOLEDB.1;data source=" + txtServerName.Text + ";user id=" + txtUsername.Text + ";Password=" + txtPassword.Text + ";database=" + txtDatabaseName.Text + ""
    End Function
    Sub Generate_Table_Names_Grid(ByRef tableNames As DataTable)
        'Create datatable, which will be used to fill grid
        Dim i As Integer
        Dim DT As New DataTable
        DT.Columns.Add("ObjectName", "".GetType)
        DT.Columns.Add("Select", True.GetType)
        For i = 0 To tableNames.Rows.Count - 1
            Dim sBCPCommand As String = Generate_BCPCommand(txtServerName.Text, txtDatabaseName.Text, txtUsername.Text, txtPassword.Text, CStr(tableNames.Rows(i)("TABLE_NAME")), "", txtNoOfRows.Text)
            DT.Rows.Add(New Object() {tableNames.Rows(i)("TABLE_NAME"), True, txtNoOfRows.Text, "", sBCPCommand})
        Next
        dgExportTables.DataSource = Nothing
        dgExportTables.DataSource = DT
        'dgExportTables.AutoResizeColumns()
        dgExportTables.Columns("TableName").Width = 150
        dgExportTables.Columns("Export").Width = 50
        dgExportTables.Columns("TotalRows").Width = 120
        dgExportTables.Columns("Condition").Width = 300
        dgExportTables.Columns("BCPCommand").Width = 300
    End Sub

    Private Sub cmdConnect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdConnect.Click
        Fill_Export_Table_Names()
    End Sub

    Private Sub cmdSelectAll_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSelectAll.Click
        If Not IsNothing(dgExportTables.DataSource) Then
            Dim i As Integer
            Dim DT As DataTable
            DT = CType(dgExportTables.DataSource, DataTable)
            For i = 0 To DT.Rows.Count - 1
                DT.Rows(i)("Export") = True
            Next
        End If
    End Sub
    Private Sub cmdDeSelectAll_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDeselectAll.Click
        If Not IsNothing(dgExportTables.DataSource) Then
            Dim i As Integer
            Dim DT As DataTable
            DT = CType(dgExportTables.DataSource, DataTable)
            For i = 0 To DT.Rows.Count - 1
                DT.Rows(i)("Export") = False
            Next
        End If
    End Sub
    Function Generate_BCPCommand(ByVal sServerName As String, ByVal sDatabaseName As String, ByVal sUserName As String, ByVal sPassword As String, ByVal sTableName As String, ByVal sCondition As String, ByVal sTotalRows As String)
        Dim sCommand As String = " bcp ""select " + sTotalRows + " from " + sDatabaseName + ".dbo." + sTableName + """ queryout """ + txtBackupDir.Text + sTableName + ".dat"" -S " + sServerName + " -U " + sUserName + " -P " + sPassword + " " + txtBCPOptions.Text
        Return sCommand
    End Function
End Class

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Shabdar Ghata
Web Developer
Canada Canada
Software Developer
 
http://www.shabdar.org

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.1411023.1 | Last Updated 17 Mar 2008
Article Copyright 2007 by Shabdar Ghata
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid