Backing up SQL Server can be a tricky thing.
First of all, it's important to understand that - with few exceptions - the only thing that can back up SQL Server is SQL Server itself. For example, you may have a third-party backup solution that utilizes a SQL Server-specific agent. The fact is, that agent is probably communicating with SQL Server, asking SQL Server to get the backup data, and then streaming that data to the backup solution. The reason for this is because SQL Server backups are tightly integrated with the way the server itself operates.
Taking a full backup, for example, doesn't require SQL Server to be taken offline in any way. Users can continue using their databases while those same databases are being backed up (although performance may be somewhat slower). Essentially, SQL Server relies – as it does for everything – on its transaction logs to manage the backup. This allows the server to keep track of additional database activity which has occurred during the backup, and to include that traffic in the backup.
Backing up a database with either a full or differential backup typically clears the transaction log. The theory is that with the data safely backed up, the log is no longer needed as a form of backup. Backups thus help keep the log from growing forever and ever. A transaction log backup may also clear the log, but does not back up the database itself; as the name implies, it merely backs up the actual log.
So here's how smart DBAs typically set up their backup plans: they take a full backup whenever it's practical to do so.
Full backups of large databases can be time-consuming, especially when writing to slower media like magnetic tape. Although the database is usable during the backup time, performance suffers. This is why most DBAs try to schedule the full backup to occur during a slow period, such as late at night. Weekly full backups are a common goal.
Somewhat more frequently – perhaps even daily – the DBA will schedule differential backups. Each differential grabs only the data that has changed since the last full backup, so the differentials are typically much smaller, and can be accomplished quicker.
Much more often, the DBA will schedule a transaction log backup. These are typically very, very quick, as the log is small to begin with, and the backup is only concerned with the transactions that have occurred since the last log backup. I've seen these types of backups scheduled as often as every half-hour.
Data recovery requires the DBA to restore the full backup first and to have SQL Server remain in restore mode. This means the database is unavailable for normal use. They then restore the most recent differential, again leaving SQL Server in restore mode, followed by a restore of each log backup taken since that differential was made. DBAs then tell SQL Server to commence recovery. Note that in the graphical user interface, you can do all of this in one step if all of the backup files are available at once.
SQL Server applies the differential to the full backup and then begins replaying transactions from the log backups, bringing the database up-to-date with the most recent log backup. The database will become available for normal activity at the end of the recovery process.
Because SQL Server is always involved with making backups, some shops will forgo the use of specialized agents and simply use SQL Server's built-in backup capabilities to back up simple files to disk. Those files can easily be grabbed by a traditional file-based backup system.
If the backup files are kept on separate disks from the database and log files, then they're less likely to suffer damage if something happens to the database itself. Keeping the most recent set of backup files on disk (as well as on tape for safekeeping) keeps them handy in the event that a restore is needed.
 |
|
| TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of . |
|
| |
All Rights Reserved, , TechTarget |
|
|
|
|
Part 1: 
Don Jones is a co-founder of Concentrated Technology LLC, the author of more than 30 IT books and a speaker at technical conferences worldwide. Contact him through his website at 
