在PB8.0中如何实现SQL Server 2000的数据库备份？谢谢，急……

[gaojian72]

在PB8.0中如何实现SQL Server 2000的数据库备份？谢谢，急……
我每次操作都报错：‘不能在事务中执行数据库备份和恢复工作’，哪位高手指点指点，谢谢，小弟感激不禁！

[gaojian72]

老大们，帮帮忙，谢谢！

[ys333]

新手，不懂

[lzheng2001]

1,是否有权限
2,试一下运行备份语句前加一句SQLCA.AUTOCOMMIT = TRUE,备份完后设成FALSE
3,搜索以前的贴,应该有很多这样的贴子

[junly1981]

这是我用的，你参考一下吧：
一、数据备份
String ls_taskname,ls_server,ls_username,ls_passwd,ls_saveto,ls_context
String ls_database,ls_statement
string ls_msg,ls_savepath,ls_use
int li_net
string ls_dbms,ls_dbparm,ls_regpath


//从注册表读出连接信息并连接数据库
string dbms,database,servername,logid,logpass,autocommit,dbparm
RegistryGet("HKEY_LOCAL_MACHINE\SOFTWARE\EI_hotel","DBMS",RegString!,dbms)
RegistryGet("HKEY_LOCAL_MACHINE\SOFTWARE\EI_hotel","Database",RegString!,database)
RegistryGet("HKEY_LOCAL_MACHINE\SOFTWARE\EI_hotel","servername",RegString!,servername)
RegistryGet("HKEY_LOCAL_MACHINE\SOFTWARE\EI_hotel","logid",RegString!,logid)
RegistryGet("HKEY_LOCAL_MACHINE\SOFTWARE\EI_hotel","logpass",RegString!,logpass)
RegistryGet("HKEY_LOCAL_MACHINE\SOFTWARE\EI_hotel","autocommit",RegString!,autocommit)
RegistryGet("HKEY_LOCAL_MACHINE\SOFTWARE\EI_hotel","dbparm",RegString!,dbparm)

ls_server =servername
ls_username =logid
ls_passwd = Decrypt(logpass)
ls_saveto = sle_backpath.text
ls_database = database


if isnull(ls_saveto) or ls_saveto = '' then
messagebox('提示信息!','请先选择数据库备份路径。')
return 0
end if

if isnull(ls_database) or ls_database = '' then
messagebox('提示信息!','请先选择数据库名称。')
return 0
end if

disconnect using sqlca;

sqlca.DBMS = "MSS Microsoft SQL Server 2000"
sqlca.Database = 'master'
sqlca.ServerName = ls_server
sqlca.LogId = ls_username
sqlca.LogPass = ls_passwd
sqlca.AutoCommit = true
sqlca.DBParm = ""

connect using sqlca;

if sqlca.sqlcode <> 0 then
messagebox('提示信息!','您输入的参数有误，无法连接到数据库。')
return -1
end if

IF FileExists ( ls_saveto ) THEN //检查指定的文件是否存在
li_net=Messagebox('系统提示','系统发现文件"'+ls_saveto+'"已经存在.~r~n~n要替换它吗?',Question!,YesNo!)
IF li_net= 1 THEN
FileDelete ( ls_saveto ) //删除指定的文件
else
disconnect using sqlca;
SQLCA.DBMS = "MSS Microsoft SQL Server"
sqlca.Database = ls_database
sqlca.ServerName = ls_server
sqlca.LogId = ls_username
sqlca.LogPass = ls_passwd
sqlca.AutoCommit = true
sqlca.DBParm = ""
connect using sqlca;
return
END IF
END IF
//设置鼠标指针
SetPointer(HourGlass!)//---开始备份---
st_3.visible=true

