SQL Server数据库压缩备份小记

Asia/Shanghai | Leave a comment
SQL Server数据库压缩备份小记
SQL Server数据库压缩备份小记

This post is over a year old, some of this information may be out of date.

维护的一台服务器近期做了异地备份，具体的做法实际上也很简单，通过任务计划每日午夜将数据库备份文件压缩，再通过群晖的Cloud Station Backup回传至异地群晖NAS中，当然群晖NAS做了多盘RAID，这样数据也多了一层保护，本来数据库备份这里也考虑看能否使用第三方软件，结果在搜索时找到了微软官方的解决方案《Schedule and automate backups of SQL Server databases in SQL Server Express》，简单易用，比较nice~

这里再简单记录一下，作为一个备忘，以下操作仅供参考，建议大家先测试确保无误后再运用在生产服务器上，本人不对下面操作带来的可能数据损坏等问题负责，敬请理解。

1 备份数据库

1.1 安装必须的软件

这个是先决条件，一般服务器上可能缺少这些软件，如果不安装将无法使用下面的命令，当然你也可以使用替代命令，这里不再介绍，所要安装的软件列表如下，请依次下载，检查完整性，并按顺序安装：

  1. Microsoft Visual C++ Redistributable for Visual Studio 2015, 2017 and 2019
  2. ODBC Driver for SQL Server
  3. sqlcmd Utility

1.2 创建备份存储过程

参考微软官方的脚本SQL_Express_Backups.sql，这里摘录如下：

-- Copyright ? Microsoft Corporation.  All Rights Reserved. 
-- This code released under the terms of the 
-- Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.) 
USE [master]  
GO  
/****** Object:  StoredProcedure [dbo].[sp_BackupDatabases] ******/  
SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
-- =============================================  
-- Author: Microsoft  
-- Create date: 2010-02-06 
-- Description: Backup Databases for SQLExpress 
-- Parameter1: databaseName  
-- Parameter2: backupType F=full, D=differential, L=log 
-- Parameter3: backup file location 
-- ============================================= 
CREATE PROCEDURE [dbo].[sp_BackupDatabases]   
            @databaseName sysname = null, 
            @backupType CHAR(1), 
            @backupLocation nvarchar(200)  
AS  
       SET NOCOUNT ON;  
            DECLARE @DBs TABLE 
            ( 
                  ID int IDENTITY PRIMARY KEY, 
                  DBNAME nvarchar(500) 
            ) 
             -- Pick out only databases which are online in case ALL databases are chosen to be backed up 
             -- If specific database is chosen to be backed up only pick that out from @DBs 
            INSERT INTO @DBs (DBNAME) 
            SELECT Name FROM master.sys.databases 
            where state=0 
            AND name= ISNULL(@databaseName ,name)
            ORDER BY Name
            -- Filter out databases which do not need to backed up 
            IF @backupType='F' 
                  BEGIN 
                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','AdventureWorks') 
                  END 
            ELSE IF @backupType='D' 
                  BEGIN 
                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks') 
                  END 
            ELSE IF @backupType='L' 
                  BEGIN 
                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks') 
                  END 
            ELSE 
                  BEGIN 
                  RETURN 
                  END 
            -- Declare variables 
            DECLARE @BackupName nvarchar(100) 
            DECLARE @BackupFile nvarchar(300) 
            DECLARE @DBNAME nvarchar(300) 
            DECLARE @sqlCommand NVARCHAR(1000)  
	        DECLARE @dateTime NVARCHAR(20) 
            DECLARE @Loop int                   
            -- Loop through the databases one by one 
            SELECT @Loop = min(ID) FROM @DBs 
      WHILE @Loop IS NOT NULL 
      BEGIN 
-- Database Names have to be in [dbname] format since some have - or _ in their name 
      SET @DBNAME = '['+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+']' 
-- Set the current date and time n yyyyhhmmss format 
      SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') + '_' +  REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')   
