Backing Up the Transaction Log

As SQL Server must write all transactions to the transaction log, backing up the log is crucial. Not only does this permit recovery of these transactions, the act of backing up the log also truncates, or clears out, the inactive portion of the log. This allows room for further transactions. The log can be set to auto grow, but this should not be relied on, because if it's unchecked it can fill all available disk space, and can be problematic to shrink back to a reasonable size.

NOTE

In simple recovery mode SQL Server periodically truncates the log without backing it up. Any transactions that are thus purged are unrecoverable. Transaction log backups are not available in this mode.

Backing Up Transaction Logs with T-SQL

The syntax for T-SQL backups of the transaction log is quite similar to that of database backups. Here is the statement for a basic log backup:

 BACKUP LOG northwind TO Nwlogbackup 

Listing 16.5 shows the syntax. For clarity, I have removed the options already covered under database backups. There are, however, a few parameters specific to log backups that are worth looking at in detail.

Listing 16.5 Syntax for the BACKUP LOG Command

 BACKUP LOG {  database_name   @database_name_var  }     TO < backup_device > [ ,...  n  ]     [ WITH         [ { NO_LOG  TRUNCATE_ONLY } ]          [ [ , ] NO_TRUNCATE ]         [ [ , ] { NORECOVERY  STANDBY =  undo_file_name  } ]     ] } 

NO_LOG and TRUNCATE_ONLY

Although part of the BACKUP LOG command, NO_LOG and TRUNCATE_ONLY don't actually back up the log. The two are synonymous so I'll just refer to NO_LOG for the sake of simplicity. The purpose of BACKUP LOG , when used in conjunction with NO_LOG , is to clear, or truncate, the inactive portion of the log. Log truncations actually occur at the end of every BACKUP LOG command. And therein, as they say, lies the rub. SQL Server 2000 is a write-ahead Relational Database Management System (RDBMS), so every transaction must be written first to the transaction log. The bad news is, BACKUP LOG is a transaction and therefore must first be written to the log. If you are trying to back up your log because it is full, the BACKUP LOG command fails, because it can't write to a full log. This would be a catch-22 situation if not for NO_LOG . When you use this option, you are basically telling SQL Server to skip the write to the log, skip backing up the log, and just trash the inactive portion of the log to clear some space. Because the transactions committed in this portion of the log are now unrecoverable, it is strongly suggested that you perform a full database backup after using BACKUP LOG WITH NO_LOG . Mark this page. When your log fills up someday and grinds your database to a halt, you'll be glad you did.

NO_TRUNCATE

The NO_TRUNCATE option is used when the log is available, but the database is not. Its function is actually the opposite of NO_LOG and TRUNCATE_ONLY . Under normal circumstances, the BACKUP_LOG command not only writes to the transaction log, but also signals a checkpoint for the database to flush any dirty buffers from memory to the database files. Where this becomes a problem is when the media containing the database is unavailable and you must capture the current contents of a log to a backup file for recovery. If the last time you did a log backup was four hours ago this would mean the loss of all the input since then. If your log is on a separate disk, which is not damaged, you have those four hours of transactions available to you, but BACKUP LOG fails as it can't checkpoint the data files. Run BACKUP LOG with the NO_TRUNCATE option and the log is backed up, but the checkpoint is not run, as the log is not actually cleared. You now have this new log backup to restore as well, enabling recovery to the time of failure. The only transactions lost will be those that were not yet committed.

TIP

Any time you think you might have to do a recovery, the first thing you should do is back up the transaction log. If you skip this step and restore the database, you will have unnecessarily lost all the transactions since the last log backup.

NOTE

There was a bug in SQL 7.0 that prevented the NO_TRUNCATE option from working if the primary data file for the database was damaged. Those of you who found this out the hard way will be happy to know it is fixed in SQL Server 2000.

NORECOVERY STANDBY= undo_file_name

The [NORECOVERY STANDBY= undo_file_name ] clause for the BACKUP LOG command is new for SQL 2000. This has been part of the RESTORE LOG command since SQL Server 7.0. In the context of the BACKUP LOG command, these options are generally used in conjunction with a Standby Server. When NORECOVERY is specified, the log is backed up, and the database is left in recovery mode. This could be used in a planned switch over to a Standby Server. As the database is in recovery mode, it is ready to have logs applied to it. This avoids having to do a full restore of the Standby Server back to the production server. The logs from the Standby Server are applied back to the production server to bring it up to date. The STANDBY= undo_file_name option is similar; however, it leaves the database available in read-only mode. See the section, "Using a Standby Server," later in this chapter, for more information.

BACKUP LOG Examples

Listings 16.6, 16.7 and 16.8 show examples of typical backup scripts.

Listing 16.6 Sample Script for a Simple Log Backup

 -- Back up to a permanent backup device. Don't initialize the device. -- Provide a name for the backup USE MASTER BACKUP LOG Northwind TO NWlogback WITH NOINIT, NAME = 'Northwind log  backup', NOFORMAT 

Listing 16.7 Sample Script Clearing a Full Transaction Log

 -- Clear the transaction log. USE MASTER BACKUP LOG Northwind WITH NO_LOG 

Listing 16.8 Sample Script for Backing Up the Log When the Database Is Inaccessible

 -- Back up the log without truncating it. USE MASTER BACKUP LOG Northwind TO NWlogback WITH   NO_TRUNCATE 

Backing Up Transaction Logs with SQL Enterprise Manager

Backing up transaction logs uses the same dialog box that is used for database backups. Select the Transaction Log radio button, a backup device, and if desired, a schedule. Figure 16.5 illustrates a typical transaction log backup using the GUI. Note that Append to media is selected by default. This is important if you are scheduling a recurring log backup that will reuse a device. You will need all the logs since the last full, differential, or file backup to do a restore, so be careful not to overwrite them by changing this option.

Figure 16.5. Selecting a transaction log backup.

You should also take into account the option tab. By default, Remove Inactive Entries from the Transaction Log is selected. Figure 16.6 illustrates this. If you unselect this option, it is the equivalent of using the NO_TRUNCATE option.

Figure 16.6. The Options tab of the Backup dialog box.

NOTE

If you try to back up the log and the Transaction Log radio box is unavailable, your database is in Simple recovery mode. You will have to switch (database properties/options) from this mode before transaction log backups are allowed. After switching from Simple recovery mode, perform a full database backup.

Backing Up the System Databases

The system databases, master, msdb, and model, must also be included in your backup plan. There is also a tempdb, but as its name implies, it contains no permanent data. The master and msdb databases are set to Simple recovery mode by default. This is sufficient as they are small, and full backups will suffice. The model database is the basis for any user databases that you create and is set to Full recovery by default. Changing the recovery mode of the model database will change the recovery mode of any subsequently created databases.

As the master database contains information crucial to SQL Server, it should be backed up on a regular basis, and whenever a major system change is implemented. For instance, if you create a new database, the existence of that database is recorded in the master database. It then makes sense to back up not only the new database, but the master as well. The same goes for dropping or altering a database.

The msdb database stores all operations for SQL Server Agent, such as jobs, operators, and alerts. Back it up on a regular basis, and whenever you make major changes to SQL Agent tasks . The model database should be backed up whenever you make modifications to it.

NOTE

If you lose the master database, you will have to rebuild it using the rebuildm .exe command. This will also rebuild the msdb and model databases. All three databases are returned to the state they were in when the SQL Server was installed, and can then be restored from your backups.