This.Enabled = FALSE
ls_use = 'EXEC' + '(' + "'" + ls_database + SQLCA.Database + "'" + ')' //crm为数据库名
EXECUTE IMMEDIATE :ls_use ;
SQLCA.AutoCommit = True
//指定设备的逻辑名称
ls_use = "EXEC sp_addumpdevice 'disk' , 'coolnan' , '" + ls_saveto + "'" //数据库备份设备的创建
EXECUTE IMMEDIATE : ls_use ;
ls_use ='BACKUP DATABASE ' + ls_database + ' TO coolnan' //crm为数据库名称
EXECUTE IMMEDIATE : ls_use ;
IF SQLCA.SQLCODE = -1 THEN
Messagebox("系统信息","数据库备份失败!~r~n~n请检查路径正确与否。",Stopsign!)
this.enabled = TRUE
st_3.visible=false
sqlca.sqlcode = 0
sqlca.sqldbcode = 0
ls_use = "EXEC sp_dropdevice 'coolnan'"
EXECUTE IMMEDIATE : ls_use ;
disconnect using sqlca;
SQLCA.AutoCommit = false
SQLCA.DBMS = "MSS Microsoft SQL Server"
sqlca.Database = ls_database
sqlca.ServerName = ls_server
sqlca.LogId = ls_username
sqlca.LogPass = ls_passwd
sqlca.AutoCommit = true
sqlca.DBParm = ""
connect using sqlca;
RETURN
END IF
ls_use = "EXEC sp_dropdevice 'coolnan'"
EXECUTE IMMEDIATE : ls_use ;
SetPointer(Arrow!)
IF SQLCA.SQLCODE >= 0 THEN
Messagebox("系统信息","备份完成! ")
st_3.visible=false
This.enabled = TRUE
SQLCA.AutoCommit = false
ls_use = "EXEC sp_dropdevice 'coolnan'"
EXECUTE IMMEDIATE : ls_use ;
disconnect using sqlca;
SQLCA.AutoCommit = false
SQLCA.DBMS = "MSS Microsoft SQL Server"
sqlca.Database = ls_database
sqlca.ServerName = ls_server
sqlca.LogId = ls_username
sqlca.LogPass = ls_passwd
sqlca.AutoCommit = true
sqlca.DBParm = ""
connect using sqlca;
RETURN
else
disconnect using sqlca;
SQLCA.DBMS = "MSS Microsoft SQL Server"
sqlca.Database = ls_database
sqlca.ServerName = ls_server
sqlca.LogId = ls_username
sqlca.LogPass = ls_passwd
sqlca.AutoCommit = true
sqlca.DBParm = ""
connect using sqlca;
//记录操作日志
Ei_Log(2,login_ID,LoginInfo_ID,'数据备份',0,'数据备份')
//////////////
messagebox("消息","数据备份完成!")
END IF
////////////////////////

[junly1981]

二、数据恢复：
String ls_taskname,ls_server,ls_username,ls_passwd,ls_saveto,ls_context
String ls_database,ls_statement
string ls_msg,ls_savepath,ls_use
int li_net
string ls_dbms,ls_dbparm,ls_regpath


//从注册表读出连接信息并连接数据库
string dbms,database,servername,logid,logpass,autocommit,dbparm
RegistryGet("HKEY_LOCAL_MACHINE\SOFTWARE\EI_hotel","DBMS",RegString!,dbms)
RegistryGet("HKEY_LOCAL_MACHINE\SOFTWARE\EI_hotel","Database",RegString!,database)
RegistryGet("HKEY_LOCAL_MACHINE\SOFTWARE\EI_hotel","servername",RegString!,servername)
RegistryGet("HKEY_LOCAL_MACHINE\SOFTWARE\EI_hotel","logid",RegString!,logid)
RegistryGet("HKEY_LOCAL_MACHINE\SOFTWARE\EI_hotel","logpass",RegString!,logpass)
RegistryGet("HKEY_LOCAL_MACHINE\SOFTWARE\EI_hotel","autocommit",RegString!,autocommit)
RegistryGet("HKEY_LOCAL_MACHINE\SOFTWARE\EI_hotel","dbparm",RegString!,dbparm)

