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