How to configure automatic, scheduled database backups in MSSQL 2012

How to configure automatic, scheduled database backups in MSSQL 2012



  1. A Windows Server that is running Microsoft SQL Server 2012
  2. Credentials that have MSSQL administrative level access (such as the ‘sa’ internal account, or a Windows user that has been granted administrative access to the MSSQL instance)


The first step in setting up automated backups of your MSSQL databases is to log in to SQL Server Management Studio:



After logging in, expand the ‘Databases’ folder to ensure you have selected the correct MSSQL instance – do this by verifying the list of User databases displayed:



Next, create a folder on a filesystem that’s accessible by the MSSQL instance to hold your backups.  In this example, we created the ‘DB_Backups’ folder at the root of the C: drive:


Next, expand the ‘Management’ folder in the left navigation pane, as shown here.  Then right-click the ‘Maintenance Plans’ folder and select ‘New Maintenance Plan’


If you see an error message about the Agent XPs component, you will need to execute some T-SQL to enable ‘advanced options’ and ‘Agent XPs’:



Open a new query window, copy/paste the following T-SQL into it, then select ‘Execute’ to run the query:

sp_configure 'show advanced options', 1;




sp_configure 'Agent XPs', 1;





After enabling Agent XPs, continue with the ‘New Maintenance Plan’ step


In the ‘New Maintenance Plan’ window, give your maintenance plan a meaningful name. In this example, we’ve called it ‘DatabaseBackups’:



Click ‘OK’ to save the name,  and you will be brought to an unsaved Maintenance Plan with a single default Subplan (Subplan_1):



Highlight the Subplan (Subplan_1) and click the small ‘edit’ icon that’s next to ‘Add Subplan’:



When you click the ‘Edit’ button, you should see the following Subplan Properties window:



Fill out the details here, giving the Subplan a more descriptive name and description.  Also, select the schedule you would like this subplan to run on (daily, weekly, etc.):



Once you’ve completed all of the fields to your liking, click ‘OK’ to save your changes.  You will be returned to the unsaved Maintenance Plan, as shown:



Make sure that the subplan you created is highlighted and hover your mouse over the ‘Toolbox’ icon on the left-hand side of the SQL Server Management Studio window, as shown:



Select the ‘Back Up Database Task’ from the toolbox and drag it into the empty space below your Subplan:



Right-click on the ‘Back Up Database Task’ item in your subplan, then select ‘Edit’ at the top of the popup menu:



You should see the following window (Back Up Database Task):



In the task editor, use the ‘Database(s):’ dropdown to select ‘All user databases’, then click ‘OK’:



Next, click the ‘browse’ icon next to ‘Folder:’ to select the target for your backup files:



Change any of the other options that you’d like (Backup type, Verify backup integrity, etc.) and then click ‘OK’ to save your changes:



Once you’ve saved the changes, you should see your maintenance plan and subplan look similar to the following:



Now we need to create a new Subplan to back up the ‘system’ databases.  Click the ‘Add Subplan’ button near the top of the maintenance plan window:



Following the steps above to edit the Subplan, you should have a Task window that looks similar to the following when you are done (make sure you select ‘All system databases’ in the ‘Database(s):’ dropdown):



Click ‘OK’ to save your edits and you should see a maintenance plan window similar to this:



Now that we have tasks to back up User and System database, we need to save our Maintenance Plan.


At the top of the Management Studio window, select File -> Save Selected Items:



After saving, right-click the ‘Maintenance Plans’ folder and select ‘Refresh’.  You should have a list that’s similar to the following



Check to see if your SQL Server Agent is stopped or running.  The following shows the default, which is ‘stopped’:


Right-click on ‘SQL Server Agent’ and select ‘Start’ to start the service:



You may see a User Account Control popup similar to this:



Select ‘Yes’ to allow the change.


When you see the following popup, click ‘Yes’ to confirm that you want to start the SQL Server Agent:



Now that the SQL Server Agent is running, expand it, then expand the ‘Jobs’ folder and you should see your two backup tasks that you created in your maintenance plan:


Double-click ‘Job Activity Monitor’ (or right-click and select ‘View Job Activity’) to see the status of your jobs:



To manually run one (or more) jobs to test them, select the job, right-click it, then select ‘Start Job at Step...’:



The following window will open and you can track the progress of the job:



Re-open the Job Activity Monitor and you will see the status of your freshly-run job and others:



If you browse to your backup target folder, you will now see the backup files that have been created.  These files can be backed up to another location via one of Codero’s backup products, thus ensuring the ability for you to restore them in the event you need to:


The last step is to make sure that the SQL Server Agent service is always running.  Open the Services Control Panel for you server (via services.msc at a command prompt, or the ‘Services’ control panel in Administrative Tools):



Scroll down and locate ‘SQL Server Agent ()’.  Notice that by default, the Startup Type is set to ‘Manual’:



Select the service, right-click it, and select ‘Properties’ to open the service properties editor:



Use the ‘Startup type:’ dropdown to change the startup type to ‘Automatic’, then click ‘OK’ to save your changes:



The service should now start automatically whenever the server is restarted.  This will ensure that your backup jobs always run, even in the event of an unplanned reboot.


Remember, it’s always a good idea to test your backups on a regular schedule.  You don’t want to ‘trust’ that the backups are good when you need them the most!!

Posted in
Last update:
2015-12-03 20:39
Average rating:0 (0 Votes)