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.

1 comment:

21st Century Software Solutions said...

No matter what database platform you’re running, dbaDIRECT is your answer for 24×7 monitoring and expert skill, at a lower cost than what’s possible with internal administration. We offer each of our core remote management services for all major database platforms, including Oracle, Sybase, MySQL, SQLServer, and IBM DB2. Our team of DBAs is here ’round the clock for your database needs, capable of servicing any size organization at any time of the day. Period.
Remote dba services support 24x7 of below mentioned applications - more… Online Training- Corporate Training- IT Support U Can Reach Us On +917386622889 - +919000444287

Scripting out existing database mail configuration

SQL Server Central Artical on Scripting out Existing database mail configuration I wanted to save this here so I can easily find it. Sorry ...