代码改变世界

SQL Server 批量完整备份

2014-02-10 14:59 by 听风吹雨, ... 阅读, ... 评论, 收藏, 编辑

一.本文所涉及的内容(Contents)

  1. 本文所涉及的内容(Contents)
  2. 背景(Contexts)
  3. 实现代码(SQL Codes)
    1. 实现方式一(One)
    2. 实现方式二(Two)
    3. 实现方式三(Three)
  4. 参考文献(References)

二.背景(Contexts)

  在公司的内网有台数据库的测试服务器,这台服务器是提供给开发人员使用的,在上面有很多的数据库,有些是临时系统用到的数据库,这些数据库有一个共同点:数据库表结构比较重要,数据库只有一些测试数据,也就是说这些数据库都很小,而整台服务器的数据库又非常多;

  现在有这样一个需求,希望间隔一段时间就备份所有数据库,所以这里写了这篇文章,这也是另外一篇文章SQL Server 批量主分区备份(One Job)的基础;

三.实现代码(SQL Codes)

下面是实现批量备份数据库的3种方式,大家可以细细体会其中的差别:

1) 实现方式1:使用游标

2) 实现方式2:使用拼凑SQL的方式

3) 实现方式3:使用存储过程sp_MSforeachdb_Filter(以sp_MSforeachdb为基础)

 

(一)实现方式1:使用游标

执行下面的SQL脚本就可以备份当前数据库实例的所有数据库(除了系统数据库);

-- =============================================
-- Author:      <听风吹雨>
-- Blog:        <http://gaizai.cnblogs.com/>
-- Create date: <2011/12/03>
-- Description: <批量备份数据库>
-- =============================================
DECLARE
      @FileName VARCHAR(200),
      @CurrentTime VARCHAR(50),
      @DBName VARCHAR(100),
      @SQL VARCHAR(1000)

SET @CurrentTime = CONVERT(CHAR(8),GETDATE(),112) + CAST(DATEPART(hh, GETDATE()) AS VARCHAR) + CAST(DATEPART(mi, GETDATE()) AS VARCHAR)

DECLARE CurDBName CURSOR FOR 
    SELECT NAME FROM Master..SysDatabases where dbid>4

OPEN CurDBName
FETCH NEXT FROM CurDBName INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
    --Execute Backup
    SET @FileName = 'E:\DBBackup\' + @DBName + '_' + @CurrentTime
    SET @SQL = 'BACKUP DATABASE ['+ @DBName +'] TO DISK = ''' + @FileName + '.bak' +
     ''' WITH NOINIT, NOUNLOAD, NAME = N''' + @DBName + '_backup'', NOSKIP, STATS = 10, NOFORMAT'
    EXEC(@SQL)

    --Get Next DataBase
    FETCH NEXT FROM CurDBName INTO @DBName
END

CLOSE CurDBName
DEALLOCATE CurDBName

执行完上面的SQL脚本,会在E:\DBBackup的目录下生成类似下图的备份文件:

clip_image002

(Figure1:数据库备份文件)

 

(二)实现方式2:使用拼凑SQL的方式

--使用拼凑SQL的方式
DECLARE @SQL VARCHAR(MAX)

SELECT @SQL = COALESCE(@SQL,'') + '
BACKUP DATABASE '+ QUOTENAME(name,'[]') 
+ ' TO DISK = ''E:\DBBackup\'+ name + '_' + CONVERT(CHAR(8),GETDATE(),112) + CAST(DATEPART(hh, GETDATE()) AS VARCHAR) + CAST(DATEPART(mi, GETDATE()) AS VARCHAR) + '.bak' 
+ ''' WITH NOINIT, NOUNLOAD, NAME = N''' + name + '_backup'', NOSKIP, STATS = 10, NOFORMAT'
FROM sys.databases WHERE database_id >4 AND name like '%%' AND state =0

PRINT(@SQL)
EXECUTE(@SQL)

生成的脚本如Figure2所示,如果想脚本更加美观,可以加上GO语句,如Figure3所示:

clip_image004

(Figure2:生成的T-SQL脚本)

clip_image006

(Figure3:生成的T-SQL脚本)

 

(三)实现方式3:使用存储过程sp_MSforeachdb_Filter(以sp_MSforeachdb为基础)

通过查看系统存储过程sp_MSforeachdb的T-SQL源代码可以发现是没有提供@whereand参数可以过滤数据库的,参考系统存储过程sp_MSforeachtable后,在sp_MSforeachdb的基础上创建带@whereand参数的存储过程sp_MSforeachdb_Filter,这样你就可以让SQL在指定的数据库上执行;

-- =============================================
-- Author:      <听风吹雨>
-- Blog:        <http://gaizai.cnblogs.com/>
-- Create date: <2013.05.06>
-- Description: <扩展sp_MSforeachdb,增加@whereand参数>
-- =============================================
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
 
