Script to backup all databases 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
I believe you can refer to ola.hallengren. SQL server Backup scripts.
As stated on how to use for SQL server Express:

SQL Server Express has no SQL Server Agent. Therefore, the execution
of the stored procedures must be scheduled by using cmd files and
Windows Scheduled Tasks. Follow these steps.

Download MaintenanceSolution.sql.
Execute MaintenanceSolution.sql. This script creates the stored    procedures that you need.
Create cmd files to execute the stored procedures; for example:    sqlcmd -E -S .\SQLEXPRESS -d master -Q &quot;EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES', @Directory = N'C:\Backup', @BackupType
= 'FULL'&quot; -b -o C:\Log\DatabaseBackup.txt
In Windows Scheduled Tasks, create tasks to call the cmd files.
Schedule the tasks.
Start the tasks and verify that they are completing successfully.


Now you can modify this easily as per you're needs.
Note: In MaintenanceSolution, you can separately only download SQL server Backup scripts individually.