Monday, February 16, 2015

Microsoft SQL Server Express - automating backups

MS SQL Tips article on automating SQL Server Express backups and the purging of old backups

The very brief, yet excellent article by , linked above, contains a PowerShell script that would allow one to:

  1. Automate backups on SQL Server Express
  2. 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.




Minimally Logged Operations in SQL Server

I'm posting this mostly so I can find it later :) The following article has a great table that defines when operations will be minimall...