Scheduling Backups in SQL Server

This tech tip will help you set up scheduled backups for your MEX database in the full version of Microsoft’s SQL Server.

Setup scheduled backups in the full version of Microsoft SQL server

Firstly, make sure the SQL server agent is running and the start-up method is set to automatic, without this feature running backups will not be taken.
To check this open the windows services tool (search for ‘services.msc’).

Searching for Services.msc in windows

With the Tool open find the SQL server agent for you SQL server running the MEX database. Make sure the process is started and the start-up type is set to automatic.

Checking your start-up type in services.msc

If it is not set to automatic right click on it and open properties, from there you can change the start-up type to automatic.

Set up your Schedule

Open SQL Server management studio and connect it to the SQL server running your MEX database.

• Expand the management folder
• Right Mouse click on maintenance plans
• Select maintenance plan wizard.

In the Wizard set a name and description for the task, it is a good idea to name it in a way that makes it easy to identify what the task does. Make sure ‘select Separate schedules for each task’ is selected and then press next.

Select the task as Back up database (full), you can do other types if you want to but when getting support from MEX full database backups are easier to work with. When you have at least one backup type selected press next.

Decide the order of your tasks if you have selected more than one, if you have only selected one option you can skip over this step.

Select the database you want to backup, if you are only creating this backup schedule for the MEX database, choose ‘These databases’ and select the MEX db.

Set up options as in the screenshot below and choose a location to save your backups to. Make sure you change the schedule at this point so the backups will run. It is good to take backups at least once a week and at most once a day. Press next.

Choose a save location for the maintenance plan actions. It might be a good idea to store this in the same location as the backups themselves to make it easier to find. Press next and then Finish.

After the wizard finishes running the maintenance plan will appear the SQL Server Management Studio object explorer. To test the plan right click on it and press execute. Once it is finished make sure you can find the backup file.

Need Help With This?

For more information or help with this, contact the MEX Support team at support@mex.com.au or call +61 7 3392 4777.