Skip to Content

SQL-2005 Best Practices – Maintenance and Backup plan – Part 1

The creation of a good disaster recovery plan is a key point in a successful implementation of SAP Business One. The points to consider including hardware considerations, backup/recovery strategies and maintenance strategies. I will try to put here information I gathered from different best practices from Microsoft and other sources including my personal experience.

i will recommend checking out also the following sites:

SQL Server Best Practices

Storage Top 10 Best Practices


1.        Overview

1.1.       Recovery Model

Which recovery model is best for the databases in your environment? This setting depends on the critically of the data and the acceptable data loss in the event of a system failure. SQL Server offers three recovery models that can be implemented for your databases. The appropriate choice depends on your applications and the criticality of your data. These settings can be configured either through Enterprise Manager or through T-SQL using the ALTER DATABASE command.

The three database recovery model options are:



With the Simple Recovery model, data is recoverable only to the most recent full database or differential backup. Transaction log (or incremental changes) backups are not available. The Simple Recovery model is easier to manage than the Full or Bulk-Logged models, but at the expense of higher data loss because the contents of the database transaction log are truncated each time a checkpoint is issued for the database.



The Full Recovery model uses database backups and transaction log backups to provide complete protection against failure. If one or more data files are damaged, restoring the backups permits recovery of all committed transactions using a combination of the database and transaction log backups.

Full Recovery provides the ability to recover the database to the point of failure or to a specific point in time. All operations, including bulk operations such as SELECT INTO, CREATE INDEX, and bulk loading data, are fully logged and recoverable.


Bulk Logged

The Bulk-Logged Recovery model provides protection against failure combined with the best performance. In order to get better performance, the following operations are minimally logged and not fully recoverable: SELECT INTO, Bulk load operations, CREATE INDEX as well as text and image operations.



1.2.       Backup Options 

Once the database recovery model has been identified, it is necessary to decide which backup method needs to be instituted for your backup and recovery procedures. There are several options and each has advantages and disadvantages. The backup options can be configured with either the Maintenance Plan Wizard, Enterprise Manager or through the use of T-SQL commands. Below outlines the available backup options:



This option creates a full copy of the database. A complete snapshot of your database is created at the time the backup occurs.



This option provides a copy of the active transaction log. Transaction log backups operate in conjunction with database backups to allow you to append transactions that have occurred since the last database backup. If successive logs are created, each log creates a set of the new transactions since the last transaction log backup.



This option copies only the database pages which have been modified after the last database backup. If successive differential backups are created, only the most recent differential backup is required for the recovery process. Differential backups are leveraged in combination with full backups. It is necessary to execute a full backup first and then execute the Differential backups on the needed interval. In addition, it is possible to use transaction log backups with differential backups based on the backup schedule.


File or Filegroup

For very large databases, an option is available for executing database file or filegroup backups. These backups allow you to backup a single data file at a time. One of the drawbacks with this option is that it requires more effort in planning the backup and recovery process as well as your overall database design. In most instances you only have one data file and one log file for each database and therefore this option does not make sense. Also, in order to use filegroup backups you must use transaction log backups in conjunction with this backup method.


Copy-Only Backups

If you do a full backup outside of this plan you interfere with the sequence and every differential backup is going to be based on the last full backup made.

If you do need to do a full backup and cannot use the already existing backups you will need to do a COPY type backup which is available only in a scripted way:



TO DISK = N’e:\sqldb\backup\dbName_date.bak’




2.        Backup Best Practices

Selecting the recovery model and backup options can be simple for your SQL Server implementation. The best scenario is to select the options that provide the most flexibility. The following are some guidelines that can be used for selecting the appropriate backup and recovery model as well as some additional considerations to institute.


2.1.       Recovery Model Selection

If you are unsure what recovery model to use, the best bet is to implement the FULL recovery model. This option is the most flexible and gives you the most options. It allows recovery for the most amount of data in the event of a failure. Even if the FULL recovery model is selected, you are still free to choose the individual implementation backup options.


2.2.       Backup Options

The best method is to perform full backups as often as possible depending on the size of your database, along with differential backups and lastly with a combination of transaction log backups. The frequency is dependent on your environment and the acceptable data loss for your company. If you have extremely large databases it will be difficult to execute frequent full backups, so you need to look at a combination of options.

A good starting point might be the following backup schedule:

– Execute a full database backup on a daily basis

– Perform transaction log backups every 15 minutes during the business day

– Complete differential backups every 4 hours

Rationale – The differential backups will minimize the number of transaction log restores needed. If you backup the transaction logs every 15 minutes, a maximum of 15 transaction logs would need to be restored. The worse case scenario would be 18 restorations in order to bring your database online and running. The 18 restorations would be one full, one differential, the 15 transaction log restores and one last transaction log. This last log would be from your attempt to backup your active transaction log if possible, before you begin your restores.


2.3.       Backup to Disk First

Backing up databases to disk first gives you the fastest means for performing database backups and restores. In the event that a database restore is needed, the most recent backups are on disk instead of having to request tapes to complete the restoration.

Disk backups give the DBA the most control. As a DBA you will have more control over the backup schedule. You know exactly when backups are going to start and exactly when they should finish. You do not need to worry about other variables outside of your database server to determine when and if good backups occurred. It is still necessary to coordinate with your Backup Operator in your organization to make sure the tape backup occurs after the database disk backup, so the latest backup is stored on tape.

When backing up the databases to disk, ensure you are backing up the database to a different physical drive on a different controller card. In the event of a disk failure, if you use the same drives for the database and backup you risk the loss of your databases and backups.

If you have the ability to backup to a different machine or a network appliance, this option allows for superior level of redundancy in the event of a failure.


2.4.       Archive to Tape/Dvd

Several backup vendors in the marketplace can assist with managing and verifying the success of your tape backups. These products should be part of your backup strategy, but should read the backup files from disk instead of executing backups directly from your databases. Several vendors offer external agents to read the SQL Server databases directly, but the recommendation is to write the backup to disk first and then to tape second. This method also gives you two full sets of backup files.


2.5.       Test

Regardless of the backup method, it is advantageous to periodically test the validity of the backups. It is in your best interest to randomly select databases to restore onto test servers to ensure the restore functionality works properly and is meeting your expectations. The more frequently restoration testing is conducted, the better you will prepared for a real recovery.


2.6.       Verify

Take the time to verify the backup is valid. The verify option listed above allows you to have peace of mind that the backup was successful. The last thing you want is to find out that the backup will not successfully perform the restoration. Take the extra time to run the RESTORE with VERIFYONLY option to ensure the backup was successful and is available when needed.


2.7.       System and User Databases

Ensure the backup procedures include all system and user databases. In the event of a complete system failure, it will be necessary to restore the system databases along with your user databases in order to recreate the SQL Server environment.

1 Comment
You must be Logged on to comment or reply to a post.