ASP实现备份SQL SERVER数据
类别: ASP教程 
建个bak文件夹啊，放数据！
db.asp代码如下：
<%
dim conn
dim connstr
\'on error resume next
set conn=server.CreateObject("adodb.connection")
connstr="Provider=SQLOLEDB;data source=192.168.2.1;UID=sa;Pwd=123;DataBase=test"
conn.Open connstr
if err<>0 then
Response.Write "无法建立到数据库的连接！"
end if
%>
backup.asp代码如下：
<!--#include file="db.asp"-->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<META NAME="Generator" CONTENT="EditPlus">
<TITLE> 数据备份系统 </TITLE>
<style type="text/css">
a{color:fff;text-decoration:none;}
a:hover{color:ccc;text-decoration:underline;}
ul{border-top:1px solid #fff;
border-left:1px solid #fff;
border-right:1px solid #fff;
margin:0px;
width:400px;}
li{list-style:none;border-bottom:1px solid #fff;height:25px;padding:4px;text-align:left;}
#box{list-style:none;border-bottom:1px solid #fff;height:25px;padding:4px;text-align:center;}
body{
background:#037EC5;
color:#fff;
font-size:12px;
text-align:center;
font-family:tahoma;
margin:0px;
}
#bak{position:absolute;
left:360px;
top:100px;
height:40px;
border:1px solid #ccc;
padding:20px;
FILTER: Alpha(opacity=75);
background:#fff;
color:#000;
}
</style>
</HEAD>
<BODY>
<%
Server.ScriptTimeOut=1000
ON error resume next
\'====================
\'=====xhtml制作=========
\'=====2005-3-31======
function CreateFile(sql)
Dim fso, tf,filename
filename=replace(replace(now()," ","-"),":","-")&".sql"
filename=server.mappath("bak/"&table&filename)
Set fso = CreateObject("Scripting.FileSystemObject")
Set tf = fso.CreateTextFile(filename, True)
tf.WriteLine "SET IDENTITY_INSERT ["&table&"] ON"
tf.WriteLine sql
tf.WriteLine "SET IDENTITY_INSERT ["&table&"] OFF"
tf.Close
end function
\'读取表名
Set rs = conn.OpenSchema(20)
Response.Write"<ul><li id=\'box\'>SQL SERVER数据备份脚本</li>"
Do While Not rs.EOF
If rs("TABLE_TYPE") = "TABLE" Then
Response.Write "<li>□ <a href=\'?table="&rs("TABLE_NAME")&"\'>备份 "& rs("TABLE_NAME") &"</a></li>"
End If
rs.MoveNext
Loop
Response.Write"</ul>"
Set rs = Nothing
table=request.querystring("table")
if table<>"" then
mysql="select * from "&table
set rs=server.createobject("adodb.recordset")
rs.open mysql,conn,1,1
\' 将字段名称循环读出
ColCount = Rs.Fields.Count
for intCount= 0 to ColCount-2
sqlBak=sqlBak&rs.Fields(intCount).Name&","
next
\'在最后的字段后加括号
sqlBak=sqlBak&Rs.Fields(ColCount-1).Name&")"
sqlHead="INSERT into ["&table&"]("&sqlBak&"values("
do while not rs.eof
for intCount= 0 to ColCount-2
mainSTR=replace(Rs.Fields(intCount).Value,"\'","\'\'")
if Rs.Fields(intCount).type=3 then
sqlData=sqlData&mainSTR&","
else
sqlData=sqlData&"\'"&mainSTR&"\',"
end if
next
\'最后的字段
\'替换单引号
lastSTR=replace(Rs.Fields(ColCount-1).Value,"\'","\'\'")
if Rs.Fields(ColCount-1).type=3 then
sqlData=sqlData&lastSTR&")"&vblf&sqlHead
else
sqlData=sqlData&"\'"&lastSTR&"\')"&vblf&sqlHead
end if
rs.movenext
loop
sqldata=sqlHead&sqldata
sqllong=len(sqldata)-len(vblf&sqlHead)
sqldata=left(sqldata,sqllong)
rs.close
set rs=nothing
conn.close
set conn=nothing
CreateFile(sqldata)
else
response.end
end if
%>
</BODY>
</HTML>
db.asp代码如下：
<%
dim conn
dim connstr
\'on error resume next
set conn=server.CreateObject("adodb.connection")
connstr="Provider=SQLOLEDB;data source=192.168.2.1;UID=sa;Pwd=123;DataBase=test"
conn.Open connstr
if err<>0 then
Response.Write "无法建立到数据库的连接！"
end if
%>
backup.asp代码如下：
<!--#include file="db.asp"-->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<META NAME="Generator" CONTENT="EditPlus">
<TITLE> 数据备份系统 </TITLE>
<style type="text/css">
a{color:fff;text-decoration:none;}
a:hover{color:ccc;text-decoration:underline;}
ul{border-top:1px solid #fff;
border-left:1px solid #fff;
border-right:1px solid #fff;
margin:0px;
width:400px;}
li{list-style:none;border-bottom:1px solid #fff;height:25px;padding:4px;text-align:left;}
#box{list-style:none;border-bottom:1px solid #fff;height:25px;padding:4px;text-align:center;}
body{
background:#037EC5;
color:#fff;
font-size:12px;
text-align:center;
font-family:tahoma;
margin:0px;
}
#bak{position:absolute;
left:360px;
top:100px;
height:40px;
border:1px solid #ccc;
padding:20px;
FILTER: Alpha(opacity=75);
background:#fff;
color:#000;
}
</style>
</HEAD>
<BODY>
<%
Server.ScriptTimeOut=1000
ON error resume next
\'====================
\'=====xhtml制作=========
\'=====2005-3-31======
function CreateFile(sql)
Dim fso, tf,filename
filename=replace(replace(now()," ","-"),":","-")&".sql"
filename=server.mappath("bak/"&table&filename)
Set fso = CreateObject("Scripting.FileSystemObject")
Set tf = fso.CreateTextFile(filename, True)
tf.WriteLine "SET IDENTITY_INSERT ["&table&"] ON"
tf.WriteLine sql
tf.WriteLine "SET IDENTITY_INSERT ["&table&"] OFF"
tf.Close
end function
\'读取表名
Set rs = conn.OpenSchema(20)
Response.Write"<ul><li id=\'box\'>SQL SERVER数据备份脚本</li>"
Do While Not rs.EOF
If rs("TABLE_TYPE") = "TABLE" Then
Response.Write "<li>□ <a href=\'?table="&rs("TABLE_NAME")&"\'>备份 "& rs("TABLE_NAME") &"</a></li>"
End If
rs.MoveNext
Loop
Response.Write"</ul>"
Set rs = Nothing
table=request.querystring("table")
if table<>"" then
mysql="select * from "&table
set rs=server.createobject("adodb.recordset")
rs.open mysql,conn,1,1
\' 将字段名称循环读出
ColCount = Rs.Fields.Count
for intCount= 0 to ColCount-2
sqlBak=sqlBak&rs.Fields(intCount).Name&","
next
\'在最后的字段后加括号
sqlBak=sqlBak&Rs.Fields(ColCount-1).Name&")"
sqlHead="INSERT into ["&table&"]("&sqlBak&"values("
do while not rs.eof
for intCount= 0 to ColCount-2
mainSTR=replace(Rs.Fields(intCount).Value,"\'","\'\'")
if Rs.Fields(intCount).type=3 then
sqlData=sqlData&mainSTR&","
else
sqlData=sqlData&"\'"&mainSTR&"\',"
end if
next
\'最后的字段
\'替换单引号
lastSTR=replace(Rs.Fields(ColCount-1).Value,"\'","\'\'")
if Rs.Fields(ColCount-1).type=3 then
sqlData=sqlData&lastSTR&")"&vblf&sqlHead
else
sqlData=sqlData&"\'"&lastSTR&"\')"&vblf&sqlHead
end if
rs.movenext
loop
sqldata=sqlHead&sqldata
sqllong=len(sqldata)-len(vblf&sqlHead)
sqldata=left(sqldata,sqllong)
rs.close
set rs=nothing
conn.close
set conn=nothing
CreateFile(sqldata)
else
response.end
end if
%>
</BODY>
</HTML>
- 上一篇: 记录的一些ASP学习笔记
- 下一篇: ASP中使用SQL语句操作数据库
