数据库还原

数据库还原

'引用sqldmo object library
Dim gSQLServer As SQLDMO.SQLServer
On Error GoTo ErrHandler:
    If gSQLServer Is Nothing Then
        Set gSQLServer = New SQLDMO.SQLServer
    End If
    gSQLServer.LoginTimeout = 15
    frmSelectServer.Show 1
    If frmSelectServer.connecString = "" Then Exit Sub
    gSQLServer.Connect frmSelectServer.connecString, "sa", "sa"
    Dim oRestore As SQLDMO.Restore
    Dim Msg As String
    Dim Response As String
    Set oRestore = New SQLDMO.Restore
    oRestore.DATABASE = "data"
     CommonDialog1.CancelError = True
   On Error GoTo Errhandler1
   ' 设置标志
   CommonDialog1.Flags = cdlOFNHideReadOnly
   ' 设置过滤器
   CommonDialog1.Filter = "All Files (data*.*)|data*.*"
   ' 指定缺省的过滤器
   CommonDialog1.FilterIndex = 2
   ' 显示“打开”对话框
   '
   Dim riqi As String
   CommonDialog1.FileName = riqi
   CommonDialog1.ShowOpen
   ' 显示选定文件的名字
   riqi = CommonDialog1.FileName
   
    oRestore.Files = riqi
  '当连接的时候，鼠标变化
    Screen.MousePointer = vbHourglass
    '恢复前断开所有的数据库连接
    Dim iDb As ADODB.Connection, iRe As ADODB.Recordset
    Set iDb = New ADODB.Connection
    Set iRe = New ADODB.Recordset
    Dim iConcStr As String
     '连接数据库服务器
    iConcStr = "Provider=sqloledb;" & _
           "Data Source=myServerName;" & _
           "Initial Catalog=master;" & _
           "User Id=myUsername;" & _
           "Password=myPassword"
    iDb.Open iConcStr
    Dim iSql As String
    iSql = "select spid from master..sysprocesses where dbid=db_id('data')"
    iRe.Open iSql, iDb, adOpenKeyset, adLockReadOnly
    frmMoive.Label1.Caption = "数据库在还原过程中,请等候。"
    frmMoive.Caption = "数据库恢复"
    frmMoive.Show vbModeless, Me
    DoEvents
    Me.Enabled = False
       Dim Spath As String
    If Right(App.Path, 1) <> "/" Then
        Spath = App.Path & "/"
    Else
        Spath = App.Path
    End If
    frmMoive.Animation1.Open Spath & "working.avi"
    frmMoive.Animation1.Play
    While iRe.EOF = False
        iSql = "kill " & iRe(0)
        iDb.Execute iSql
        iRe.MoveNext
    Wend
    iRe.Close
    iDb.Close
    '恢复数据库
    oRestore.SQLRestore gSQLServer
    '恢复后鼠标返回默认的状态
    Me.Enabled = True
    frmMoive.Animation1.Stop
    Unload frmMoive
    Set oRestore = Nothing
    Screen.MousePointer = vbDefault
    MsgBox "数据库还原成功！"
   
    Exit Sub

ErrHandler:
    MsgBox "Error " & Err.Description
    Exit Sub
Errhandler1:
       ' 用户按了“取消”按钮
   Exit Sub

将Excel的数据导入SQL server :
-- ======================================================
SELECT * into newtable
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:/book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
 
实例:
SELECT * into newtable
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:/Finance/account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

给一个vb的例子给你
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim stm As ADODB.Stream

Private Sub SavePictureToDB(cn As ADODB.Connection)
'将BMP图片存入数据库
On Error GoTo EH
    Set stm = New ADODB.Stream
    rs.Open "select ImagePath,ImageValue from tbl_Image", cn, adOpenKeyset, adLockOptimistic
    CommonDialog1.ShowOpen
    Text1.Text = CommonDialog1.FileName
   
    With stm
         .Type = adTypeBinary
         .Open
         .LoadFromFile CommonDialog1.FileName
    End With
    With rs
         .AddNew
         .Fields("ImagePath") = Text1.Text
         .Fields("ImageValue") = stm.Read
         .Update
    End With
    rs.Close
    Set rs = Nothing
Exit Sub
EH: MsgBox Err.Description, vbInformation, "Error"
End Sub

Private Sub LoadPictureFromDB(cn As ADODB.Connection)
'载数据库中读出BMP图片
On Error GoTo EH
    Dim strTemp As String
    Set stm = New ADODB.Stream
    strTemp = "c:/temp.tmp" '临时文件,用来保存读出的图片
    rs.Open "select ImagePath,ImageValue  from tbl_image", cn, , , adCmdText
    With stm
        .Type = adTypeBinary
        .Open
        .Write rs("ImageValue")
        .SaveToFile strTemp, adSaveCreateOverWrite
        .Close
    End With
    Image1.Picture = LoadPicture(strTemp)
    Set stm = Nothing
    rs.Close
    Set rs = Nothing
Exit Sub
EH: MsgBox Err.Description, vbInformation, "Error"
End Sub