//-------------------------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