使用存储过程自动备份数据库

一、创建存储过程完全备份数据库

USE [某某数据库]
GO
/****** Object: StoredProcedure 某某数据库 Script Date: 2023/4/20 9:30:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE 完全备份某某数据库
AS
BEGIN
SET NOCOUNT ON;

DECLARE @backupName NVARCHAR(255);
DECLARE @backupFile NVARCHAR(255);

SET @backupName = N'WebDB Backup ' + CONVERT(NVARCHAR(20), GETDATE(), 112) + ' ' + REPLACE(CONVERT(NVARCHAR(20), GETDATE(), 108), ':', '');
SET @backupFile = N'd:\SQLBackups\' + @backupName + N'.bak';

BACKUP DATABASE [某某数据库]
TO DISK = @backupFile
WITH INIT, COMPRESSION, STATS = 10;

PRINT 'WebDB has been backed up to ' + @backupFile;
END

如果报错，出现操作系统错误 3(系统找不到指定的路径）。请确保SQL Server服务帐户（例如，NT Service\MSSQLSERVER）具有访问备份文件夹的权限。您可以在文件夹属性中设置权限。

二、差异备份数据库

USE [某某数据库]
GO
/****** Object: StoredProcedure [dbo].[DiffBackupWebDB] Script Date: 2023/4/20 9:38:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[DiffBackupWebDB]
--根据日期自动备份数据库
AS
DECLARE @strPath NVARCHAR(200)
set @strPath = convert(NVARCHAR(19),getdate(),120)
set @strPath = REPLACE(@strPath, '-' , '')
set @strPath = REPLACE(@strPath, ':' , '')
set @strPath = REPLACE(@strPath, ' ' , '')
set @strPath = 'E:\DBBackup\WebDB\Diff\' + 'WebDB_'+@strPath + '.bak'

BACKUP DATABASE [某某数据库]
TO DISK = @strPath WITH INIT
,NOUNLOAD
,DIFFERENTIAL
,NAME = N'WebDB 差异备份'
,NOSKIP
,COMPRESSION
,STATS = 10
,NOFORMAT