Brad’s Sure Guide to SQL Server Maintenance Plans- P30: SQL Server has a reputation as being a simple database application to install, configure, and maintain. This is a little misleading. SQL Server is a powerful relational database that can handle the needs of the largest organizations and, as such, its proper maintenance almost certainly requires the attention of an experienced DBA. | Chapter 12 Back Up Database Full Task The basic purpose of a backup of a SQL Server database is to make a copy of the data so that it can be used to restore a database in the event of a disaster such as system failure damage to the database corruption of the data it contains and so on. Of all the Maintenance Plan tasks that you need to perform this is the most important. It is critical that you make full backups of your production databases on a regular basis preferably once a day. A full backup can be performed on any database in SQL Server expect for tempdb no matter what recovery model it uses. While the Backup Database Full task is the focus of this chapter it is not the only backup task available from the Maintenance Plan Wizard. In the following two chapters we will examine the Backup Database Differential task and the Backup Database Transaction Log task. In most cases you will combine the Backup Database Full task with the Backup Database Transaction Log task in the same Maintenance Plan. In fact once you have mastered the Backup Database Full task you will find that the other backup tasks are very similar and learning them will be easy. Note before we start that the Maintenance Plan Wizard performs SQL Server native backups. If you want to perform a backup using a third-party tool then in almost all cases you will have to use the third-party s GUI tool or T-SQL to perform your backups. This is because third-party tools don t usually integrate with the Maintenance Plan Wizard. If you are using such a tool you can still create a Maintenance Plan using the Maintenance Plan Wizard but you will need to leave out the backup tasks that are included with it. Backup Strategy - a Brief Primer As 1 stated in the introduction there are three basic types of database backups Full backup - backs up all the data in the database. This is essentially making a copy of the MDF file s for a given database. Differential backups - a backup of any data that has changed since the