ls_server =servername
ls_username =logid
ls_passwd = Decrypt(logpass)
ls_saveto = sle_repath.text
ls_database = database

if isnull(ls_saveto) or ls_saveto = '' then
messagebox('提示信息!','请先选择数据库备份文件。')
return 0
end if

if isnull(ls_database) or ls_database = '' then
messagebox('提示信息!','请先选择数据库名称。')
return 0
end if

//设置鼠标指针
SetPointer(HourGlass!)//---开始备份---
st_3.visible=true
This.Enabled = FALSE

ls_statement = ' restore database '+ ls_database + " from disk = '" + ls_saveto + "' WITH REPLACE "
disconnect using sqlca;
sqlca.DBMS = "MSS Microsoft SQL Server 2000"
sqlca.Database = 'master'
sqlca.ServerName = ls_server
sqlca.LogId = ls_username
sqlca.LogPass = ls_passwd
sqlca.AutoCommit = true
sqlca.DBParm = ""
connect using sqlca;
if sqlca.sqlcode <> 0 then
messagebox('提示信息!','您输入的参数有误，无法连接到数据库。')
SetPointer(Arrow!)
This.Enabled = TRUE
disconnect using sqlca;
SQLCA.DBMS = "MSS Microsoft SQL Server"
sqlca.Database = ls_database
sqlca.ServerName = ls_server
sqlca.LogId = ls_username
sqlca.LogPass = ls_passwd
sqlca.AutoCommit = true
sqlca.DBParm = ""
connect using sqlca;
st_3.visible=false
return -1
end if

EXECUTE IMMEDIATE :ls_statement using sqlca;

IF sqlca.SqlCode = -1 THEN
messagebox('无法还原!',sqlca.SQLErrText)
ROLLBACK USING sqlca;
SetPointer(Arrow!)
This.Enabled = TRUE
disconnect using sqlca;
SQLCA.DBMS = "MSS Microsoft SQL Server"
sqlca.Database = ls_database
sqlca.ServerName = ls_server
sqlca.LogId = ls_username
sqlca.LogPass = ls_passwd
sqlca.AutoCommit = true
sqlca.DBParm = ""
connect using sqlca;
st_3.visible=false
return -1
end if

This.Enabled = TRUE
COMMIT USING sqlca;
disconnect using sqlca;
SQLCA.DBMS = "MSS Microsoft SQL Server"
sqlca.Database = ls_database
sqlca.ServerName = ls_server
sqlca.LogId = ls_username
sqlca.LogPass = ls_passwd
sqlca.AutoCommit = true
sqlca.DBParm = ""
connect using sqlca;
//记录操作日志
Ei_Log(2,login_ID,LoginInfo_ID,'数据恢复',0,'数据恢复')
//////////////
messagebox("消息","数据恢复成功!")
st_3.visible=false
return 1

[chgjszj]

不错
！

[yewenlin]

帮你顶吧。

[yangxingbo]

写个存储过程　调用　

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE up_backup_database AS

declare @Backup_file char(40)
declare @flag int
declare @rows int

set @flag = day(getdate()) % 2
if @flag = 1
begin
set @Backup_file = 'D_数据库名_单日备份'
print '数据库名_单日备份'
end
else
begin
set @Backup_file = 'D_数据库名_双日备份'
print '数据库名_双日备份'
end

checkpoint
dbcc checkdb
dbcc checkalloc
dbcc checkcatalog
backup database 数据库名 to @Backup_file with init

select @rows = count(backup_device) from ut_database_backup_log
where backup_device = @backup_file
if @rows = 0
insert ut_database_backup_log values (getdate(), '数据库名', @backup_file, null)
else
update ut_database_backup_log set backup_date = getdate() where backup_device = @backup_file

select * from ut_database_backup_log


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

[guojing590]

太棒了，谢谢诸位了。偶刚好也需要