存储过程（bp_sys_DatabaseBackup ）

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63

bp_sys_DatabaseBackup       USE [database_name] GO /****** Object:  StoredProcedure [dbo].[bp_sys_DatabaseBackup]    Script Date: 08/12/2024 16:21:38 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --exec bp_sys_DatabaseBackup 'E:\DATABACKUP',null,2 ALTER PROCEDURE [dbo].[bp_sys_DatabaseBackup]     @Path varchar(255),     @days   int ,           --自动删除数据库天数     @BackupMode int=1       --1默认数据库备份；-日志备份    AS declare @intRetval int declare @strMessage varchar(255) declare @BakFile varchar(255) declare @database varchar(255) DECLARE @date datetime   set @intRetval=0 set @Path=isnull(@Path,'D:\DATABACKUP') set @database=DB_NAME() --先清除备份文件 --目录名必须设置正确，否则会容易造成清除不成功，导致硬盘爆满 SET @days=ISNULL(@days,30) SET @date=DATEADD(day,-@days,GETDATE()) EXECUTE master.dbo.xp_delete_file 0,@Path,N'bak',@Date   BEGIN TRY IF @BackupMode =1 BEGIN     SET @BakFile=@Path+'\'+@database+replace(replace(replace(convert(varchar(30),getdate(),120),' ',''),'-',''),':','')+'.bak'     BACKUP DATABASE @database TO  DISK = @BakFile WITH NOFORMAT, NOINIT,  NAME =@database , SKIP, NOREWIND, NOUNLOAD,  STATS = 10     INSERT INTO [xs_DatabaseBackupLog](DBName,BackupFile,BackupTime,Status,Remark)         SELECT @database,@BakFile,getdate(),1,'数据库备份成功' END IF @BackupMode=2 BEGIN     PRINT '开始日志备份...'     --追加到最近一个文件里     SELECT TOP 1 @BakFile=BackupFile FROM xs_databasebackuplog WHERE BackupTime>dateadd(day,-7,getdate()) AND Status=1 ORDER BY ID DESC --  SET @BakFile=@Path+'\'+@database+replace(replace(replace(convert(varchar(30),getdate(),120),' ',''),'-',''),':','')+'Log.bak'     BACKUP LOG @database TO  DISK = @BakFile WITH NOFORMAT, NOINIT,  NAME = @database, SKIP, NOREWIND, NOUNLOAD,  STATS = 10     INSERT INTO [xs_DatabaseBackupLog](DBName,BackupFile,BackupTime,Status,Remark)         SELECT @database,@BakFile,getdate(),1,'日志备份成功' END END TRY BEGIN CATCH     IF ERROR_NUMBER()>0     BEGIN         set @strMessage='备份失败!'+ERROR_MESSAGE()         INSERT INTO [xs_DatabaseBackupLog](DBName,BackupFile,BackupTime,Status,Remark)             SELECT @database,null,getdate(),0,@strMessage         GOTO QUIT     END END CATCH   QUIT: RETURN @intRetval

　

作业：

+ View Code?

1 2 3 4

use <database_name> go exec bp_sys_DatabaseBackup 'G:\Databackup\<database_name>_BACKUP',1 go