Another quick one that come up today which i thought was fun.

I had a request from the server team to know much space our backups where taking on each server, i think there’s a couple of different ways you could do this, but I went with querying the folder size with xp_cmdshell, now we have a lot of SQL 2005 boxes running on Windows 2003 and no active pursuit of putting PowerShell on any thing, so i went with the idea of using the command line instead of posh (which would have got a cleaner result and i could have done some logic checking).

Heres my query. I use the config table which we use to direct backups (I said i found them useful!) and then use the good old DIR to get to size, a bit of clean up is done with deleting the extra details from the table.

Let me know if you’ve got a nicer way to do it!

CREATE TABLE #results ( results VARCHAR(255) )
DECLARE @backupLocation VARCHAR(255) = ( SELECT [Value]
                                         FROM   dba.dbo.olaConfig
                                         WHERE  [Option] = 'BackupDirectory'
DECLARE @cmd VARCHAR(255) = 'DIR ' + @backupLocation

INSERT  INTO #results
        ( results )
        EXEC xp_cmdshell @cmd

DELETE  #results
WHERE   results NOT LIKE '            %'
        OR results IS NULL

SELECT  results
FROM    #results
DROP TABLE #results

Pulls out: