It's been a few years, but I seem to recall that I used PoSH because I had trouble getting T-SQL to handle some of the filesystem checks (eg, check for default data file location, check for space). If that sounds vague, it is...I really don't remember.

Ultimately, I did automate the tasks via SQL Agent scheduled task. We would run the script about 3-5 days before the end of the month. That way if it did fail, I'd have a day or two to fix things up before month end rolled around and data would start loading. In the end, we never had an issue with the script failing in production. We only had an issue in non-production (as I recall, the issue would only arise if we refreshed the non-production database after production had run the merge/switch routine).

We recently turned off this solution because we stopped using the database (because we switched to a new product with a new [non-existent] BI solution). The business is only now beginning to realize how much they have lost due to the switch. But that's a story for a different day...

Regards,

Hugh Scott