backupsql.vb

系统概要: 本系统采用VB.NET开发. 开发平台:Windows XP Professional SP2 (English Version) 开发环境:Visual Studio .NET

'
'    Copyright(C)2006,济南大学材料科学与工程学院
'    All right reserved.
'
'    文件名称:BackupSQL.vb
'    文件标识:
'    摘    要:备分本地数据库数据
'
'    当前版本:1.0.0
'    作    者:梁  海
'    完成日期:2006-8-24
'
'    取代版本:
'    原作者  :
'    完成日期:
'
'    修改历史:
'
'
Imports System
Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections

Public Class BackupSQL

    Private _Server As String           '服务器
    Private _Uid As String              '登陆名
    Private _Password As String         '登陆密码
    Private _Database As String         '要操作的数据库
    Private _ConnectString As String    '数据库连接字符串

    Sub New()

        _ConnectString = getConnectString()
        _Server = CutString(_ConnectString, "server=", ";")
        _Uid = CutString(_ConnectString, "uid=", ";")
        _Password = CutString(_ConnectString, "pwd=", ";")
        _Database = CutString(_ConnectString, "database=", ";")

    End Sub

    Private Function getKey(ByVal s As String) As String

        Return System.Configuration.ConfigurationSettings.AppSettings(s)

    End Function

    Private Function getConnectString() As String

        Return getKey("SQL")

    End Function

    Private Function CutString(ByVal s As String, _
                               ByVal beginStr As String, _
                               ByVal endStr As String) As String

        Dim SubString As String
        SubString = s.Substring(s.IndexOf(beginStr) + beginStr.Length)
        SubString = SubString.Substring(0, SubString.IndexOf(endStr))
        Return SubString

    End Function

    Private Function getFileName() As String

        Dim strPath As String
        Dim strFileName As String
        Dim dt As DateTime

        dt = Now()
        strPath = getKey("SQL_BACKUP")
        strFileName = "SQL_DB_" & _Database & "_"
        strFileName &= dt.Year.ToString()
        strFileName &= dt.Month.ToString()
        strFileName &= dt.Day.ToString()
        strFileName &= dt.Hour.ToString()
        strFileName &= dt.Minute.ToString()
        strFileName &= dt.Second.ToString()
        strFileName &= ".bak"

        Return strPath & strFileName

    End Function

    Public Function Backup() As Boolean

        Dim strFileName As String
        strFileName = getFileName()
        Dim oBackup As SQLDMO.Backup
        oBackup = New SQLDMO.BackupClass
        Dim oSQLServer As SQLDMO.SQLServer
        oSQLServer = New SQLDMO.SQLServerClass

        Try
            oSQLServer.LoginSecure = False
            oSQLServer.Connect(_Server, _Uid, _Password)
            oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database
            oBackup.Database = _Database
            oBackup.Files = strFileName
            oBackup.BackupSetName = _Database
            oBackup.BackupSetDescription = "数据库备份"
            oBackup.Initialize = True
            oBackup.SQLBackup(oSQLServer)

            Return True

        Catch ex As Exception
            Common.WriteLog(" Occur Error on Backup SQL Database " & _
                               _Database & " " & ex.ToString)
            Return False
        Finally
            oSQLServer.DisConnect()
        End Try

    End Function

    Public Function Restore(ByVal FileName As String) As Boolean

        If (Exepro() <> True) Then '执行存储过程

            WriteLog("SQL数据库备份操作失败" & Now.ToString)
            Return False

        Else

            Dim oRestore As SQLDMO.Restore
            oRestore = New SQLDMO.RestoreClass
            Dim oSQLServer As SQLDMO.SQLServer
            oSQLServer = New SQLDMO.SQLServerClass

            Try
                oSQLServer.LoginSecure = False
                oSQLServer.Connect(_Server, _Uid, _Password)
                oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database
                oRestore.Database = _Database

                oRestore.Files = FileName
                oRestore.FileNumber = 1
                oRestore.ReplaceDatabase = True
                oRestore.SQLRestore(oSQLServer)

                WriteLog("SQL数据库备份操作成功" & Now.ToString)
                Return True

            Catch ex As Exception
                WriteLog("SQL数据库备份操作失败" & Now.ToString)
                Return False
            Finally
                oSQLServer.DisConnect()
            End Try
        End If

    End Function

    Private Function Exepro() As Boolean

        Dim oCnn As SqlConnection

        oCnn = New SqlConnection("server=" & _Server & ";uid=" & _
                                  _Uid & ";pwd=" & _Password & _
                                  ";database=master")
        Dim oCmd As SqlCommand
        oCmd = New SqlCommand("killspid", oCnn)
        oCmd.CommandType = CommandType.StoredProcedure
        oCmd.Parameters.Add("@dbname", _Database)
        Try
            oCnn.Open()
            oCmd.ExecuteNonQuery()
            oCnn.Close()
            Return True
        Catch ex As Exception
            Common.WriteLog("杀死进程失败" & ex.ToString)
            Return False
        Finally
            If oCnn.State = ConnectionState.Open Then
                oCnn.Close()
            End If
        End Try

    End Function

End Class