Transaction Log backup and recovery 2

ArticleDirectory

• 14.2 tail log backup
• 14.2.1 graphical tail log backup operation
• 14.2.2 use the backup log statement to back up the end log

14.2 tail log backup

It is easy to understand and perform operations to restore the database to the immediate point. Next we will focus on the necessary operations to restore the database to the fault point, that is, the backup of the tail log.

However, it should be noted that in management studio, tail log backup will never be completed according to the default settings.

14.2.1 graphical tail log backup operation

Figure 14-7 shows the selection tab of the database for log backup. By default, you can select the disconnect transaction log button, which will never back up the end log.

Tip: to back up the tail log, select "backup log tail and restore the Database" in Figure 14-7.

Figure 14-7 [Options] Tab

14.2.2 use the backup log statement to back up the end log

You can also directly execute the backup log statement to back up logs. The following describes the syntax of the statement.

1. Syntax form

The syntax of the backup log statement is as follows.

Backup log {database_name | @ database_name_var}

{

To <backup_device> [,... n]

[[Mirror to <backup_device> [,... n] [... Next-mirror]

[

[Blocksize = {blocksize | @ blocksize_variable}]

[[,] {Checksum |No_checksum}]

[[,] {Stop_on_error| Continue_after_error}]

[[,] Description = {'text' | @ text_variable}]

[[,] Expiredate = {date | @ date_var}

| Retaindays = {days | @ days_var}]

[[,] Password = {password | @ password_variable}]

[[,] {Format |Noformat}]

[[,] {Init |Noinit}]

[[,] {Noskip| Skip}]

[[,] Mediadeiterator = {'text' | @ text_variable}]

[[,] Medianame = {media_name | @ media_name_variable}]

[[,] Mediapassword = {mediapassword | @ mediapassword_variable}]

[[,] Name = {backup_set_name | @ backup_set_name_var}]

[[,] No_truncate]

[[,] {Norecovery | standby = undo_file_name}]

[[,] {Norewind |Rewind}]

[[,] {Nounload |Unload}]

[[,] Restart]

[[,] Stats [= percentage]

[[,] Copy_only]

]

}

2. Main Parameters

For other parameters, refer to the related description of books online. The main parameters related to the backup log are as follows.

-No_truncate: used only with backup log. Specifies that logs are not truncated and the database engine attempts to perform a backup, regardless of the database status. This option allows you to back up logs when the database is damaged.

-The no_truncate option of backup log is equivalent to specifying both copy_only and continue_after_error.

-No_log | truncate_only: By dropping all logs other than activity logs, you do not need to back up and copy the logs to delete the inactive logs and truncate_only. This option releases space. Because log backup is not saved, you do not need to specify a backup device. No_log and truncate_only are synonymous. Changes recorded in the log cannot be recovered after the log is truncated using no_log or truncate_only. To recover the database, immediately execute the backup database for full backup or full differential backup.

3. Usage

To back up the end log, you can use the truncate_only parameter. Example of this bookCodeAs follows.

Backup log [db_test] to disk = n'c: "test2.bak'

With no_truncate,

Noformat,

Noinit,

Name = n' dB _ test-transaction log backup ',

Skip,

Norewind,

Nounload,

Norecovery,

Stats = 10

Go