带磁盘空间检查的 MS SQL 数据库备份

• 下载脚本 - 1.6 KB

引言

最近，我被要求为一个 MS SQL 数据库实现一个备份例程，该例程需要从应用程序内部手动触发。问题是备份驱动器位于虚拟机上，而组织只为系统分配了一定量的驱动器空间。如果驱动器由于任何原因空间不足，备份将会失败。我们需要建立一个系统，告诉用户没有剩余空间，他们需要发出警报。当然 - 我们可以/应该在这里放置各种各样的功能来自动完成很棒的事情，但是，这是工作规范，所以如果它或其中的一部分有用，我已在此处记录了它。代码在这里以独立脚本的形式呈现，但它可以很容易地转换成存储过程并作为定时作业运行，等等。

Using the Code

我们需要做的第一件事是设置一些变量，每个变量后面的注释说明了它们的作用。

Declare @DatabaseName as varchar(200) -- e.g.: MyDatabaseName
Declare @FileName as varchar(100) -- e.g.: MyBackup.bak (but formatted with data time stamp)
Declare @BackupFolder as varchar(200) -- e.g.: c:\data\backupfiles\
Declare @BackupFileNamePath as varchar(200) -- e.g.: c:\data\backupfiles\MyBackup.bak  
                                            -- (both combined)
Declare @BackupDriveLetter as char -- e.g.: C  (c drive)
Declare @SpaceAvailable float -- will store the space available per drive
Declare @DatabaseSize float -- will store the size of the database
Declare @BackupInfo varchar(1000) -- will store information about the backup itself 
                                  -- when carried out

然后，我们通过将一些值添加到变量来初始化脚本...

set @DatabaseName = 'MyTestDataBase'
set @BackupFolder = 'c:\data\'
set @FileName = @DatabaseName + '_Backup_' + FORMAT(SYSDATETIME(), 'yyyy_MM_dd' + '.bak')  
set @BackupFileNamePath = CONCAT(@BackupFolder, @FileName)  
set @BackupDriveLetter = 'C'
set @BackupInfo = 'Debug information at: ' + _
Format(SysDateTime(), 'dd-MMM-yyyy hh:mm') + CHAR(13) + CHAR(10) + _
'----------------------------------------'​​​​​

变量是数据库的名称、我想要备份的文件夹、由数据库名称和预先格式化的日期字符串等组成的完整文件名。

脚本中的下一件事是创建一些临时表......在创建它们之前，我们会检查它们是否已经存在，如果存在则删除它们。

-- Check if a temp tables exist, if yes, delete them

IF OBJECT_ID('tempdb..#DriveData') IS NOT NULL
DROP TABLE #DriveData

IF OBJECT_ID('tempdb..#DBData') IS NOT NULL
DROP TABLE #DBData

IF OBJECT_ID('tempdb..#Rslt') IS NOT NULL
DROP TABLE #Rslt

-- create the temp tables

CREATE TABLE #Rslt
( 
   backupResult   VarChar(250),
   BackupInfo varchar(500)
)

CREATE TABLE #DriveData
(
    Drive   CHAR(3),
    MBFree float
)

CREATE TABLE #DBData
(
    Name   varchar(200),
    MBSize float
)

设置好 *temp* 表后，我们调用系统存储过程 `xp_fixeddrives`。这会查询操作系统以获取固定驱动器的列表！... 我们将结果插入到临时表 '`DriveData`' 中。

-- get drive sys data
insert into #DriveData
EXEC master..xp_fixeddrives;

这是您在执行该查询时可以期望的输出类型

Drive  MBFree
C      16275

下一步是查询一些系统文件以获取我们要备份的数据库的当前大小

-- get current space taken by database
with fs
as
(
    select database_id, type, size * 8.0 / 1024 size
    from sys.master_files
)
insert into #DBData(Name, MBSize)
select 
    name,
    (select sum(size) from fs where type = 0 and _
     fs.database_id = db.database_id) + (select sum(size) from fs where type = 1 _
     and fs.database_id = db.database_id)
from sys.databases db
where db.name = @DatabaseName

完成后，我们将信息放入一些变量中以供以后比较

select @SpaceAvailable = MBFree from #DriveData where Drive = @BackupDriveLetter
select @DatabaseSize = MBSize from #DBData where Name = @DatabaseName

快完成了，现在我们需要收集一些调试信息（不是严格需要的，但如果出现问题并且您需要进行一些故障排除，它将节省您拔头发的时间！）

您可能可以在一个聪明的 select/join 中完成下一部分，而不是使用游标，但我没有那么聪明，所以阅读土地上的所有聪明的 SQL 大佬如果愿意可以提出更有效的解决方案... 最终结果是它不会经常运行，并且做它说的事情，所以我并不担心。

此代码的输出是格式良好的文本，描述了磁盘空间信息。

-- drive info

Declare @Drive CHAR(3)
Declare @MBFree int
SET @BackupInfo = @BackupInfo + CHAR(13) + CHAR(10) + _
    '# Drive information:' + CHAR(13) + CHAR(10)

Declare drive_cursor CURSOR FOR  
SELECT Drive, MBFree  
FROM #DriveData 

OPEN drive_cursor   

FETCH NEXT FROM drive_cursor INTO @Drive, @MBFree  

WHILE @@FETCH_STATUS = 0   

BEGIN   
  SET @BackupInfo = @BackupInfo + CHAR(13) + CHAR(10) + _
      ltrim(rtrim(@Drive)) + ': ' + ltrim(rtrim(Str(@MBFree, 25, 2)))
  FETCH NEXT FROM drive_cursor INTO @Drive, @MBFree  
END   

CLOSE drive_cursor   
DEALLOCATE drive_cursor

既然我们在做美化工作，现在让我们为数据库本身的大小获取一些可读的文本。
（关于游标的相同评论也适用于这里！）

-- database size info
Declare @Name CHAR(200)
Declare @MBSize float

SET @BackupInfo = @BackupInfo + CHAR(13) + CHAR(10) + _
    CHAR(13) + CHAR(10) + '# Database size:' + CHAR(13) + CHAR(10)

Declare db_cursor CURSOR FOR  

SELECT Name, MBSize  
FROM #DBData 

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @Name, @MBSize

WHILE @@FETCH_STATUS = 0   

BEGIN   
  SET @BackupInfo = @BackupInfo + CHAR(13) + CHAR(10) + _
      ltrim(rtrim(@Name)) + ': ' + ltrim(rtrim(Str(@MBSize, 25, 2)))
  FETCH NEXT FROM db_cursor INTO @Name, @MBSize 
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

最后（这就是我们来参加这个派对的原因！），我们运行比较代码，如果可用磁盘空间大于数据库大小，则我们运行备份，否则我们记录一个错误。

-- determine if there is sufficient space to run the backup.
if (@SpaceAvailable < @DatabaseSize)
begin

delete from #Rslt

insert into #Rslt (BackupResult, BackupInfo)
(select 'ERROR: Insufficient space on backup drive. _
 DB Size: '  + Str(@DatabaseSize, 25, 2) + ' Space available: ' + _
 Str(@SpaceAvailable, 25, 2), @BackupInfo)
end

else 

begin 
     BEGIN TRY

     BACKUP DATABASE @DatabaseName TO DISK = @BackupFileNamePath

     delete from #Rslt

     insert into #Rslt (BackupResult, BackupInfo)
       (select 'BACKUP OK', @BackupInfo)

     END TRY

     BEGIN CATCH

     Declare @ErrorMessage as varchar(max) 

     set @ErrorMessage = ERROR_MESSAGE()

     delete from #Rslt

     insert into #Rslt (BackupResult, BackupInfo)
       (select 'BACKUP FAILED! - ' + @ErrorMessage, @BackupInfo)

     END CATCH
end;

就这样，工作完成了。我已附上一个脚本，您可以下载它以节省您复制/粘贴的时间，如果您需要代码。祝您脚本编写愉快！

历史

• 2016 年 5 月 31 日：版本 1