Perform transaction log backup

Go back to DP-300 Tutorials

In this we will learn how to back up a transaction log in SQL Server 2019 (15.x) by using SQL Server Management Studio, Transact-SQL, or PowerShell.

Using SQL Server Management Studio

• Firstly, after connecting to the appropriate instance of the SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.
• Secondly, expand Databases, and, depending on the database, either select a user database or expand System Databases and select a system database.
• Thirdly, right-click the database, point to Tasks, and then click Back Up. The Back Up Database dialog box appears.
• Then, in the Database list box, verify the database name. You can optionally select a different database from the list.
• After that, verify that the recovery model is either FULL or BULK_LOGGED.
• Next, in the Backup type list box, select Transaction Log.

(optional) Select Copy Only Backup to create a copy-only backup. A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups, see Copy-Only Backups (SQL Server).

• Now, either accept the default backup set name suggested in the Name text box, or enter a different name for the backup set.
• (optional) In the Description text box, enter a description of the backup set.
• Then, specify when the backup set will expire:
  • To have the backup set expire after a specific number of days, click After (the default option). And enter the number of days after set creation that the set will expire.
  • The default value is set in the Default backup media retention (in days) option of the Server Properties dialog box (Database Settings page). To access this dialog box, right-click the server name in Object Explorer and select properties; then select the Database Settings page.
  • Now, to have the backup set expire on a specific date, click On, and enter the date on which the set will expire.
• After that, choose the type of backup destination by clicking Disk, URL or Tape. To select the paths of up to 64 disk or tape drives containing a single media set, click Add. The selected paths are displayed in the Backup to list box. And, to remove a backup destination, select it and click Remove. To view the contents of a backup destination, select it and click Contents.
• Then, to view or select the advanced options, click Options in the Select a page pane.

After that, select an Overwrite Media option, by clicking one of the following:

1. Back up to the existing media set
2. Back up to a new media set, and erase all existing backup sets

• Now, in the Reliability section, optionally, check:
  • Verify backup when finished.
  • Perform checksum before writing to media and (optional) Continue on checksum error.
• Next, in the Transaction log section:
  • For routine log backups, keep the default selection, Truncate the transaction log by removing inactive entries.
  • To back up the tail of the log (the active log), check Back up the tail of the log, and leave database in the restoring state.
• After that, if you are backing up to a tape drive (as specified in the Destination section of the General page), the Unload the tape after backup option is active. Clicking this option activates the Rewind the tape before unloading option.
• Lastly, SQL Server 2008 Enterprise and later supports backup compression. By default, whether a backup is compressed depends on the value of the backup-compression default server configuration option. However, regardless of the current server-level default, you can compress a backup by checking Compress backup, and you can prevent compression by checking Do not compress backup.

Using Transact-SQL

Execute the BACKUP LOG statement to back up the transaction log, specifying the following:

• Firstly, the name of the database to which the transaction log that you want to back up belongs.
• Secondly, the backup device where the transaction log backup is written.

However, this example creates a transaction log backup for the AdventureWorks2012 database to the previously created named backup device, MyAdvWorks_FullRM_log1.

SQL
BACKUP LOG AdventureWorks2012
TO MyAdvWorks_FullRM_log1;
GO

Using PowerShell

Set up and use the SQL Server PowerShell Provider. Use the Backup-SqlDatabase cmdlet and specify Log for the value of the -BackupAction parameter.

The following example creates a log backup of the database to the default backup location of the server instance Computer\Instance.

PowerShell
Backup-SqlDatabase -ServerInstance Computer\Instance -Database -BackupAction Log

Reference: Microsoft Documentation

Go back to DP-300 Tutorials