http://www.2ec.cn/article/17.shtm
数据库操作(包括数据库备份、恢复、压缩、附加、删除、浏览) A、数据库备份(dbback.asp): 参数:
Servername (服务器地址或ip) Userid (数据库服务器登陆名) Userpassword (数据库服务器登陆密码) DBname (备份数据库名) DBbackfile (备份文件路径以及文件名,使用绝对路径,如果使用相对路径,代码做适当调整。)
核心代码:
<% dim msvr,muid,mpwd,mdb,mto msvr=Request.Form("Servername") muid=Request.Form("Userid") mpwd=Request.Form("Userpassword") mdb=Request.Form("DBname") mto=Request.Form("DBbackfile") '如果是相对路径,则:mto=server.mappath(Request.Form("DBbackfile")) if mpwd="" then mpwd="''" on error resume next set dmosvr=server.CreateObject("SQLDMO.SQLServer") dmosvr.connect msvr,muid,mpwd if err.number>0 then Response.Redirect("backuperr.asp?err="&err.number) mdevname="Backup_"&muid&"_"&mdb set dmodev=server.CreateObject("SQLDMO.BackupDevice") dmodev.name=mdevname dmodev.type=2 dmodev.PhysicalLocation=mto dmosvr.BackupDevices.Add dmodev if err.number>0 then Response.Redirect("backuperr.asp?err="&err.number) set dmobak=server.CreateObject("SQLDMO.Backup") dmobak.database=mdb dmobak.devices=mdevname dmobak.sqlbackup dmosvr if err.number>0 then Response.Redirect("backuperr.asp?err="&err.number) dmosvr.backupdevices(mdevname).remove set dmobak=nothing set dmodev=nothing dmosvr.disconnect set dmosvr=nothing %>
错误捕捉:
backuperr.asp 错误号 错误原因 18456 数据库用户名或密码无效 20482 服务器名错误或连接失败 911 数据库不存在 15026 服务器路径错误 3201 服务器路径错误 3254 数据库恢复文件非法(用于数据库恢复) 其他 未知错误
B、数据库恢复(dbrestore.asp)
<% txtsvr=request("txtsvr") txtuid=request("txtuid") txtpwd=request("txtpwd") txtfrom=request("txtfrom") if txtsvr="" or txtuid="" or txtpwd="" or txtfrom="" then %> <script language="javascript"> alert("请填完必要的数据!"); history.go(-1); </script> <% response.End() end if Set srv=Server.CreateObject("SQLDMO.SQLServer") srv.LoginTimeout = 30 srv.Connect txtsvr,txtuid,txtpwd Set rest=Server.CreateObject("SQLDMO.Restore") rest.Action=0 ' 完全恢复 rest.Database=request("txtdb") rest.Devices=Files rest.Files=server.MapPath(txtfrom) rest.ReplaceDatabase=True '强制恢复,覆盖当前存在数据库 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 "恢复成功! " %>
C、数据库压缩
<% Dim StrServer,StrUid,StrSaPwd,StrDbName StrServer="(local)" StrUid="sa" StrSaPwd="123456" StrDbName="dygood" Dim Conn Dim StrDSN StrDSN="driver={SQL server};server="&StrServer&";uid="&StrUid&";pwd="&StrSaPwd&";database="&StrDbName set Conn = Server.CreateObject("ADODB.Connection") Conn.Open StrDSN on error resume next sql="BACKUP log dygood WITH NO_LOG BACKUP log dygood WITH TRUNCATE_ONLY DBCC SHRINKDATABASE(dygood)" conn.execute(sql) %>
D、数据库附加(attach.asp) 核心代码:
on error resume next sql="exec sp_attach_db @dbname='"&request("database")&"', @filename1='"&server.MapPath(mdffile)&"', @filename2='"&server.MapPath(ldffile)&"'" conn.execute (sql)
如果数据库文件或日志文件不存在,或者数据库已经存在,应该触发错误。 捕捉“数据库已存在错误”:
<% set rs=conn.execute("exec sp_helpdb") do while not rs.eof if rs("name")=request("database") then response.write("数据库已经存在!") end if rs.movenext loop %>
E、数据库删除 删除数据库
conn.execute("drop database "&request("name"))
删除表格
conn.execute("drop table "&request("tablename"))
删除字段
conn.execute("alter table "&request("tablename")&" drop column "&request("colname"))
F、数据库浏览(数据库浏览、表格浏览、字段浏览) 数据库浏览
set rs=conn.execute("exec sp_helpdb")
表格浏览( 需要获取 数据库名 参数)
<% Dim StrServer,StrUid,StrSaPwd,StrDbName StrServer="(local)" StrUid="sa" StrSaPwd="123456" StrDbName=request(“dbname”) '数据库名 Dim Conn Dim StrDSN StrDSN="driver={SQL server};server="&StrServer&";uid="&StrUid&";pwd="&StrSaPwd&";database="&StrDbName set Conn = Server.CreateObject("ADODB.Connection") Conn.Open StrDSN sql="select id,name,crdate,refdate from sysobjects where xtype='U' and name<>'dtproperties'" set rs=conn.execute(sql) %>
字段浏览 (需要获取 数据库名和表格id 参数)
<% Dim StrServer,StrUid,StrSaPwd,StrDbName StrServer="(local)" StrUid="sa" StrSaPwd="123456" StrDbName=request("dbname") '数据库名 Dim Conn Dim StrDSN StrDSN="driver={SQL server};server="&StrServer&";uid="&StrUid&";pwd="&StrSaPwd&";database="&StrDbName set Conn = Server.CreateObject("ADODB.Connection") Conn.Open StrDSN sql="select id,name,xtype,length,isnullable from syscolumns where id="&request("id") set rs=conn.execute(sql) %>