上QQ阅读APP看书，第一时间看更新

Transaction log backup

As written in the previous section, full backup establishes a baseline for more efficient backup strategies. In other words, at least one full backup must be created before we can start a backup transaction log. A transaction log backup is a consequencing backup that backs up all transaction log records from the last LSN, which is contained in any previous backup.

In other words, a full backup is a backup of state of the database while transaction log backup is a backup of additional changes. Using transaction log backup ensures that the recovery point objective (RPO) point to which the database could be restored, will be very close to the moment when the database was damaged.

Another important property of transaction log backups is, that this backup type erases inactive virtual log files of the transaction log file. It keeps the transaction log file at a reasonable size.

To be able to use a transaction log backup, the database's recovery model property has to be set to the BULK_LOGGED or FULL value. Remember that the BULK_LOGGED value does not allow you to restore the database at a certain point in time.

When the recovery model is set correctly and a full backup is executed, we can start a backup of the transaction log on a regular basis. The basic syntax for a transaction log backup is as follows:

BACKUP LOG AdventureWorks TO DISK = 'D:\myBackups\AdventureWorksLog.bak' WITH <additional options>

As seen in the preceding code example, the BACKUP LOG syntax is very similar to the BACKUP DATABASE syntax. The database must already exist and it must be in online state and the path to the .bak file must exist in the filesystem, but if the .bak file does not exist, it will be created when the BACKUP LOG statement is executed for the first time.

Additional options are basically almost the same as the full backup statement:

INIT/NOINIT pair controls whether the content of the backup file will be replaced
FORMAT/NOFORMAT pair is a stronger variant for INIT/NOINIT options
COMPRESSION/NO_COMPRESSION pair controls the backup compression

The meaning of these options is completely the same for all backup types.

This is the moment when we have sufficient information about basic backup types and we can go through more complex examples. The following code sample shows you a set of backup statements and their sorting. The only difference in the real world is that every statement is executed separately and typically the execution is planned by SQL Server Agent (note: SQL Server Agent will be described later in Chapter 9, Automation - Use Tools to Manage and Monitor SQL Server 2017).

Let's see this assignment: The AdventureWorks database is used as a typical operational database with lots of incoming transactions. These transactions must not be lost because clients of the database write their data through a sales web application. The backup cycle will be restarted every day. The AdventureWorks database is relatively small so all backups could be stored in the same file. An important business need is that the database must be recoverable to a certain point in time. How do we prepare the AdventureWorks database for proper backups and which backup statements do we use?

-- 1. we have to ensure if the database is in FULL recovery model
--    this statement will be run once only
--    if database is in FULL recovery model already, nothing happens
ALTER DATABASE AdventureWorks SET RECOVERY FULL
GO

-- 2. every day, for example at 3 a.m. we will run full backup
--    following statement will reset content of the backup file
BACKUP DATABASE AdventureWorks TO DISK = 'D:\backups\AdventureWorks.bak'
WITH INIT
GO

-- 3. every hour or more often we will repeat the transaction log backup
--    following statement will apend the backup to the backup file and clears 
--    transaction log 
BACKUP LOG AdventureWorks TO DISK = 'D:\backups\AdventureWorks.bak'
WITH NOINIT
GO

As seen in the previous code sample, it is not so complicated to create a simple and strong enough backup strategy. The transaction log backup itself has to be executed often; in some cases, more than every hour, because the transaction log file is maintained well and the backup operation itself is fast, relatively small, and not in conflict with regular user requests.

Until this moment, everything is just routine, but what if some damage appears? A very common mistake is that the only kind of damage is physical damage, for example, file corruption. However, we have to keep in mind that another damage is also logical damage, for example, accidental deletion of data or some structures. When such logical damage occurs, the database itself is still online and, from SQL Server's perspective, no problem occurred; but from user's perspective, the database is useless and damaged.

For either type of corruption, SQL Server provides a special transaction log backup called tail-log backup. The tail-log backup is a variant of the transaction log backup. It backs up transaction log records written to the transaction log file up to the moment of the corruption, that's why it's called backup of tail of the transaction log. Tail-log backup switches the state of the database to restoring. The restoring state of the database causes inaccessibility of the database to users. It's very important to use the tail-log backup in the case of logical corruption. It is not so probable that all data in the database will be logically damaged at the same moment and that's why we need to stop any user working on the rest of the data because we know that the database is going to be restored and all user changes will be lost. An example syntax to execute tail-log backup is as follows:

BACKUP LOG AdventureWorks TO DISK = 'D:\backups\tailLog.bak' WITH NORECOVERY

The NORECOVERY keyword is the option that forms the tail-log backup. The preceding syntax is sufficient just for logical accidents such as unwanted deletes of data. But for every backup operation, the database must be in consistent and online state. What if the database is in suspect state?

Suspect state of a database is set by SQL Server in situations when the database is somehow corrupted physically and not accessible to users. In this case, we have two additional options that can be added to the BACKUP LOG statement:

BACKUP LOG AdventureWorks TO DISK = 'D:\backups\taillog.bak'
WITH
NORECOVERY, NO_TRUNCATE, CONTINUE_AFTER_ERROR

Let's describe these new options in more detail. When the database is corrupted, no backup could be executed with the exception of the preceding code. The CONTINUE_AFTER_ERROR option says to SQL Server that we know about the corruption, but we want to keep all possible transaction log records captured by the transaction log until the moment of damage even if the transactions are incomplete or some of the transaction log records are not readable. If we don't use the CONTINUE_AFTER_ERROR option, SQL Server expects that the database is in consistent online state and the BACKUP LOG statement will fail.

The second NO_TRUNCATE option causes no maintenance to be done by SQL Server on completion of the backup. It's intended behavior because we know that the database is in an unstable state and it's probable that any write operation will fail. We also know that after the tail-log backup completion, we will start the restore process of the database so any additional maintenance is wasteful.