备份数据库存储过程

原文: 备份数据库存储过程

由于使用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 = '' + '' + 
    
      @dbname + '' + ''
    
    

--定义备份文件的全名

        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
  

 备注：此处只是完整备份的例子，如果需要别的备份，那么要修改脚本。

  

    
      备份数据库存储过程