VB实现SQL Server数据库备份/恢复

原创 2007年09月21日 14:50:00
 **模 块 名:fBackupDatabase_a
'**描    述:备份数据库,返回出错信息,正常恢复,返回""
'**调    用:fBackupDatabase_a "备份文件名","数据库名"
'**参数说明:
'**          sBackUpfileName  恢复后的数据库存放目录
'**          sDataBaseName    备份的数据名
'**          sIsAddBackup     是否追加到备份文件中
'**说    明:引用Microsoft ActiveX Data Objects 2.x Library
'**创 建 人:邹建
'**日    期:2003年12月09日
'*************************************************************************
Public Function fBackupDatabase_a(ByVal sBackUpfileName$ _
                                , ByVal sDataBaseName$ _
                                , Optional ByVal sIsAddBackup As Boolean = False _
                                ) As String
                                
    Dim iDb As ADODB.Connection
    Dim iConcStr$, iSql$, iReturn$
    
    On Error GoTo lbErr
    
    '创建对象
    Set iDb = New ADODB.Connection
    
    '连接数据库服务器,根据你的情况修改连接字符串
    iConcStr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=zj"
    iDb.Open iConcStr
    
    '生成数据库备份语句
    iSql = "backup database [" & sDataBaseName & "]" & vbCrLf & _
            "to disk='" & sBackUpfileName & "'" & vbCrLf & _
            "with description='" & "zj-backup at:" & Date & "(" & Time & ")'" & vbCrLf & _
            IIf(sIsAddBackup, "", ",init")
            
    iDb.Execute iSql
    GoTo lbExit
    
lbErr:
    iReturn = Error
lbExit:
    fBackupDatabase_a = iReturn
End Function

'*************************************************************************
'**模 块 名:frestoredatabase_a
'**描    述:恢复数据库,返回出错信息,正常恢复,返回""
'**调    用:frestoredatabase_a "备份文件名","数据库名"
'**参数说明:
'**          sDataBasePath  恢复后的数据库存放目录
'**          sBackupNumber  是从那个备份号恢复
'**          sReplaceExist  指定是否覆盖已经存在的数据
'**说    明:引用Microsoft ActiveX Data Objects 2.x Library
'**创 建 人:邹建
'**日    期:2003年12月09日
'*************************************************************************
Public Function fRestoreDatabase_a(ByVal sBackUpfileName$ _
                                , ByVal sDataBaseName$ _
                                , Optional ByVal sDataBasePath$ = "" _
                                , Optional ByVal sBackupNumber& = 1 _
                                , Optional ByVal sReplaceExist As Boolean = False _
                                ) As String
    
    Dim iDb As ADODB.Connection, iRe As ADODB.Recordset
    Dim iConcStr$, iSql$, iReturn$, iI&
    
    On Error GoTo lbErr
    
    '创建对象
    Set iDb = New ADODB.Connection
    Set iRe = New ADODB.Recordset
    
    '连接数据库服务器,根据你的情况修改连接字符串
    iConcStr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=zj"
    iDb.Open iConcStr
    
    '得到还原后的数据库存放目录,如果没有指定,存放到SQL SERVER的DATA目录
    If sDataBasePath = "" Then
        iSql = "select filename from master..sysfiles"
        iRe.Open iSql, iDb, adOpenKeyset, adLockReadOnly
        iSql = iRe(0)
        iRe.Close
        sDataBasePath = Left(iSql, InStrRev(iSql, "/"))
    End If
    
    '检查数据库是否存在
    If sReplaceExist = False Then
        iSql = "select 1 from master..sysdatabases  where name='" & sDataBaseName & "'"
        iRe.Open iSql, iDb, adOpenKeyset, adLockReadOnly
        If iRe.EOF = False Then
            iReturn = "数据库已经存在!"
            iRe.Close
            GoTo lbExit
        End If
        iRe.Close
    End If
    
    '关闭用户进程,防止其它用户正在使用数据库,导致数据恢复失败
    iSql = "select spid from master..sysprocesses where dbid=db_id('" & sDataBaseName & "')"
    iRe.Open iSql, iDb, adOpenKeyset, adLockReadOnly
    While iRe.EOF = False
        iSql = "kill " & iRe(0)
        iDb.Execute iSql
        iRe.MoveNext
    Wend
    iRe.Close
    
    '获取数据库恢复信息
    iSql = "restore filelistonly from disk='" & sBackUpfileName & "'" & vbCrLf & _
        "with file=" & sBackupNumber
    iRe.Open iSql, iDb, adOpenKeyset, adLockReadOnly
    
    '生成数据库恢复语句
    iSql = "restore database [" & sDataBaseName & "]" & vbCrLf & _
        "from disk='" & sBackUpfileName & "'" & vbCrLf & _
        "with file=" & sBackupNumber & vbCrLf
    With iRe
        While Not .EOF
            iReturn = iRe("PhysicalName")
            iI = InStrRev(iReturn, ".")
            iReturn = IIf(iI = 0, "", Mid(iReturn, iI)) & "'"
            iSql = iSql & ",move '" & iRe("LogicalName") & _
                    "' to '" & sDataBasePath & sDataBaseName & iReturn & vbCrLf
            .MoveNext
        Wend
        .Close
    End With
    iSql = iSql & IIf(sReplaceExist, ",replace", "")
    
    iDb.Execute iSql
    iReturn = ""
    GoTo lbExit
    
lbErr:
    iReturn = Error
lbExit:
    fRestoreDatabase_a = iReturn
End Function
版权声明:本文为博主原创文章,未经博主允许不得转载。

相关文章推荐

SQL SERVER数据库备份恢复

  • 2006-02-23 09:05
  • 452KB
  • 下载

SQL Server数据库备份与恢复

一,备份 概念:是对sql serrver数据库或事务日志进行拷贝  备份:1.设备                 ...

sql Server数据库备份与恢复

  • 2009-08-17 17:08
  • 9.86MB
  • 下载

SQL Server数据库备份恢复-拷贝数据库文件mdf和日志文件ldf

SQL Server数据库备份有两种方式,一种是使用BACKUP DATABASE将数据库文件备份出去,另外一种就是直接拷贝数据库文件mdf和日志文件ldf的方式。下面将主要讨论一下后者的备份与恢复。...

SQL Server 数据库备份和还原认识和总结(二)

通过《SQL Server 数据库备份和还原认识和总结(一)》,相信您对数据备份和还原有了一个更深入的认识,在上文中我没有对事务日志做剖析,在此推荐宋沄剑的文章,对事务日志做了比较详细的讲解:http...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)