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

一  共用函数

      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