Tuesday, 07 February 2012 10:35

How to back up your system databases

Written by 
Rate this item
(7 votes)

Besides your BizTalk databases your SQL Servers also have the following system databases:

  • master - contains all system-level information for a SQL Server system
  • model - template for creating new databases
  • msdb - used by SQL Server Agent for scheduling alerts and jobs
  • tempdb - used for amongst others temporary user objects

Since most of these databases are relatively static, they don't need to be in the Backup BizTalk Server job, but they do need to be in a backup. Such a backup can be made with a Maintenance plan. In this article I show you how to create such a Maintenance Plan in the most easiest way, with the Maintenance Plan Wizard.

Open SQL Server Management Server and in the left pane, navigate to Management, Maintenance Plans

1 SSMS Maintenance Plans

Right click in the right pane, the context menu appears.

2 - SSMS - Context Menu - Maintenance Plan Wizard

Select Maintenance Plan Wizard. The SQL Server Maintenance Plan Wizard appears

SSMS - Maintenance Plan Wizard

Click Next

Maintenance Plan Wizard - Select Plan Properties

Enter a name and a description
Click the Change... button to define a schedule

Maintenance Plan Wizard - Job Schedule Properties

Define a schedule which runs every day, say at 2AM. Click OK when you're finished.
Click Next.

Maintenance Plan Wizard - Select Maintenance Tasks

In the list with available Maintenance Tasks, check  Back Up Database (Full)
Click Next

Maintenance Plan Wizard - Select Maintenance Tasks Order

Since there is only 1 task, we don't have to determine the order, so click Next

Maintenance Plan Wizard - Define BackUp Database Full Task

Select the Databases dropdown box, the following screen appears

Maintenance Plan Wizard - Define BackUp Database Full Task - Select one ore more databases

Select System databases and click OK

Maintenance Plan Wizard - Define BackUp Database Full Task - Databases selected

As you can see, the system databases are now selected
Click on the elipsis button (...) behind the Folder textbox

Maintenance Plan Wizard - Define BackUp Database Full Task - Locate Folder

Select a folder where your databases are stored and click OK.
Then click Next

Maintenance Plan Wizard - Complete the Wizard

A resume is shown of the to be performed actions, Click Finish to perform these actions.

Maintenance Plan Wizard - Maintenance Plan Wizard Progress

The Maintenance Plan is created. You can choose to see a report of it, by clicking the Report button

Maintenance Plan Wizard - Select Report Options

Click Next

Maintenance Plan Wizard - View Report

When you have checked the report, close it.

In the SQL Server Management Studio you can see that the Management Plan is created

SSMS - Maintenance Plans - Backup System databases created

Under the SQL Server Agent jobs you can see that a new job is created. This job actually does the backup.

SSMS - SQL Server Agent - Job Backup System databases created


Read 5738 times Last modified on Wednesday, 08 February 2012 13:08
Lex Hegt

Lex Hegt currently works as a BizTalk architect/administrator at Ordina. Although he works in the Information Technology for more than 25 years, he 'only' works with BizTalk for 8 years. His first BizTalk assignments were as a developer, but since a couple of years he works as an administrator.
Besides this blog he also blogs for many years at the BIA blog and does he maintain some tools, namely BizTalk Processing Monitor and BTSDecompress. He has certifications for BizTalk Server 2006, BizTalk Server 2006 R2 and BizTalk Server 2010.


Website: biztalkia.blogspot.com
Login to post comments