-- Create backup filename in path\filename.extension format for full,diff and log backups 
      IF @backupType = 'F' 
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_FULL_'+ @dateTime+ '.BAK' 
      ELSE IF @backupType = 'D' 
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_DIFF_'+ @dateTime+ '.BAK' 
      ELSE IF @backupType = 'L' 
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_LOG_'+ @dateTime+ '.TRN' 
-- Provide the backup a name for storing in the media 
      IF @backupType = 'F' 
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' full backup for '+ @dateTime 
      IF @backupType = 'D' 
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' differential backup for '+ @dateTime 
      IF @backupType = 'L' 
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' log backup for '+ @dateTime 
-- Generate the dynamic SQL command to be executed 
       IF @backupType = 'F'  
                  BEGIN 
               SET @sqlCommand = 'BACKUP DATABASE ' [email protected]+  ' TO DISK = '''[email protected]+ ''' WITH INIT, NAME= ''' [email protected]+''', NOSKIP, NOFORMAT' 
                  END 
       IF @backupType = 'D' 
                  BEGIN 
               SET @sqlCommand = 'BACKUP DATABASE ' [email protected]+  ' TO DISK = '''[email protected]+ ''' WITH DIFFERENTIAL, INIT, NAME= ''' [email protected]+''', NOSKIP, NOFORMAT'         
                  END 
       IF @backupType = 'L'  
                  BEGIN 
               SET @sqlCommand = 'BACKUP LOG ' [email protected]+  ' TO DISK = '''[email protected]+ ''' WITH INIT, NAME= ''' [email protected]+''', NOSKIP, NOFORMAT'         
                  END 
-- Execute the generated SQL command 
       EXEC(@sqlCommand) 
-- Goto the next database 
SELECT @Loop = min(ID) FROM @DBs where ID>@Loop 
END

选择master数据库，运行脚本，忘记选择也没事，脚本已经USE [master]，如果一切正常，那么在master数据库内就建立了备份存储过程[dbo].[sp_BackupDatabases]。

1.3 执行备份命令

(1) 例子：使用Windows认证模式全局完整备份实例.\SQLEXPRESS所有数据库到D:\SQLBackups\路径下，这两内容请根据实际情况进行修改。

sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @backupType='F'"

(2) 例子：使用用户名<YourSQLLogin>和密码<StrongPassword>登录并差异备份实例.\SQLEXPRESS所有数据库到D:\SQLBackups\路径下，这用户名、密码、实例名、备份路径内容请根据实际情况进行修改。

sqlcmd -U -P -S .\SQLEXPRESS -Q "EXEC sp_BackupDatabases  @backupLocation ='D:\SQLBackups', @BackupType='D'"

还有其他用法，具体请参考微软《Schedule and automate backups of SQL Server databases in SQL Server Express》文档，这里不再赘述。

上面命令执行完毕后路径D:\SQLBackups\下将存放有备份的数据库文件，以BAK扩展名命名。我们可以在Windows 任务计划里周期性执行命令，以实现无人值守的数据库备份操作。

2 删除、压缩备份存档

2.1 删除旧的备份存档

随着备份命令的多次执行目的路径下备份文件将会越来越多，为了节约服务器空间我们可能需要手动删除旧的备份文件，是否可以通过命令自动删除旧的备份存档呢？

求助于万能的StackOverflow，我找到了这个方案《Batch file to delete files older than N days》，比如使用下面的命令可以删除D:\SQLBackups\路径下7天前的备份文件：

forfiles /p "D:\SQLBackups" /s /m *.BAK /D -7 /C "cmd /c del @path"

这里的天数设置在/D -命令后，比如7天就是/D -7，注意数字前面的短横线，据原作者介绍这个命令适用于Windows 7及以后版本（包括Windows 10）。

2.2 压缩备份存档

实际操作下来，这种备份模式对于远程网络传输并不是很友好，部门业务数据库的备份文件比较大，有的能有十几GB或者更多，需要传输很长时间，严重占用服务器带宽，所以对于远程备份文档传输来说，需要在传输前先压缩，实际测试下来压缩比还是相当可观的，使用7zip这个小巧开源的压缩解压，7zip有命令行版本，位于安装路径下7z.exe，比如通过下面的命令压缩D:\SQLBackups\路径下所有的BAK文件到backups.7z：

7z.exe a -t7z backups.7z D:\SQLBackups\*.BAK

这样所有的数据库备份将会被合并压缩成一个backups.7z，如果数据库较多，那么这个文件的体积也会很大，不利于后期管理，后来我的解决办法的每个BAK单独压缩，参考2.1的命令写法，我们可以尝试下面的命令：

forfiles /p "D:\SQLBackups" /s /m *.BAK /C "cmd /c C:\PROGRA~1\7-Zip\7z.exe a -t7z D:\SQLBackups\@file.7z @path"

这样在D:\SQLBackups\路径下将会生存7z扩展名的压缩文件，通过网络传输此压缩文件到备份服务器上大大提高效率，这里C:\PROGRA~1\7-Zip\7z.exe根据你的7zip安装路径可能需要修改。

3 备份、压缩、删除旧备份合并操作

参考1、2节所叙述内容，整理一下自己的服务器SQL Server数据库备份方案，分为6步：1. 设立临时目录和同步目录；2. 将同步目录链接到同步备份软件上（群晖Cloud Station Backup）；2. 删除同步目录下旧存档；3. 备份数据库到临时目录下；4. 压缩数据库并将压缩文件移入同步目录下；5. 同步软件同步；6. 删除临时目录下备份文件。

比如我们服务器上临时目录为D:\SQLBackups\Temp，同步目录为D:\SQLBackups\Sync，其中同步软件只管D:\SQLBackups\Sync路径下文件的备份，通过定期运行下面的批处理命令即可：

@ECHO OFF
forfiles /p "D:\SQLBackups\Sync" /s /m *.* /D -7 /C "cmd /c del @path"
sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\Temp\', @backupType='F'"
forfiles /p "D:\SQLBackups\Temp" /s /m *.BAK /C "cmd /c C:\PROGRA~1\7-Zip\7z.exe a -t7z D:\SQLBackups\Sync\@file.7z @path"
DEL /F /S /Q D:\BackupTemp\sqlserver\*.*

上述命令还请读者根据需要修改，这里就先介绍这么多。