怎么用程序代码实现备份Sql 200数据库呢
解决方案 »
- C# DataGridView中如何合并某一行所有单元格??
- 请教一个基础的问题
- 带参数的exe文件调用问题!!
- 新年第一问:override OnPaint()方法一定要调用base.OnPaint(e)方法么?有什么用?不调用行么?
- 关于操作数据库的方法设计?
- C# 屏幕监控问题
- C#显示数据中的多条数据 需要用到什么控件?
- 为什么我的Duwamish的Web项目不能加载?
- dataGRIDVIEW 绑定不刷新
- 处理异常后,TRY语块中的其他部分还会被执行吗?
- 怎么样才能在前台执行的速度快点?
- 急问:如何在在vc#环境下,select一个表tab1并且返回一个值(bool值或者int值)?
'完全备份
'只做备份,不做还原
Dim tmpPath As String
If InStr(Trim(Text1.Text), ".") = 0 Then
tmpPath = Trim(Text1.Text)
Else
tmpPath = Trim(Left(Trim(Text1.Text), Len(Trim(Text1.Text)) - 4)) '去掉扩展名
End If
'设备名随机产生
Dim tmpsql As String
Dim theDevice As String
theDevice = "Device" & Trim(Year(Now)) & Trim(Month(Now)) & Trim(Day(Now)) & Trim(Hour(Now))
If BackupDB("JHDB", tmpPath, theDevice) = True Then
'保存此备份记录
MsgBox "备份数据库成功!", vbExclamation + vbOKOnly, "提示"
End If
End SubPrivate Sub Command2_Click()
Unload Me
End Sub Function BackupDB(ByVal DbName As String, ByVal Selfname As String, ByVal Devicename As String) As Boolean '传数据库的名字
On Error GoTo itr
BackupDB = False
Dim cn As New ADODB.Connection
Dim cm As New ADODB.Command
Dim rs As New ADODB.Recordset
cn.Open (cnstr)
rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient
cm.ActiveConnection = cn
Dim filename As String
' Set file = CreateObject("scripting.filesystemobject")
DoEvents
cn.Execute "Backup log " & DbName & " with no_log"
cn.Execute "Dbcc shrinkdatabase (" & DbName & ")"
filename = Trim(Selfname) & ".dat" '由日期生成文件名
If UCase(Dir(filename)) = UCase(filename) Then '存在该文件则删除
Kill (filename)
End If
cm.CommandText = "exec sp_helpdevice" '显示所有备份设备
Set rs = cm.Execute
If Not rs.BOF And Not rs.EOF Then
Do While Not rs.EOF
If Trim(rs.Fields(0).Value) = Trim(Devicename) Then
cm.CommandText = "exec sp_dropdevice '" & Trim(Devicename) & "'" '判断是否存在该备份设备,如果有则删除
cm.Execute
End If
rs.MoveNext
Loop
End If rs.Close
' Try
cm.CommandText = "exec sp_addumpdevice 'disk','" & Trim(Devicename) & "','" & Trim(filename) & "'"
cm.Execute cm.CommandText = "backup database " & Trim(DbName) & " to " & Trim(Devicename)
cm.Execute
BackupDB = True
' Catch ee As Exception
'End Try
cn.Close
Exit Function
itr:
BackupDB = False
End Function
两个存储过程为:
create proc backup_database
(
@device_name varchar(80),--备份设备名
@file_name varchar(125), --备份文件名
@devicelog_name varchar(85),--日志备份设备名
@filelog_name varchar(130) --日志备份文件名
)
as
--创建数据库的备份逻辑设备
exec sp_addumpdevice 'disk', @device_name ,@file_name //创建设备逻辑名
exec sp_addumpdevice 'disk', @devicelog_name, @filelog_name
backup database testdb to @device_name //备份数据库
backup log testdb to @devicelog_name //备份日志create proc Rewordtable
(
@device_name varchar(80),
@devicelog_name varchar(85)
)
as
Restore database testdb from @device_name
Restore log testdb @devicelog_name
备份:CREATE PROCEDURE BackDataBase @dbname varchar(128), @backPath varchar(256) AS BACKUP DATABASE @dbname TO DISK = @backPath WITH INIT
恢复就直接使用Restore就可