PB中对SQL SERVER 2K 数据库的创建、备份与恢复

港控/mmm° 2022-06-01 10:25 195阅读 0赞

一  共用函数

1. gf\_check\_sqlserver

//\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*//  
// Function:  gf\_check\_sqlserver  
//      Description:判断是否安装SQL是否已经启动，如果没有启动则做相应处理  
//--------------------------------------------------------------------  
// Return:   integer lo\_SqlServer.status:  
//        0 未知状态  
//        1 已经启动  
//        2 SQL Server为暂停状态  
//        3 SQL Server为停止状态  
//        4 SQLSERVER正在启动(关闭-->启动)  
//        5 SQLSERVER正在关闭  
//        6 SQLSERVER正在启动(暂停-->启动)  
//        7 SQLSERVER正在暂停(启动-->暂停)  
//--------------------------------------------------------------------  
// Arguments: (无)  
//--------------------------------------------------------------------  
// Author:   Liu xj   Date: 2005.12.11  
//\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*//

oleobject lo\_SqlServer  
string  ls\_servername  
string  ls\_UserName ='sa'  
string  ls\_password=''  
integer  li\_return

ls\_servername = ProfileString("user.ini","Profile","ServerName","192.168.1.2")  
lo\_SqlServer = create oleobject  
li\_return  = lo\_SqlServer.connecttonewobject("sqldmo.sqlserver")

if li\_return  = 0 then //连接成功  
lo\_SqlServer.name = ls\_servername  
lo\_SqlServer.logintimeout  = 10  
//lo\_SqlServer.loginsecure = true         //以nt方式连接  
lo\_SqlServer.loginsecure   = false        //以sql server方式连接  
  
Try  
    li\_return = lo\_SqlServer.status  
   Catch(RunTimeError rte)  
    messagebox('提示',"系统没有安装SQLServer",StopSign!)  
   End try  
  
Choose case li\_return   
  case 1   //已经启动  
   //messagebox('提示',ls\_ServerName+' 已经启动SQLSERVER服务器')  
   //lo\_SqlServer.stop() //停止SQLSERVER  
  case 2   //SQL Server为暂停状态  
   lo\_SqlServer.Continue()  
   messagebox('提示', '服务器已暂停，正在启动'+ ls\_servername + '上的SQL Server ...    ~r~n~r~n 请稍后再试。')    
  case 3  //SQL Server为停止状态  
   lo\_SqlServer.Start(false,ls\_servername,ls\_username,ls\_password)  
   messagebox('提示', '服务器已停止，正在启动'+ ls\_servername + '上的SQL Server ...    ~r~n~r~n 请稍后再试。')    
  case 4  //SQLSERVER正在启动(关闭-->启动)  
   messagebox('提示', ls\_servername + '上的SQL Server 正在启动 (关闭-->启动)')  
  case 5  //SQLSERVER正在关闭  
   messagebox('提示', ls\_servername + '上的SQL Server 正在关闭')  
  case 6  //SQLSERVER正在启动(暂停-->启动)  
   messagebox('提示', ls\_servername + '上的SQL Server 正在启动 (暂停-->启动)')  
  case 7 //SQLSERVER正在暂停(启动-->暂停)  
   messagebox('提示', ls\_servername + '上的SQL Server 正在暂停')  
  case 0 //未知状态  
   messagebox('提示', '未知'+ls\_servername + '上的SQL Server 状态')  
End Choose  
else  
destroy(lo\_SqlServer)  
messagebox('系统提示',"系统未安装Sql Sserver ！")  
return 0  
end if  
destroy(lo\_SqlServer)  
return li\_return

二  创 建 数 据 库

integer li\_count  
string ls\_sql  
string ls\_dbpath  
uno\_des luno\_des  
if gf\_check\_sqlserver() <> 1 then return

if not wf\_check\_input() then return  
SetPointer(HourGlass!)  
timer(1)  
// Profile master  
itrans\_master = create Transaction  
itrans\_master.DBMS = "MSS Microsoft SQL Server"  
itrans\_master.Database = 'master'  
itrans\_master.LogPass = is\_logpass  
itrans\_master.ServerName = is\_servername  
itrans\_master.LogId = is\_logid  
itrans\_master.AutoCommit = True  
SQLCA.DBParm = ""

