• Yeah, in the limited scope of having sql express I could see how initially you might want to do this, but it's still not the best option. Even assuming you decided to use PS to run the backup you're still better off calling a backup SP from invoke-sqlcmd instead of the overly-complicated SMO method.

    Also, I don't really want to put that much effort into backing up just a couple express installs. Most companies aren't overrun with them so they're the exception and I'm not going to hinge my entire backup scenario on the LCD.

    But if I were faced with this issue, I'd still code it in Agent, I would just run it from another box. Or I would schedule it in an SSIS pkg from another box. Make something you can gather history for the standard. Keep SQL where SQL belongs.

    And as for cmdshell... I talk about this all the time. It's not evil, and it's not even unsafe. You just have to lock it down and make sure that it's being used well. So what's wrong with using cmdshell for deleting your backup files? That's what I do. Cmdshell is no more dangerous than having an sa acct. You protect your sa, so protect your cmdshell and use it wisely. But it's not inherently evil or bad or even ill-advised. It just got a bad rep there for a while cause it came unprotected out of the box and got exploited. Now it's locked down by default, so open it up only to the DBAs and start using it again.

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE: