作者:qingheqianyenft | 来源:互联网 | 2018-07-10 07:00
一.本文所涉及的内容(Contents)本文所涉及的内容(Contents)背景(Contexts)实现代码(SQLCodes)实现方式一(One)实现方式二(Two)实现方式三(Three)参
一.本文所涉及的内容(Contents)本文所涉及的内容(Contents)背景(Contexts)实现代码(SQLCodes)实现方式一(One)实现方式二(Two)实现方式三(Three)参
一.本文所涉及的内容(Contents)
参考文献(References)
二.背景(Contexts)三.实现代码(SQL Codes)
下面是实现批量备份数据库的3种方式,大家可以细细体会其中的差别:
1) 实现方式1:使用游标
2) 实现方式2:使用拼凑SQL的方式
3) 实现方式3:使用存储过程sp_MSforeachdb_Filter(以sp_MSforeachdb为基础)
(一)实现方式1:使用游标
执行下面的SQL脚本就可以备份当前数据库实例的所有数据库(除了系统数据库);
-- =============================================
-- Author:
<听风吹雨>
-- Blog:
<>
-- 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的目录下生成类似下图的备份文件:
(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所示:
(Figure2:生成的T-SQL脚本)
(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:
<>
-- 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的区别有以下两点:
(Figure4:添加内容1)
(Figure5:添加内容2)