I'm working on automating backups of SQL Server databases on SQL 2008 R2 Express. These backups are initiated daily by the Windows Task Scheduler, which execute a batch file, which in turns executes a SQL Script. I cannot use the SQL Agent because this is the express edition.
Currently, I am writing a script which backs up each database one by one. However, this is quite high maintenance - every time a database is added, dropped, or renamed, I have to modify this script to reflect these changes. I would instead like to write one single SQL script which backs up all databases at once, without the need to explicitly backup each database one by one.
This is currently the script used to backup a single database:
Print 'Backing up MyDatabase...' BACKUP DATABASE MyDatabase TO DISK = 'F:\Backups\SQL Databases\MyDatabase.bak' WITH FORMAT, NAME = 'Full Backup of MyDatabase'; GO
How can I go about enumerating a list of all databases and performing a loop to do this backup to all databases?
Just some additional notes of the backup process:
- Scheduled Task in Windows executes a batch file at 9:00 PM daily
- Batch file calls OSQL which executes the SQL script
- The OSQL outputs a text file saving the results
- There are about 30 databases to be backed up
- Some of the databases are over 2GB (so it will take some time)
- A third-party backup service obtains the resulting backup files and pushes them off-site at midnight every night