Setting up a maintenance schedule

Setting up a new server does not have to be a daunting task; on the contrary, with well defined processes, it should be just another step before your server goes operational – this is true whether the server is a physical or virtual server, clustered server, etc.  The general process is essentially the same for all database servers (although we are only focusing on Microsoft SQL Server in this article).

Thanks to Ola Hallengren, most DBAs only need to install his maintenance scripts on their server (don’t worry – this is a free solution implemented in many different installations).  Download the script from his site (you should always retrieve the latest version since he is constantly improving it), change the backup directory location in the script (variable @BackupDirectory), and execute the script.  It will create all of the necessary objects.  In this example, you can see it created  the different types of backup jobs you normally need. 



A great benefit of these jobs is that you set the process up once, providing you have your backup location set up (e.g. maybe you have a separate tape job that backups the backup directory to tape periodically for offsite disaster recovery).  As you create new databases, the script will automatically pick them up and they will be backed up and sent offsite.

You can execute these by invoking sqlcmd or set them up on schedules.  The following script sets up a standard schedule

Get the code Create Job Schedule

Leave a Reply

Your email address will not be published. Required fields are marked *