Microsoft SQL Server 2008 R2 Unleashed- P114:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1074 CHAPTER 33 Database Maintenance option for the AdventureWorks2008 database. Multiple versions of backups can be retained with this option and the identification of the backup is simple because of the naming convention. CAUTION You should use the option Back Up Databases Across One or More Files with caution. The pitfall with overwriting the file with this option is that only one backup is available for restoration. When this option is used with the Append option you can eat up all your disk space if the file is not cleaned up. In addition if multiple databases are backed up with the plan all these backups will be spread across the file or files specified for the destination. A separate backup for each database is not created with this option. This can lead to confusion and complicate the restoration process. Generally speaking you should steer clear of backing up the database to a single file or set of files. Instead you should choose the option Create a Backup File for Every Database. This option has fewer pitfalls and requires little attendance. When you use the Create a Backup File for Every Database option you need to specify a folder for the database backups to be written to. You can use the default folder or you can change it to a folder of your choice. It is a good practice to choose a folder on a drive that is different from the drive where your database files reside. Having backups on the same drive as your data could be a big problem if that drive fails and your only backups are on that drive. If you select the option Create a Sub-directory for Each Database each database has a separate subfolder under the folder specified for the backup. CAUTION The main pitfall associated with the option Create a Backup File for Every Database is that many backup files can be created and are not automatically deleted by default. This point has been mentioned already but it is a critical consideration. The good news is that you can now add the deletion of the older
