How much space are your backups using
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