PB数据库MSSQL备份_pb execute immediate sqlcode-CSDN博客

PB数据库MSSQL备份

2010-04-06 10:42 394人阅读评论(0) 收藏举报

数据库 database file string sql disk

//-------------------------PB数据库MSSQL备份

//-------------------------建立 sle_1 控件和 cd_1按钮控件在 cd_1.clicked()事件写如下脚本!

String ls_name,ls_sql ,ls_file
int li_flag = 0,li_count

ls_file = 'ntsfgl'+string(year(today())) + string(month(today()),"00") + string(day(today()),"00")

//生成备份的数据库名 ls_file

DISCONNECT USING SQLCA;

Transaction CA_BACKUP

// --- 事先应检查数据库是否还有其他用户在使用 ---

//..................

//open (w_backup_filename) // 打开文件名选择窗口，只填写文件名，不带路径
//ls_file = Message.stringparm // 从窗口的返回值中取得文件名

if ls_file = '' then // 用户取消了

return

end if

// --- 组成备份语句 --- ntsfgl 用你自己的数据库名称要备份的数据库名称

ls_sql = "BACKUP DATABASE ntsfgl TO Disk = '" + ls_file + "' WITH INIT"

// --- 开始备份 ---

CA_BACKUP = SQLCA // SQLCA是当前的数据库连接

// 建立新连接

CONNECT USING CA_BACKUP;

// 测试连接成功与否

IF CA_BACKUP.Sqlcode <> 0 THEN

MessageBox ( "提示", "无法连接数据库! 可能的原因为：~r~n" + CA_BACKUP.SQLErrText)

li_flag = 1

// 重新恢复以前的数据库连接

CONNECT USING SQLCA;

IF SQLCA.Sqlcode <> 0 THEN // 若连以前的数据库也连接不上了

halt // 就挂掉算了

else

return // 若能连上就结束恢复过程，继续程序运行

end if

END IF

// 设为自动提交，此句很关键

CA_BACKUP.autocommit = TRUE

// 执行备份操作

execute immediate :ls_sql USING CA_BACKUP;

IF CA_BACKUP.Sqlcode <> 0 THEN

MessageBox ( "提示", "无法备份数据库! 可能的原因为：~r~n" + CA_BACKUP.SQLErrText)
li_flag = 1
ELSE

MessageBox ( '提示', '数据库成功备份到[' + ls_file + ']。' )
li_flag = 0
END IF

// 设为手动提交

// 断开连接

DISCONNECT USING CA_BACKUP;

SQLCA.autocommit = FALSE

// 重新恢复以前的数据库连接

CONNECT USING SQLCA;

IF SQLCA.Sqlcode <> 0 THEN // 以前的数据库连接不上了
MessageBox ( "提示", "无法连接数据库! 可能的原因为：~r~n" + SQLCA.SQLErrText)
halt // 结束程序运行
Else
If li_flag = 0 Then
select count() Into :li_count FROM bak_file_names Where bak_name =:ls_file Using Sqlca;
commit using sqlca ;
if li_count= 0 Then
Insert INTO bak_file_names (bak_name) values (:ls_file) Using Sqlca ;
Commit Using Sqlca;
End IF
End If
END IF

//-------------------------------------------- 恢复

//----------------------------------------------- 建立 ddlb_1 和 cb_2 控件

//-----------------------------------------------在窗口的open事件写写

String ls_name
declare xsdwda cursor for
SELECT bak_name FROM bak_file_names ; //bak_file_names 保存每次备份的文件名放在字段 bak_name 中
open xsdwda;
//取得游标值
do while sqlca.sqlcode = 0
fetch xsdwda into :ls_name ;
if sqlca.sqlcode = 0 then
ddlb_1.additem(ls_name)
end if
loop
close xsdwda;

sle_1.text = 'ntsfgl'+string(year(today())) + string(month(today()),"00") + string(day(today()),"00")

------------------------------------------------在 cd_2.clicked()事件写如下脚本!

string ls_sql ,path
string ls_file ,s_exec
string ls_database
double str_tmp1

ls_database = SQLCA.Database      //原数据库信息
DISCONNECT USING SQLCA;   //断开现数据库连接
Transaction tr_sqlca                          //定义一个新事务
tr_sqlca=create transaction                //建立一个新事务

// Profile master
tr_sqlca.DBMS = "OLE DB" //
tr_sqlca.LogPass = "1" //你的数据库密码
tr_sqlca.LogId = "sa" //你的数据库登录ID

tr_sqlca.AutoCommit = True
tr_sqlca.DBParm = "PROVIDER='SQLOLEDB',DATASOURCE='ZHIDASOF-SERVER',PROVIDERSTRING='database=master'"

SetPointer(HourGlass!)//---开始恢复
Connect using tr_sqlca ;
ls_file = 'ntsfgl'                           //要恢复的数据库名称
path = ddlb_1.text
declare g_cur cursor for   select spid from dbo.sysprocesses where dbid=db_id(:ls_file) using tr_sqlca;
OPEN   g_cur;

    integer pid
    do while true
    fetch g_cur into :pid ;     //get the process id
         if tr_sqlca.sqlcode=100 or tr_sqlca.sqlcode=-1 then exit

//if the loop can get next item
if pid>50 then

   // when pid great than 50 means it is a user process,we can't kill system process
           ls_sql = "kill "+string(pid)
            EXECUTE IMMEDIATE :ls_sql USING tr_sqlca;

           //use inline sql to kill the process (as mean,to disconnect it)
         end if
    loop
    close g_cur;

    ls_sql = "EXEC sp_addumpdevice 'disk' "+" , 'coolnan' "+","+" '"+path+"' "
    EXECUTE IMMEDIATE :ls_sql USING tr_sqlca;
    ls_sql = "use master RESTORE DATABASE "+ ls_file+" FROM DISK = "+" '"+path+"' "
    EXECUTE IMMEDIATE :ls_sql USING tr_sqlca;

//s_exec = "restore database ntsfgl from disk = '" + ls_file + "' with replace"
//execute immediate :s_exec using tr_sqlca;
MessageBox ( '提示', '数据库从[' + ls_file + ']恢复成功。' )

// 设为手动提交

sqlca.AutoCommit = FALSE

// 断开连接

DISCONNECT USING tr_sqlca;

SQLCA.Database = ls_database // 恢复以前的数据库
// 重新恢复以前的数据库连接

CONNECT USING SQLCA;

SetPointer(HourGlass!)

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/hczdpc/archive/2008/08/30/2851098.aspx