Introduction
Recently, I was called on to implement a backup routine for MS SQL that was to be triggered manually from within an application. The problem is that the backup drive was on a virtual machine, and the organization only had a certain amount of drive space assigned for the system. If the drive was out of space for whatever reason, the backup would fail. We needed to put a system in place that would tell the user there was no space left and they needed to raise the alarm. Sure - there are all kinds of bells and whistles we could/should have put on here to do wonderful stuff automatically, but, this was the jobspec so in case it, or parts of it are useful, I have documented it here. The code is presented here as a standalone script, but it can easily be transformed into a stored procedure and run as a timed job, etc.
Using the Code
The first thing we need to do is set up a few variables, the comments behind each tell what they are for.
Declare @DatabaseName as varchar(200)
Declare @FileName as varchar(100)
Declare @BackupFolder as varchar(200)
Declare @BackupFileNamePath as varchar(200)
Declare @BackupDriveLetter as char
Declare @SpaceAvailable float
Declare @DatabaseSize float
Declare @BackupInfo varchar(1000)
We then initialize the script by adding some values to the variables...
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) + _
'----------------------------------------'
The variables are the name of the database, a folder where I want to backup, a complete filename made up of the database name and a preformatted date string, etc.
The next thing in the script is the creation of some temp tables ... before we create them, we check if they already exist and delete them if they do.
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 TABLE #Rslt
(
backupResult VarChar(250),
BackupInfo varchar(500)
)
CREATE TABLE #DriveData
(
Drive CHAR(3),
MBFree float
)
CREATE TABLE #DBData
(
Name varchar(200),
MBSize float
)
After we setup the temp tables, we call a system stored procedure xp_fixeddrives
. This queries the operating system for a list of, well, fixed drives! ... we insert the results into the temp table 'DriveData
'.
insert into #DriveData
EXEC master..xp_fixeddrives;
Here is the kind of output you can expect when you execute that query:
Drive MBFree
C 16275
The next step is to query some system files to get the current size of the database we are backing up:
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
Once that's complete, we place the information into some variables for later comparison:
select @SpaceAvailable = MBFree from #DriveData where Drive = @BackupDriveLetter
select @DatabaseSize = MBSize from #DBData where Name = @DatabaseName
Almost there, now we need to get some debug information together (not strictly needed, but will save you pulling your hair out if something goes wrong and you need to troubleshoot a bit!)
You could prob do the next chunk in a clever select/join rather than use Cursors, but I'm not that clever, so all you clever clogs SQL heads out there in reading land can propose a more efficient solution if you wish ... bottom line for this is that it's not going to run that often, and does what it says on the tin, so I'm not concerned.
The output of this code is nicely formatted text describing the drive space information.
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
Since we are doing pretty, let's now get some readable text for the size of the database itself.
(Same comments about cursors apply here!)
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
Finally (here's the reason we came to this party!), we run the comparison code and if the disk space available is greater than the database size, then we run the backup, else we log an error.
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;
That's it, job done. I have attached a script you can download to save you doing a copy/paste if you need the code. Happy scripting!
History
- 31st May, 2016: Version 1