connect using itrans\_master ;  
if itrans\_master.sqlcode <> 0 then  
messagebox("系统提示",itrans\_master.sqlerrtext)  
goto Error  
end if

//\*\*\*\*\*\*\*\*\*\*\*\* 取服务器网卡地址  
select top 1 net\_address into :is\_servermacadd from sysprocesses where dbid=db\_id('master') order by login\_time DESC using itrans\_master;  
if isnull(is\_servermacadd) or (len(is\_servermacadd) <> 12) then  
messagebox("系统提示",'请确认网络连接正常，然后重试。')  
goto Error  
else  
is\_servermacadd = mid(is\_servermacadd,3,2) + right(is\_servermacadd,6)  
is\_servermacadd = luno\_des.uf\_jiami(is\_servermacadd)  
//messagebox('is\_servermacadd',is\_servermacadd)  
end if

hpb\_1.position = 10  
创建一个空库  
SELECT count(1) into :li\_count FROM sysdatabases WHERE name = :is\_database using itrans\_master ;

if li\_count > 0 then  
messagebox('系统提示','数据库已存在 ！')  
//goto Error  
ls\_sql = "DROP DATABASE " + is\_database  
EXECUTE IMMEDIATE :ls\_sql USING itrans\_master ;  
if itrans\_master.sqlcode <> 0 then  
  messagebox("系统提示",itrans\_master.sqlerrtext)  
  goto Error  
end if  
end if

hpb\_1.position = 30  
ls\_dbpath = is\_currentdirectory + '/data/'  
//ls\_sql = " CREATE DATABASE " + is\_database + " ON (NAME = '" + is\_database + "\_data' , FILENAME = '" + ls\_dbpath + is\_database + "\_data.mdf' , SIZE = 10, FILEGROWTH = 10%) LOG ON (NAME = '" + is\_database + "\_log' , FILENAME = '" + ls\_dbpath + is\_database + "\_log.ldf' , SIZE = 5, FILEGROWTH = 10%) "  
ls\_sql = " CREATE DATABASE " + is\_database  
EXECUTE IMMEDIATE :ls\_sql USING itrans\_master ;  
if itrans\_master.sqlcode <> 0 then  
messagebox("系统提示",itrans\_master.sqlerrtext)  
goto Error  
end if

hpb\_1.position = 60  
//\*\*\*\*\*\*\*\*\*\*\*\*\* 检测连接状况 \*\*\*\*\*\*\*\*\*\*\*\*//  
/\*select spid into :li\_count from sysprocesses where dbid=db\_id(:is\_database) using itrans\_master;  
if li\_count > 0 then  
//messagebox("系统提示",'数据库有其他用户连接，无法恢复。~r~n~r~n请在数据库空闲时再重试。')  
//goto Error  
end if  
\*/  
恢复数据库  
ls\_sql = " RESTORE DATABASE  " + is\_database + " FROM DISK = '" + is\_dbbakfile + "' WITH RECOVERY "  
EXECUTE IMMEDIATE :ls\_sql USING itrans\_master ;  
if itrans\_master.sqlcode <> 0 then  
messagebox("系统提示",itrans\_master.sqlerrtext)  
goto Error  
end if  
hpb\_1.position = 90

ls\_sql = "update " + is\_database + "..gy\_constant set zhi = '" + is\_servermacadd + "' where mc = 'servermacadd'"  
EXECUTE IMMEDIATE :ls\_sql USING itrans\_master ;  
if itrans\_master.sqlcode <> 0 then  
messagebox("系统提示",itrans\_master.sqlerrtext)  
goto Error  
end if

disconnect using itrans\_master ;  
destroy itrans\_master  
hpb\_1.position = 100  
timer(0)  
SetPointer(Arrow!)  
messagebox("系统提示","数据库创建完成。")

return

Error:  
disconnect using itrans\_master ;  
destroy itrans\_master  
hpb\_1.position = 1  
SetPointer(Arrow!)  
timer(0)  
return

三  备份数据库

string ls\_sql  
//string ls\_dbpath  
if gf\_check\_sqlserver() <> 1 then return  
  
