sqlserver数据库备份

 

1，存储过程（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
　
2作业：

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