SqlServer数据库备份存储过程.doc
文本预览下载声明
SqlServer数据库备份存储过程:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER proc [dbo].[p_backupdb]
@bkpath nvarchar(260)=,
@temppath nvarchar(4000)= output,
@flag nvarchar(260)= output
AS
declare @dbname nvarchar(260)
set @dbname = WebDt30
declare @bkfname nvarchar(260)
set @bkfname =
declare @filepath nvarchar(4000)
set @filepath =
declare @sql nvarchar(4000)
set @sql =
declare @temp nvarchar(4000)
set @temp =
declare @mdpath nvarchar(4000)
set @mdpath =
--得到当前数据库的数据文件路径
select @filepath=ltrim(filename) from sysfiles
set @temppath=subString(@filepath,0,CHARINDEX(\,@filepath)+1)+Backup
if right(@temppath,1)\ set @temppath=@temppath+
set @bkfname=convert(char(10),getdate(),21)
set @mdpath = md + @temppath
--判断配置文件的文件路径是否存在
if exists (select *
from sysobjects
where type=u
and
name = temp_xp_fileexist)
begin
drop table temp_xp_fileexist
end
create table temp_xp_fileexist(a bit,b bit,c bit)
insert into temp_xp_fileexist
exec master.dbo.xp_fileexist @bkpath
--如果不存在
IF EXISTS(SELECT 1 FROM temp_xp_fileexist WHERE b=0)
BEGIN
set @sql=backup database +@dbname
+ to disk= +@temppath+WebDt30+@bkfname+.bak+
set @flag=flase
exec master.dbo.xp_cmdshell @mdpath
exec(@sql)
END
--存在
ELSE IF EXISTS(SELECT 1 FROM temp_xp_fileexist WHERE b=1)
BEGIN
if right(@bkpath,1)\ set @bkpath=@bkpath+ set @flag=true
set @sql=backup database +@dbname
+ to disk= +@bkpath+WebDt30+@bkfname+.bak+
exec(@sql)
END
drop table temp_xp_fileexist
java中：
调用此存储过程有三个参数：一个输入，两个输出
cst = con.prepareCall(
{call dbo.p_backupdb(?,?,?)});
cst.setString(1, filepath);
cst.registerOutParameter(2, java.sql.Types.VARCHAR);
cst.registerOutParameter(3, java.sql.Types.VARCHAR);
存储过程中输入参数filepath为nvarchar型，java中setString
输出参数 temppath为nvarchar型，java中注册输出参数是Types为VARCHAR
flag 为nvarchar型，java中注册输出参数是Types为VARCHAR
显示全部