if not wf\_check\_input() then return  
SetPointer(HourGlass!)  
timer(1)  
  
// Profile master  
itrans\_master = create Transaction  
itrans\_master.DBMS = "MSS Microsoft SQL Server"  
itrans\_master.Database = 'master'  
itrans\_master.LogPass = is\_logpass  
itrans\_master.ServerName = 'liuxj'  
itrans\_master.LogId = is\_logid  
itrans\_master.AutoCommit = True  
SQLCA.DBParm = ""  
  
connect using itrans\_master ;  
if itrans\_master.sqlcode <> 0 then  
messagebox("系统提示",itrans\_master.sqlerrtext)  
goto Error  
end if  
  
hpb\_1.position = 10  
//\*\*\*\*\*\*\*\*\*\*\*\*\* 备份数据库 \*\*\*\*\*\*\*\*\*\*\*\*\*//  
ls\_sql="BACKUP DATABASE " + is\_database + " to disk='" + is\_dbbakfile + "'"  
EXECUTE IMMEDIATE :ls\_sql USING itrans\_master ;  
  
if itrans\_master.sqlcode <> 0 then  
messagebox("备份数据库错误：",itrans\_master.sqlerrtext)  
goto Error  
end if  
  
hpb\_1.position = 90  
disconnect using itrans\_master ;  
destroy itrans\_master  
  
is\_DBBackPath = left(is\_dbbakfile,lastpos(is\_dbbakfile,'/'))  
  
hpb\_1.position = 100  
SetPointer(Arrow!)  
timer(0)  
messagebox("系统提示",space(20) + "数据库备份完毕。~r~n~r~n备份到下面文件： ~r~n" + is\_dbbakfile)  
return  
  
Error:  
disconnect using itrans\_master ;  
destroy itrans\_master  
hpb\_1.position = 1  
timer(0)  
SetPointer(Arrow!)  
return

四  恢复数据库

integer li\_count  
string ls\_sql  
string ls\_dbpath  
if gf\_check\_sqlserver() <> 1 then return

if not wf\_check\_input() then return  
SetPointer(HourGlass!)  
timer(1)

// Profile master  
itrans\_master = create Transaction  
itrans\_master.DBMS = "MSS Microsoft SQL Server"  
itrans\_master.Database = 'master'  
itrans\_master.LogPass = is\_logpass  
itrans\_master.ServerName = is\_servername  
itrans\_master.LogId = is\_logid  
itrans\_master.AutoCommit = True  
SQLCA.DBParm = ""

connect using itrans\_master ;  
if itrans\_master.sqlcode <> 0 then  
messagebox("系统提示",itrans\_master.sqlerrtext)  
goto Error  
end if

hpb\_1.position = 10

//\*\*\*\*\*\*\*\*\*\*\*\*\* 检测连接状况 \*\*\*\*\*\*\*\*\*\*\*\*//  
select spid into :li\_count from sysprocesses where dbid=db\_id(:is\_database) using itrans\_master;  
if li\_count > 0 then  
messagebox("系统提示",'数据库有其他用户连接，无法恢复。~r~n~r~n请在数据库空闲时再重试。')  
goto Error  
end if

//\*\*\*\*\*\*\*\*\*\*\*\*\* 恢复数据库 \*\*\*\*\*\*\*\*\*\*\*\*\*//  
ls\_sql = " RESTORE DATABASE  " + is\_database + " FROM DISK = '" + is\_dbbakfile + "' WITH RECOVERY "  
EXECUTE IMMEDIATE :ls\_sql USING itrans\_master ;  
if itrans\_master.sqlcode <> 0 then  
messagebox("恢复数据库错误：",itrans\_master.sqlerrtext)  
goto Error  
end if  
hpb\_1.position = 90

disconnect using itrans\_master ;  
destroy itrans\_master  
hpb\_1.position = 100  
SetPointer(Arrow!)  
timer(0)  
messagebox("系统提示","数据库恢复完毕。")  
return

Error:  
disconnect using itrans\_master ;  
destroy itrans\_master  
hpb\_1.position = 1  
SetPointer(Arrow!)  
timer(0)  
return