CREATEPROCEDURE dbo.db_backup2pc @databaseNamenvarchar(100) =null, --数据库名 @filepathnvarchar(125) =null--文件保存路径(IP+隐藏共享+) AS BEGIN DECLARE @year1varchar(4), @month1varchar(2), @day1varchar(2), @flagvarchar(255), @proc_resulttinyint, /**//*返回系统存储过程xp_cmdshell运行结果*/ @sqlstrvarchar(2000), @createdirvarchar(255) /**//*建立文件备份的目录*/ begin -- Get year & month &day fromat of the day before yesterday -- SET @year1 = substring(convert(varchar,datepart(yyyy,getdate()-2)),3,2) SET@month1=substring(convert(varchar,datepart(mm,getdate())),1,2) SET@day1=substring(convert(varchar,datepart(dd,getdate())),1,2) -- if len(@month1)<2 set @month1 = '0' + @month1 /* 不足两位的前面加零 */ -- if len(@day1)<2 set @day1 = '0' + @day1 set@sqlstr='dir '+@filepath+@databaseName+'_'+@month1+'-'+@day1 EXEC@proc_result= master..xp_cmdshell @sqlstr,no_output if (@proc_result<>0) /**//*系统存储过程xp_cmdshell返回代码值:0(成功)或1(失败)*/ begin set@createdir='md '+@filepath+@databaseName+'_'+@month1+'-'+@day1/**//*建立文件备份的目录*/ EXEC master..xp_cmdshell @createdir,no_output set@sqlstr='backup database '+@databaseName+' to disk='''+@filepath+@databaseName+'_'+@month1+'-'+@day1+''+@databaseName+'.bak'''--+' with init' Execute (@sqlstr) /**//* 备份databaseName数据 */ set@sqlstr='backup database master to disk='''+@filepath+@databaseName+'_'+@month1+'-'+@day1+'master.bak''' Execute (@sqlstr) /**//* 备份 master 数据 */ set@sqlstr='backup database msdb to disk='''+@filepath+@databaseName+'_'+@month1+'-'+@day1+'msdb.bak''' Execute (@sqlstr) /**//* 备份 msdb 数据 */ set@flag='Backup database successful.' end else set@flag='The directory "'+@databaseName+'_'+@month1+'-'+@day1+'" has been in, backup database unsuccessful.' print@flag END END GO