There are many ways to automatically backup MS SQL Server databases even if you use an Express version and do not have SQL Agent installed.
I sketched one possible solution some time ago, calling T-SQL commands from a batch file which is executed as a Scheduled Task.
If you are running MySQL databases under Linux, you may be aware of a tool called automysqlbackup, which runs as a cron job, creates dumps of MySQL databases and rotates daily and weekly backups.
During the last couple of days I translated this script into PowerShell to backup MS SQL Server databases in a similar way.
automssqlbackup provides the following functionality:
- Local or Remote backup
- Rotation of daily and weekly backups
- Optionally zipping backups
- Logging
- Notifications by e-mail
The first release of automssqlbackup is version 0.25 to reflect the current version 2.5 of automysqlbackup, which it is based on.
automssqlbackup is available for download here.
[...] Files with PowerShell When I developed automssqlbackup, one of the key features was to optionally zip the generated database backup [...]
[...] Updating automssqlbackup A little bit of fine-tuning automssqlbackup: [...]
[...] for Server’ raised in SMO Backup Since I developed the first version of my script automssqlbackup, I kept getting a ‘Backup failed for Server’ exception on one specific database. All [...]
How does this script pick which ones to archive. Out of 20 DB’s that it backed up, it only archived 2 of them. Thanks.
Generally, *every* .bak should be zipped. Although I occasionally notice that a .bak is not deleted, but this happens quite rarely.
Make sure you use the latest version which zips using SharpZipLib rather than Explorer’s zipped “folders”.