create proc [dbo].[sp_MSforeachdb_Filter]
    @command1 nvarchar(2000), @replacechar nchar(1) = N'?', @command2 nvarchar(2000) = null, @command3 nvarchar(2000) = null,
    @whereand nvarchar(2000) = null,@precommand nvarchar(2000) = null, @postcommand nvarchar(2000) = null
as
    set deadlock_priority low
    
    /* This proc returns one or more rows for each accessible db, with each db defaulting to its own result set */
    /* @precommand and @postcommand may be used to force a single result set via a temp table. */
 
    /* Preprocessor won't replace within quotes so have to use str(). */
    declare @inaccessible nvarchar(12), @invalidlogin nvarchar(12), @dbinaccessible nvarchar(12)
    select @inaccessible = ltrim(str(convert(int, 0x03e0), 11))
    select @invalidlogin = ltrim(str(convert(int, 0x40000000), 11))
    select @dbinaccessible = N'0x80000000'        /* SQLDMODbUserProf_InaccessibleDb; the negative number doesn't work in convert() */
 
    if (@precommand is not null)
        exec(@precommand)
 
    declare @origdb nvarchar(128)
    select @origdb = db_name()
 
    /* If it's a single user db and there's an entry for it in sysprocesses who isn't us, we can't use it. */
   /* Create the select */
    exec(N'declare hCForEachDatabase cursor global for select name from master.dbo.sysdatabases d ' +
            N' where (d.status & ' + @inaccessible + N' = 0)' +
            N' and (DATABASEPROPERTY(d.name, ''issingleuser'') = 0 and (has_dbaccess(d.name) = 1))' + @whereand)
 
    declare @retval int
    select @retval = @@error
    if (@retval = 0)
        exec @retval = sys.sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 1
 
    if (@retval = 0 and @postcommand is not null)
        exec(@postcommand)
 
   declare @tempdb nvarchar(258)
   SELECT @tempdb = REPLACE(@origdb, N']', N']]')
   exec (N'use ' + N'[' + @tempdb + N']')
 
    return @retval

上面的存储过程sp_MSforeachdb_Filter与sp_MSforeachdb的区别有以下两点:

clip_image008

(Figure4:添加内容1)

clip_image010

(Figure5:添加内容2)

而且需要注意在创建存储过程的时候需要设置SET QUOTED_IDENTIFIER OFF,当 SET QUOTED_IDENTIFIER 为 ON 时,标识符可以由双引号分隔,而文字必须由单引号分隔;当 SET QUOTED_IDENTIFIER 为 OFF 时,标识符不可加引号,且必须符合所有 Transact-SQL 标识符规则。具体可以参考:SET QUOTED_IDENTIFIER (Transact-SQL)

调用sp_MSforeachdb_Filter实现批量备份数据库的T-SQL如下所示:

--使用更新的存储过程sp_MSforeachdb_Filter(以sp_MSforeachdb为基础)
USE [master]
GO

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = COALESCE(@SQL,'') + '
BACKUP DATABASE [?] 
TO DISK = ''E:\DBBackup\?_' + CONVERT(CHAR(8),GETDATE(),112) + CAST(DATEPART(hh, GETDATE()) AS VARCHAR) + CAST(DATEPART(mi, GETDATE()) AS VARCHAR) + '.bak'' 
WITH NOINIT, NOUNLOAD, NAME = N''?_backup'', NOSKIP, STATS = 10, NOFORMAT'

PRINT @SQL

--过滤数据库
EXEC [sp_MSforeachdb_Filter] @command1=@SQL,
@whereand=" and [name] not in('tempdb','master','model','msdb') "

执行上面的存储过程就可以备份所有数据库(系统数据库除外,想要过滤数据库可以填写@whereand参数的条件),执行上面SQL的效果如下图所示:

clip_image011

(Figure6:错误信息)

如果没有设置SET QUOTED_IDENTIFIER 这个选项为 OFF ,那么在调用存储过程sp_MSforeachdb_Filter的时候会出现下图所示的错误信息:

clip_image013

(Figure7:错误信息)

如果想查看存储过程sp_MSforeachdb的详细代码,可以在通过访问路径:数据库-可编程性-存储过程-系统存储过程-sp_MSforeachdb找到,或者通过下面的脚本查看:

--显示规则、默认值、未加密的存储过程、用户定义函数、触发器或视图的文本
EXEC sp_helptext N'sp_MSforeachdb';

更多批量备份数据库的文章可以参考:

SQL Server 批量主分区备份(One Job)

SQL Server批量主分区备份(Multiple Jobs)

四.参考文献(References)

SET QUOTED_IDENTIFIER (Transact-SQL)(英文)

SET QUOTED_IDENTIFIER (Transact-SQL)(中文)