• 欢迎访问搞代码网站,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站!
  • 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏搞代码吧

SQL Server 批量完整备份

mysql 搞代码 4年前 (2022-01-09) 19次浏览 已收录 0个评论

一.本文所涉及的内容(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: — 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 FORSELECT NAME FROM Master..SysDatabases where dbid>4OPEN CurDBNameFETCH NEXT FROM CurDBName INTO @DBNameWHILE @@FETCH_STATUS = 0BEGIN–Execute BackupSET @FileName = ‘E:\DBBackup\’ + @DBName + ‘_’ + @CurrentTimeSET @SQL = ‘BACKUP DATABASE [‘+ @DBName +’] TO DISK = ”’ + @FileName + ‘.bak’ +”’ WITH NOINIT, NOUNLOAD, NAME = N”’ + @DBName + ‘_backup”, NOSKIP, STATS = 10, NOFORMAT’EXEC(@SQL)–Get Next DataBaseFETCH NEXT FROM CurDBName INTO @DBNameENDCLOSE CurDBNameDEALLOCATE 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 + ‘来@源gao*daima.com搞@代#码网_backup”, NOSKIP, STATS = 10, NOFORMAT’FROM sys.databases WHERE database_id >4 AND name like ‘%%’ AND state =0PRINT(@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: — Description: — =============================================USE [master]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER OFFGOcreate 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) = nullasset 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 intselect @retval = @@errorif (@retval = 0)exec @retval = sys.sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 1if (@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)


    搞代码网(gaodaima.com)提供的所有资源部分来自互联网,如果有侵犯您的版权或其他权益,请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected],我们会在看到邮件的第一时间内为您处理,或直接联系QQ:872152909。本网站采用BY-NC-SA协议进行授权
    转载请注明原文链接:SQL Server 批量完整备份

    喜欢 (0)
    [搞代码]
    分享 (0)
    发表我的评论
    取消评论

    表情 贴图 加粗 删除线 居中 斜体 签到

    Hi,您需要填写昵称和邮箱!

    • 昵称 (必填)
    • 邮箱 (必填)
    • 网址