备份数据库存储过程

由于使用SSMS创建的维护计划中，完整备份的话，会打断别的备份的顺序链，而由于管理原因，往往需要有几套备份计划，所以经过时间，本人编写了一个存储过程实现我的想法。不管你用不用，反正我用了。

首先，创建一个表，用于记录备份信息。因为用DMV和系统视图的话往往要编写很多东西，对于一般人很难记住那么多表。所以使用一个表来记录更加方便。以前之前已经创建了一个库 AuditDB，用于监控数据库的DDL操作，所以这里没有再编写建库脚本。只是在这个库里面创建一个表。

USE AuditDB GO IF OBJECT_ID('BackupHistory') IS NOT NULL DROP TABLE BackupHistory GO CREATE TABLE BackupHistory ( DatabaseName NVARCHAR(128),--数据库名 CreateDate DATETIME,--创建时间 Compatibilitylevel TINYINT,--数据库兼容级别 RecoveryModel CHAR(10),--恢复模式 BackupStartData DATETIME, --备份开始时间 BackupEndData DATETIME, --备份结束时间 BackupSpace NVARCHAR(128), --备份文件大小 Operator NVARCHAR(128),--执行操作者 Servername NVARCHAR(128), --服务器名称 [FILENAME] NVARCHAR(256) --备份文件名 );
建了表之后，在msdb或者AuditDB中创建这个存储过程。一般不要在客户数据中创建。

/* 注意：由于用GUI备份会导致备份链中断，而作业无法实现“仅复制备份”，所以使用脚本备份 create by huangzj 20120510 EXEC Backup_By_DBA 'auditdb','e:\新建文件夹\' */ ALTER PROC Backup_By_DBA ( @dbname NVARCHAR(128), @bakpath NVARCHAR(128)='E:\' ) AS --不备份系统表 IF @dbname IN ( 'master', 'msdb', 'model', 'tempdb' ) BEGIN RETURN END ELSE BEGIN --定义备份时间，精确到秒 DECLARE @date NVARCHAR(64) SELECT @date = SUBSTRING(CONVERT(VARCHAR(20), GETDATE(), 121), 1, 4) + '_' + SUBSTRING(CONVERT(VARCHAR(20), GETDATE(), 121), 6, 2) + '_' + SUBSTRING(CONVERT(VARCHAR(20), GETDATE(), 121), 9, 2) + '_' + CONVERT(CHAR(2), DATEPART(hh, GETDATE())) + CONVERT(CHAR(2), DATEPART(mi, GETDATE())) + CONVERT(CHAR(2), DATEPART(ss, GETDATE())) --定义要备份的数据库名 DECLARE @db NVARCHAR(20) SET @db = '' + '' + <span style="background-color: rgb(255, 255, 255); ">@dbname + '' + ''</span> --定义备份文件的全名 DECLARE @bakname NVARCHAR(128) SELECT @bakname = @db + '_' + @date --定义备份存放路径 DECLARE @disk NVARCHAR(256) SELECT @disk = @bakpath + @bakname + '.bak' --定义备份描述 DECLARE @name NVARCHAR(128) SELECT @name = @db + '-完整 数据库 备份' --定义错误信息 DECLARE @error NVARCHAR(128) SELECT @error = '验证失败。找不到数据库“' + @db + '”的备份信息。' BACKUP DATABASE @db TO DISK = @disk WITH COPY_ONLY, NOFORMAT, NOINIT, NAME =@name, SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM ; DECLARE @backupSetId AS INT SELECT @backupSetId = position FROM msdb..backupset WHERE database_name = @db AND backup_set_id = ( SELECT MAX(backup_set_id) FROM msdb..backupset WHERE database_name = @db ) IF @backupSetId IS NULL BEGIN RAISERROR(@error, 16, 1) END RESTORE VERIFYONLY FROM DISK = @disk WITH FILE = @backupSetId, NOUNLOAD, NOREWIND INSERT INTO AuditDB.dbo.BackupHistory(DatabaseName,CreateDate,Compatibilitylevel,RecoveryModel,BackupStartData,BackupEndData,BackupSpace,Operator,Servername,[FILENAME]) SELECT a.[name] ,create_date,a.[compatibility_level] ,recovery_model_desc,S.backup_start_date,S.backup_finish_date,CONVERT(VARCHAR(20),CONVERT(DECIMAL(10,2),S.compressed_backup_size/(1024*1024)))+'MB', S.[user_name],S.[server_name],physical_device_name FROM sys.databases a INNER JOIN msdb.dbo.backupset S ON a.name=S.database_name Inner Join msdb.dbo.backupmediafamily M ON S.media_set_id =M.media_set_id WHERE a.name=db_name() AND physical_device_name=@disk END 备注：此处只是完整备份的例子，如果需要别的备份，那么要修改脚本。