The very brief, yet excellent article by Ahmad Yaseen, linked above, contains a PowerShell script that would allow one to:
- Automate backups on SQL Server Express
- Automatically clean up backups that are aged > x days.
I always advocate that a DBA should test their backups in SQL Server. I would therefore recommend adding a step to Ahmad's script that performs a restore of the backup.
There are some assumptions, here:
- These are relatively small databases, and are using simple recovery model
- Along those same lines, there is room on the server's storage media to hold a "throw-away" database.
In sitting down to actually code this up, it occurred to me that this is WAY more trouble than it's worth ;-)
I would probably follow an approach that generates the necessary backup and restore scripts from SSMS, then executes them from either a normal command shell, or the powershell, as the case may be.
For those who use SQL Server Express, if you have not already encountered it, I recommend all of MSSQL Tips articles on Express Edition.
