介绍

最近我叫上实现对MS SQL备份程序，这是必须从应用程序中手动触发。问题是，在备用驱动器是在虚拟机上，并且该组织只产生了一定量的分配的系统驱动器空间。如果驱动器是出于无论什么原因空间，备份将失败。我们需要把一个系统，这将告诉用户没有留下空间，他们需要引起警觉。当然 - 还有FO花里胡哨的，我们可以/应该放在这里自动做精彩的东西，但是，这是JOBSPEC所以在情况下，它或它的一部分是有用的，我在这里记录这一切多种。代码在这里表示为一个独立的脚本，但它可以很容易地转变成一个存储过程和运行为定时作业等

使用代码

我们需要做的第一件事就是设置几个变量，每个背后的意见告诉他们是什么。

Declare @DatabaseName as varchar(200) -- eg: MyDatabaseName
Declare @FileName as varchar(100) -- eg: MyBackup.bak (but formatted with data time stamp)
Declare @BackupFolder as varchar(200) -- eg: c:\data\backupfiles\
Declare @BackupFileNamePath as varchar(200) -- eg: c:\data\backupfiles\MyBackup.bak  (both combined)
Declare @BackupDriveLetter as char -- eg: 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
)

我们安装后临时表，我们称之为系统存储过程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

快到了，现在我们需要一起得到一些调试信息（不是严格必要的，但会救你拉你的头发，如果出现错误，你需要解决一点！）

你可以做习题下一大块在一个聪明的选择/加入，而不是使用游标，但我没那么聪明，所以你聪明木屐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 sufficent space to run the backup.
if (@SpaceAvailable < @DatabaseSize)
begin

delete from #Rslt

insert into #Rslt (BackupResult, BackupInfo)
(select 'ERROR: Insufficent 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;

这就是它，完成任务。我附上一个脚本，你可以下载保存你做了复制/粘贴，如果你需要的代码。快乐的脚本！