Making a Backup of All Databases in MSSQL Server 2008

Sometimes you need to make a one-off backup of all databases (all databases) so you can move them somewhere or gather them in a separate storage location.

To back up all the databases you need to:

1. Connect to the Database Engine using SQL Management Studio.
2. Expand Management in Object Explorer and find Maintenance Plans.
3. Right click and select New maintenance plan

1. Give it any name you want (like backup_all_dbs).
2. After that a Toolbox should appear right under Object Explorer.

3. Grab the Backup Database Task item and drag it to the right
   

4. A SubPlan with the backup task appeared. Right click on it and pick Edit at the very top

5. In the dropdown next to Databases you can pick what exactly to back up. To back up all databases except the system ones, pick the option marked on the screenshot. You can also manually pick which databases you need:

1. A bit lower you can pick where exactly to save the files. You can create separate directories for each database.
   

2. Once you’re done - picked the databases to back up and the storage location - click OK to save, and you land back on the previous screen. Click the Save button (the blue floppy disk) at the top and your plan shows up in the list.

Right now you can already right click on it and select Execute and it’ll go off and run:

1. If you need to run this custom backup periodically, find the calendar-looking icon in the designer and click it (last column on the right) - a window will pop up where you can set up a schedule for periodic runs.