|
cnitblog.com/lxasp - - 有一种信念叫做编程
|
posts - 61, comments - 34, trackbacks - 0, articles - 0
|
|
|
Posted on 2012-04-13 20:54 lxasp 阅读(686) 评论(0) 编辑 收藏 引用 所属分类: ASP
Function newConnMdf(cnn, dbn, svr, usr, pwd)
Dim conn
Dim cnnSqlUserId
Dim cnnSqlPassWord
Dim cnnSqlDataBase
Dim cnnSqlServer
On Error Resume Next
cnnSqlUserId = usr
cnnSqlPassWord = pwd
cnnSqlDataBase = dbn
cnnSqlServer = svr
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB.1;User ID=" & cnnSqlUserId & ";Password=" & cnnSqlPassWord & ";Initial Catalog=" & cnnSqlDataBase & ";Data Source=" & cnnSqlServer & ""
Set cnn = conn
newConnMdf = True
If Err Then
cnn = Empty
newConnMdf = False
Err.Clear
End If
On Error GoTo 0
End Function
Function newConnMdb(cnn, dbn)
Dim conn
Dim cnnSqlDataBase
On Error Resume Next
cnnSqlDataBase = dbn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(cnnSqlDataBase)
Set cnn = conn
newConnMdb = True
If Err Then
cnn = Empty
newConnMdb = False
Err.Clear
End If
On Error GoTo 0
End Function
Function newRso(mConn, rs, SQLz)
Dim xRS
On Error Resume Next
Set xRS = Server.CreateObject("ADODB.Recordset")
xRS.Open SQLz, mConn, 1, 1
Set rs = xRS
newRso = True
If Err Then
rs = Empty
newRso = False
Err.Clear
End If
On Error GoTo 0
End Function
Function newRsu(mConn, rs, SQLz)
Dim xRS
On Error Resume Next
Set xRS = Server.CreateObject("ADODB.Recordset")
xRS.Open SQLz, mConn, 1, 3
Set rs = xRS
newRsu = True
If Err Then
rs = Empty
newRsu = False
Err.Clear
End If
On Error GoTo 0
End Function
Function rsGetRows(mConn, SQLz)
Dim xRS
On Error Resume Next
Set xRS = Server.CreateObject("ADODB.Recordset")
xRS.Open SQLz, mConn, 0, 1
rsGetRows = xRS.GetRows
If Err Then
rsGetRows = Empty
Err.Clear
End If
On Error GoTo 0
End Function
Function rsGetVal0(mConn, SQLz)
Dim xRS, r
On Error Resume Next
Set xRS = Server.CreateObject("ADODB.Recordset")
xRS.Open SQLz, mConn, 0, 1
rsGetVal0 = xRS(0)
If Err Then
rsGetVal0 = Empty
Err.Clear
End If
On Error GoTo 0
End Function
Function exSqlRestoreDb(conn, dbname, tosave, frombak)
On Error Resume Next
Dim a
a = rsGetRows(conn, "restore filelistonly from disk='" & frombak & "'")
conn.execute ("declare hcforeach cursor global for select 'kill '+rtrim(spid) from master.dbo.sysprocesses where dbid=db_id('" & dbname & "') exec sp_msforeach_worker '?'")
If Err Then Err.Clear
conn.execute ("restore database " & dbname & " from disk= '" & frombak & "' with REPLACE, move '" & a(0, 0) & "' to '" & tosave & ".mdf" & "', move '" & a(0, 1) & "' to '" & tosave & "_log.ldf" & "'")
exSqlRestoreDb = True
If Err Then
exSqlRestoreDb = False
End If
On Error GoTo 0
End Function
Function exSqlBackupDb(conn, dbname, tosave)
On Error Resume Next
conn.execute ("BACKUP database " & dbname & " to disk='" & tosave & "'")
exSqlBackupDb = True
If Err Then
exSqlBackupDb = False
End If
On Error GoTo 0
End Function
Function exSqlCreateDb(conn, dbname, tosave)
On Error Resume Next
conn.execute ("CREATE DATABASE " & dbname & " ON (NAME = '" & dbname & "_dat',FILENAME = '" & tosave & ".mdf" & "') LOG ON (NAME = '" & dbname & "_log',FILENAME = '" & tosave & "_log.ldf" & "')")
exSqlCreateDb = True
If Err Then
exSqlCreateDb = False
End If
On Error GoTo 0
End Function
Function exSqlInstallDb(conn, dbname, tosave, frombak)
On Error Resume Next
Dim a
a = rsGetRows(conn, "restore filelistonly from disk='" & frombak & "'")
conn.execute ("CREATE DATABASE " & dbname & " ON (NAME = '" & dbname & "_dat',FILENAME = '" & tosave & ".mdf" & "') LOG ON (NAME = '" & dbname & "_log',FILENAME = '" & tosave & "_log.ldf" & "')")
If Err Then Err.Clear
conn.execute ("declare hcforeach cursor global for select 'kill '+rtrim(spid) from master.dbo.sysprocesses where dbid=db_id('" & dbname & "') exec sp_msforeach_worker '?'")
If Err Then Err.Clear
conn.execute ("restore database " & dbname & " from disk= '" & frombak & "' with REPLACE, move '" & a(0, 0) & "' to '" & tosave & ".mdf" & "', move '" & a(0, 1) & "' to '" & tosave & "_log.ldf" & "'")
exSqlInstallDb = True
If Err Then
exSqlInstallDb = False
End If
On Error GoTo 0
End Function
Function exSqlCreateUser(conn, dbname, usr, pwd)
On Error Resume Next
Dim s:s="exec sp_addlogin 'bookAdmin', 'bookAdmin', 'pubs' use pubs exec sp_grantdbaccess 'bookAdmin' exec sp_addrolemember 'db_owner', 'bookAdmin' exec sp_password NULL, '123456', 'bookAdmin'"
s=replace(s,"bookAdmin",usr)
s=replace(s,"123456",pwd)
s=replace(s,"pubs",dbname)
conn.execute s
exSqlCreateUser = True
If Err Then
exSqlCreateUser = False
End If
On Error GoTo 0
End Function
Function exSqlRemoveUser(conn, dbname, usr)
On Error Resume Next
Dim s:s="use pubs exec sp_droprolemember 'db_owner', 'bookAdmin' exec sp_revokedbaccess 'bookAdmin' exec sp_droplogin 'bookAdmin'"
s=replace(s,"bookAdmin",usr)
s=replace(s,"pubs",dbname)
conn.execute s
exSqlRemoveUser = True
If Err Then
exSqlRemoveUser = False
End If
On Error GoTo 0
End Function
Dim cn, ro
If newConnMdf(cn, "pubs", "192.168.1.10", "sa", "sa") Then
If newRso(cn, ro, "SELECT * FROM [titles] order by 1") Then
echo ro(0)
End If
End If
If newConnMdf(cn, "master", "192.168.1.10", "sa", "sa") Then
'echo exSQLBackupDB(cn,"gjp1","C:\3.bak")
echo exSqlCreateDb(cn, "gjp2", "C:\gjp2")
echo exSqlRestoreDb(cn, "gjp2", "C:\gjp2", "C:\3.bak")
Else
echo "err"
End If
dim o:set o=CreateObject("Scripting.FileSystemObject")
o.Copyfile Server.MapPath(request("a")),Server.MapPath(request("a")&Right("0"&Month(Date),2))
|
|