How To Backup All SQL Express Databases
First, I'm assuming SQL Server Express is installed on Windows Server. If not, download it from Microsoft here.
Often times you will need to make backups of your SQL databases. By using the following method, you will be able to backup all the databases you have on your SQL Server at once.
Step 1: Open SQL Server Management Studio (or interface with SQL Server using the command line).
Step 2: Create the Stored Procedure "sp_BackupDatabases" by executing this SQL script that's available from Microsoft. Copy the SQL script, open a new query window and execute it on "master" so that it exists in the "System Databases".
Step 3: Create a batch file called "Sqlbackup.bat" with the following code (change the path to the directory you want to use for new backups and change the instance name to your SQL Express instance). This script deletes the specified directory, creates it and then saves the backups to it.
Save this file in a location on your drive (eg: "C:\scripts").
Step 4: Run the Windows Task Scheduler (taskschd.msc) and create a Basic Task to run a batch command. You can run it everyday or week if you want. For "Action" you will want to select "Start a program". From here, put in the location of the .bat file.
Step 5: Test running the backup process by clicking "Run" from the menu or right clicking the task. Once complete, you will see a .BAK file, which is a full backup, of each database on the server. Every time the task runs, it will create a new full backup and overwrite the previous version of the database, which gets deleted.
Addtional Considerations
If you want to have a daily, weekly and monthly backup of the current version of the database, you would specify each directory to do these in 3 separate .bat files. This way you can resort back to a backup from the previous week or even month if the daily one isn't enough.
Another option could be to have a folder for each day of the week, a seperate weekly and monthly folder as well. This way you could resort to the previous day at any time and have older copies as well.
The amount of backups you have will depend on how much space is on your server.
If you want the backups stored off of the server, you would need to build another process which uploads them elsewhere.
