Backup of MS SQL Database with Disk Space Check

• Download script - 1.6 KB

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) -- 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

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.

-- 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
)

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'.

-- get drive sys data
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:

-- 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

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.

-- 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

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!)

-- 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

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.

-- 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;

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

• 31^st May, 2016: Version 1