怎样让SQL数据库每隔1分钟备份一次?
我想每隔1分钟让 pubs 数据库备份一次,备份到D盘,应该怎么实现?
[解决办法]
数据库维护计划中看看。
[解决办法]
打开服务管理器 选择服务-> 最下面一个 启动-> 开机启动
打开企业管理器 管理 数据库维护计划 新建 选择目标数据库 到备份计划页 选择更改设置时间 确定 一直到完成
其他都是界面操作
[解决办法]
strSQL = "BACKUP DATABASE " & m_con.DefaultDatabase & " TO DISK = 'D:\SQLBack\DB.BAK ' WITH INIT "
m_con.Execute strSQL
[解决办法]
你把你的企业管理器里的备份数据库里进行设定就好拉。不过数据库过大的话，就选差分备分。这样可以减少时间。过段时间在再完全备分下。
[解决办法]
首先确保 SQL SERVER Agent服务的运行
直接在企业管理器中,备份时 选择调度,可以按你的任意要求进行备份,但1分种一次是不是太频繁了点
[解决办法]
sqlserver备份
Private Sub mnudatabackup_Click()
On Error GoTo ErrHandler
Dim i As String
Dim SQLname As String
Dim iReturn As String
SQLname = Year(Now) & "年 " & Month(Now) & "月 " & "(QC).bak "
With CommonDialog1
.DialogTitle = "数据备份 "
.FileName = SQLname
.Filter = "(数据库)*.bak|*.bak "
.CancelError = True
.ShowOpen
.InitDir = App.Path & "\Databackup "
End With
iReturn = fBackupDatabase_a(CommonDialog1.FileName, "jzd ")
MsgBox iReturn
Exit Sub
ErrHandler:
' 用户按了“取消”按钮
MsgBox "用户取消备份操作或备份过程中出现错误，备份失败！ ", 48, "提示 "
Exit Sub
End Sub
还原
Private Sub mnudatarevert_Click()
On Error GoTo ErrHandler
Dim i As String
Dim iReturn As String
With CommonDialog1
.DialogTitle = "数据还原 "
.FileName = " "
.Filter = "(数据库)*.bak|*.bak "
.CancelError = True
.ShowOpen
.InitDir = App.Path & "\Databackup "
End With
iReturn = fRestoreDatabase_a(CommonDialog1.FileName, "jzd ")
MsgBox iReturn
Exit Sub
ErrHandler:
' 用户按了“取消”按钮
MsgBox "用户取消还原操作或还原过程中出现错误，还原失败！ ", 48, "提示 "
Exit Sub
End Sub
'*************************************************************************
'**模 块 名：fBackupDatabase_a
'**描 述：备份数据库,返回出错信息,正常恢复,返回 " "
'**调 用：fBackupDatabase_a "备份文件名 ", "数据库名 "
'**参数说明：
'** sBackUpfileName 恢复后的数据库存放目录
'** sDataBaseName 备份的数据名
'** sIsAddBackup 是否追加到备份文件中
'**说 明：引用Microsoft ActiveX Data Objects 2.x Library
'*************************************************************************
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$
Dim uid, pwd, addr As String
On Error GoTo lbErr
'读取数据库相关信息
uid = sa
pwd = " "
addr = 13.1.1.50
'创建对象
Set iDb = New ADODB.Connection
'连接数据库服务器,根据你的情况修改连接字符串
iConcStr = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID= " & uid & ";password= " & pwd & ";Initial Catalog=jzd;Data Source= " & addr
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
iReturn = "数据库备份成功！ "
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 = True _
) As String
Dim iDb As ADODB.Connection, iRe As ADODB.Recordset
Dim iConcStr$, iSql$, iReturn$, iI&
Dim uid, pwd, addr As String
On Error GoTo lbErr
'读取数据库相关信息
uid = sa
pwd = " "
addr = 13.1.1.50
'创建对象
Set iDb = New ADODB.Connection
Set iRe = New ADODB.Recordset
'连接数据库服务器,根据你的情况修改连接字符串
iConcStr = "Provider=SQLOLEDB.1;Persist Security Info=False;Data Source= " & addr & ";User ID= " & uid & ";password= " & pwd
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
