Thursday 9 April 2015

Configuring built-in MSSQL backups with Maintainece Plans:

We will firstly need to enable "Agent XPs" as part of our security configuration firstly:
sp_configure 'show advanced options', 1;
sp_configure 'Agent XPs', 1;
We will also start the SQLServerAgent service (as this is required to perform the backups) and also make sure it's startup type is set too "Automatic":

sc start SQLServerAgent
sc config SQLServerAgent start=auto

We should then expand the "Management" tree >> Maintainence Plans >> Maintainence Plan Wizard.

We can name the plan something like: "Daily Backup"
Schedule: Daily.

We will select the following Maintainence Task: "Backup Up Database (Full)"

Select the desired database you wish to backup and we will set "Backup set will expire" to: After 14 days - which simply sets the rentention period of the backups.

We will also define the backup directory under "Create a backup file for ever database" to something like: D:\Backups. We will also tick "Verify backup integrity" and finally click next and define an email address to send the backup log too.


Post a Comment