To quote " "
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.
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.
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:
Database
This option creates a full copy of the database. A complete snapshot of your database is created at the time the backup occurs.
Transaction
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.
Differential
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.
Snapshot Backups
Using third party tools, such as Storage Area Network (SAN) solutions, you have the ability to capture file level snapshots of the database to replicate the complete database files to other disk drives on the SAN. Unfortunately, this method is expensive and not an option for most database installations.
Backup Commands
There are primarily two options when constructing backup commands, either backing up the database or the transaction log. In conjunction with these commands, there are several options which can be specified when constructing your backup commands. These additional options can be found in SQL Server Books Online in an article entitled ‘BACKUP’.
In the commands below, the {device} reference can specify either a logical or physical device. In constructing the commands you can reference the name of a physical file or you can specify a logical device that has been setup through Enterprise Manager or T-SQL. More information about this can be found in SQL Server Books Online.
DATABASE
This option specifies backing up the data portion of the database. For this command there are options to specify the full database, a list of files/filegroups or differential backups. The backup commands are constructed as follows:
Database
BACKUP DATABASE {databasename} TO {device}
Differential
BACKUP DATABASE {databasename} TO {device}. WITH DIFFERENTIAL
Filegroup
BACKUP DATABASE {databasename} FILE = {filename}, FILEGROUP = {filegroup} TO {device}
LOG
This option specifies a backup of the active transaction log. The log is backed up from the last successfully executed LOG backup to the end of the log. The command is constructed as follows:
BACKUP LOG {databasename} TO {device}
Hope this helps