想在前台做个备份和恢复页面,但是用ASP的FSO只能复制备份ACCESS,对于SQL无用,请教各位有什么好的办法,,
解决方案 »
- 程序中如何确定 sql server 表主键
- 求sql语句
- sqlserver2005 生成XML的字串时有长度限制,如何解决!!
- O(∩_∩)O~求一个比较复杂的SQL 查询问题 (*^__^*) ……
- 高分求助!!SQLServer里可以对日期格式的数据进行操作嘛,例如:我有一个日期20030609,我想得到11天前的日期,该怎么做呢?
- 求一条sql语句,大家帮忙
- 按 RID 检索行
- 求解决sql server单用户模式启动问题
- 为什么同样的SQL语句在SQL Server里正确在VC里确报错?????
- ACCESS 和SQL SERVER 的SQL语句有区别么??比如UPDATE这句话!!
- SQL2005转为sql2000时,发生两个问题
- VB中建立查询
<HTML><HEAD>
<TITLE>SQL Server 数据库的备份与恢复</TITLE>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
</HEAD>
<BODY>
<form method="post" name=myform>
选择操作:<INPUT TYPE="radio" NAME="act" id="act_backup" value="backup"><label for=act_backup>备份</label>
<INPUT TYPE="radio" NAME="act" id="act_restore" value="restore"><label for=act_restore>恢复</label>
<br>数据库名:<INPUT TYPE="text" NAME="databasename" value="<%=request("databasename")%>">
<br>文件路径:<INPUT TYPE="text" NAME="bak_file" value="c:\$1.bak">(备份或恢复的文件路径)<br>
<input type="submit" value="确定">
</form>
★数据库的备份
<%dim sqlserver,sqlname,sqlpassword,sqlLoginTimeout,databasename,bak_file,act
sqlserver = "localhost" 'sqlserver服务器名称或IP
sqlname = "sa" '用户名
sqlpassword = "sa" '密码
sqlLoginTimeout = 15 '登录超时时间,稍微大点比较合适
databasename = trim(request("databasename"))
bak_file = trim(request("bak_file"))
bak_file = replace(bak_file,"$1",databasename)
act = lcase(request("act"))
if databasename = "" then
response.write "input database name"
else
if act = "backup" then
Set srv=Server.CreateObject("SQLDMO.SQLServer")
srv.LoginTimeout = sqlLoginTimeout
srv.Connect sqlserver,sqlname, sqlpassword
Set bak = Server.CreateObject("SQLDMO.Backup")
bak.Database=databasename
bak.Devices=Files
bak.Files=bak_file
bak.SQLBackup srv
if err.number>0 then
response.write err.number&"<font color=red><br>"
response.write err.description&"</font>"
end if
Response.write "<font color=green>备份成功!</font>"
elseif act = "restore" then
★恢复数据库
Set srv=Server.CreateObject("SQLDMO.SQLServer")
srv.LoginTimeout = sqlLoginTimeout
srv.Connect sqlserver,sqlname, sqlpassword
Set rest=Server.CreateObject("SQLDMO.Restore")
rest.Action=0 ' full db restore
rest.Database=databasename
rest.Devices=Files
rest.Files=bak_file
rest.ReplaceDatabase=True 'Force restore over existing database
if err.number>0 then
response.write err.number&"<font color=red><br>"
response.write err.description&"</font>"
end if
rest.SQLRestore srv
Response.write "<font color=green>恢复成功!</font>"
else
Response.write "<font color=red>没有选择操作</font>"
end if
end if%>
</BODY></HTML>
sqlname = "sa " '用户名
sqlpassword = "sa " '密码 这个地方麻烦您解释下好吗?和远程数据库不需要连接?
还有个问题,在恢复时候出现问题:Microsoft SQL-DMO (ODBC SQLState: 42000) (0x800A0C1D)
[Microsoft][ODBC SQL Server Driver][SQL Server]因为数据库正在使用,所以未能获得对数据库的排它访问权。 [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE DATABASE 操作异常终止。
请赐教,急
我在db_hfcz.asp里设置了单用户并使用回滚恢复数据还是说不能恢复。怎么样才能停止数据库从而恢复。而且我在本机上可以实现,但是上传了之后就连接不上了。。是不是虚拟机上的数据库连接语句不对。。我搞不清楚连接本地的SQL服务器还是远端数据库服务器。。您帮忙。。非常谢谢!!db_bf.asp
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.ConnectionString="provider=sqloledb;server=127.0.0.1;UID=sa;PWD=sa;Database=**"
conn.open
%><style type="text/css">
</style>
<tr bgcolor="#dddddd">
<td height="1" colspan="4"></td>
</tr>
<tr>
<td height="34" colspan="2" bgcolor="#efefef"> <span class="style2"><strong>数据库恢复</strong></span></td>
<td height="19"> </td>
<td height="19"> </td>
</tr>
<tr valign="middle">
<td height="184" colspan="4">
<body>
<script language="javascript">
function jiancha(){
document.form1.submit();
}
</script>
<%
Dim fs, folderpara, folderArray '声明3个变量
folderpara=server.mappath("../backup")
%>
<form action="db_hfcz.asp" name="form1" method="post">
<table align="center">
<tr>
<td width="275" class="css"><div align="center"><span class="css"> 请选择要恢复的数据: </span></div></td>
</tr>
<tr>
<td height="27" > <div align="center">
<%
Call ShowFolderList(folderpara)
%>
</div></td>
</tr>
<tr>
<td> <div align="center">
<input type="button" name="hf" value="开始恢复" onClick="return jiancha();">
</div></td>
</tr>
</table>
<%
Set fs = CreateObject("Scripting.FileSystemObject")
set fs = Nothing '将对象中的数据在系统内存中清除
Sub ShowFolderList(folderspec)
Dim f, f1, fc, s,fs
Set fs=Server.CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set fc = f.SubFolders
Set fs=f.Files
For Each f1 in fs
s = f1.name
if right(s,4)=".sql" then
response.write "<input type='radio' value='" & s & "' name='hf'>" & s & "<br>"
end if
Next
end sub
%>
<p class="css"> </p>
</form>
</td>
</tr>
</table> db_bfcz.asp(备份处置)<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<body bgcolor="#FFFFFF">
<%response.expires=0
bf=request("bf")
str=server.mappath("../backup") '存储数据库的备份路径
set conn=server.createobject("adodb.connection")
conn.open "Driver={Sql Server};Server=(local);UID=sa;pwd=sa"
on error resume next '用于设置错误陷阱
conn.execute "backup database ** to disk='" & str & "\" & bf & ".sql'" '**是为远程数据库名称
response.write "<title>提示页面</title><br><br><br><br><br><br><p align='center'><font face='楷体_GB2312' size='5' color=#ff0000><b>"
if err.number=0 then
response.write "<script language='javascript'>alert('数据库备份完成!');window.location.href='../admin.asp';</script>"
else
response.write "<br>数据库备份失败,请检查后重试!</b></font></p><br><center><a href='javascript:history.back(1)'>返回</a></center>"
response.write err.Description '输出系统的错误提示信息
end if
conn.close
set conn=nothing
%>
db_hf.asp (恢复)
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.ConnectionString="provider=sqloledb;server=127.0.0.1;UID=sa;PWD=sa;Database=**"
conn.open
%><style type="text/css">
</style>
<tr bgcolor="#dddddd">
<td height="1" colspan="4"></td>
</tr>
<tr>
<td height="34" colspan="2" bgcolor="#efefef"> <span class="style2"><strong>数据库恢复</strong></span></td>
<td height="19"> </td>
<td height="19"> </td>
</tr>
<tr valign="middle">
<td height="184" colspan="4">
<body>
<script language="javascript">
function jiancha(){
document.form1.submit();
}
</script>
<%
Dim fs, folderpara, folderArray '声明3个变量
folderpara=server.mappath("../backup")
%>
<form action="db_hfcz.asp" name="form1" method="post">
<table align="center">
<tr>
<td width="275" class="css"><div align="center"><span class="css"> 请选择要恢复的数据: </span></div></td>
</tr>
<tr>
<td height="27" > <div align="center">
<%
Call ShowFolderList(folderpara)
%>
</div></td>
</tr>
<tr>
<td> <div align="center">
<input type="button" name="hf" value="开始恢复" onClick="return jiancha();">
</div></td>
</tr>
</table>
<%
Set fs = CreateObject("Scripting.FileSystemObject")
set fs = Nothing '将对象中的数据在系统内存中清除
Sub ShowFolderList(folderspec)
Dim f, f1, fc, s,fs
Set fs=Server.CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set fc = f.SubFolders
Set fs=f.Files
For Each f1 in fs
s = f1.name
if right(s,4)=".sql" then
response.write "<input type='radio' value='" & s & "' name='hf'>" & s & "<br>"
end if
Next
end sub
%>
<p class="css"> </p>
</form>
</td>
</tr>
</table> db_hfcz.asp<%response.expires=0 '设置浏览器缓存页面的时间
hf=request("hf")
if hf="" then '用条件语句判断选择的恢复信息不能为空
response.write "<title>提示页面</title><br><br><br><br><br><br><p align='center'><font face='楷体_GB2312' size='5' color=#ff0000><b>"& "<br>必须选定一项要恢复的数据,请查清后重试!</b></font></p>"
response.write "<p align='center'><a href='javascript:history.back(1)'>返回</a></p>"
response.end
end if
'连接数据库
str=server.mappath("../backup") & "\" & hf
set conn=Server.CreateObject("ADODB.Connection")
conn.ConnectionString="provider=sqloledb;server=127.0.0.1;UID=sa;PWD=sa;Database=**"
conn.open
on error resume next '设置错误陷阱
'设置单用户并使用回滚恢复数据,保障数据恢复质量。
conn.execute " alter database ** set single_user with rollback immediate restore database jxph from disk = '" & str & "'"
response.write "<title>提示页面</title><br><br><br><br><br><br><p align='center'><font face='楷体_GB2312' size='5' color=#ff0000><b>"
if err.number=0 then
response.Redirect("main.asp") '无错误信息跳转到数据恢复成功页面
else
response.write "<br>数据库恢复失败,请检查后重试!</b></font></p><br><center><a href='javascript:history.back(1)'>返回</a></center>"
response.write err.Description '输出系统的错误提示信息
end if
conn.execute " alter database ** set MULTI_USER" '恢复成功后将数据库设置为多用户
set conn=nothing
conn.close
%>