今天一台MSSQL2000出问题,SQLSERVERAGENT服务又启不过来,胡乱撮写的一个脚本。
'*******************************************************************
' 目的:当MSSQL的SQLSERVERAGENT启不过来时,可以用这个脚本来备份全部数据库,也可以做成计划任务来执行
' 注意还原master数据库要用单用户启动MSSQL
'*******************************************************************
dim spath
spath="E:\sqldatabackup\db\" '请注意修改备份路径
dim backpath
backpath=spath & cstr(date() & hour(now) & minute(now) & second(now)) & "\"
deletefile(spath & "all.sql")
CreateFolderDemo()
strContent = ExportData
Call LogToFile(strContent, "all.sql")
'---------------------------------
Set shell = WScript.CreateObject("WScript.Shell")
cmd = "osql -E -i all.sql"
Shell.Run cmd, 1, True
Set shell = Nothing
'---------------------------------
'Wscript.Echo "完成!"
'新建文件夹
Function CreateFolderDemo()
Dim fso, f
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.CreateFolder(backpath & "")
CreateFolderDemo = f.Path
Set fso = Nothing
End Function
'删除文件
function deletefile(filename)
if filename<>"" then
Set fso = CreateObject("Scripting.FileSystemObject")
if fso.FileExists(filename) then
fso.DeleteFile filename
else
' "<script>alert(''该文件不存在'');</script>"
end if
end if
Set fso = Nothing
end function
'写文件
Function LogToFile(strContent,strFileName)
Const ForReading = 1, ForWriting = 2,ForAppending = 8
Dim fso, f
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.OpenTextFile(strFileName, ForAppending, True)
f.WriteLine strContent
Set fso = Nothing
End Function
'生成备份脚本
Function ExportData()
Dim strConn,strSql,strData
Dim objConn,objRs
strConn = "Provider=sqloledb;Data Source=(local);Initial Catalog=master;Integrated Security=SSPI;"
Set objConn = CreateObject("ADODB.Connection")
objConn.Open strConn
'strSql = "SELECT name,filename FROM sysdatabases"
strSql = "SELECT name FROM sysdatabases"
Set objRs = objConn.Execute(strSql)
If NOT objRs.EOF Then
While NOT objRs.EOF
strData = strData & "backup database [" & objRs(0).value &"] to disk='" & backpath & objRs(0).value & ".bak'" & vbCrLf & "GO" & vbCrLf
objRs.MoveNext
Wend
End If
If Err.Number <> 0 Then
ExportData = Err.Description
Else
ExportData = strData
End If
objRs.Close
objConn.Close
Set objRs = Nothing
Set objConn = Nothing
End Function