Last week one of the Sys Admin’s asked me “How big were last night’s backups?” I was like “Why are you asking me? We drop all of the backup files onto one of your arrays so why don’t you tell me.”
Background: We’re doing a Data Center move and they wanted to move a copy of all of the dbs over to the new DC to test the applications. In some shops this might be easy because they just delete backups more than a day old. For us, we are blessed with enough room to keep several days worth of backups for some of our critical systems. This is further complicated by the fact that we have over 100 databases to move and they have different retention policies based on importance.
I know that the databases take up 1.8 TB thanks to another PowerShell script I wrote but since we use backup compression our data is about a fifth of that size. But what is the exact number?! I’m glad you asked.
I threw together a script I had used previously and modified it so that would recurse down the directory structure of our db archive server and pickup on .bak files that were made in the last day. Now, because nothing is simple where I work, there is a software product – which will remain nameless for now – that also backs up transaction log files with the .bak extension; but luckily also throw in _TLOG_ so I was able to filter them out pretty easily.
Now I was already in a directory where I wanted to get all the files in all of the sub-directories when I ran this so don’t forget to do that or this could run for a long time. For you that might look like this (but probably won’t): C:\Program Files\Microsoft SQL Server\MSSQL10_50\MSSQL\Backup.
I went and checked a bunch of these file sizes manually and they came out exactly correct
If you’re new to PowerShell I hope you give this a try and come up with a way that it could be useful to you.