Creating a transaction log backup

In this recipe, we will create a transaction log backup.

Getting ready

We will use the AdventureWorks2008R2 database for this recipe. We will create a timestamped transaction log backup file in the C:\Backup folder. Feel free to use a database of your choice for this task.

Ensure the recovery model of the database you are backing up is either Full or BulkLogged. You can use the Changing database recovery model recipe as a reference. The main code you can execute to query the current recovery model setting of your database is:

$database.DatabaseOptions.RecoveryModel

You can also check this using SQL Server Management Studio.

• Log in to SSMS

• Expand Databases, and right-click on AdventureWorks2008R2

• Go to Properties | Options and check the Recovery Model value

The T-SQL syntax that will be generated by this PowerShell recipe will look similar to:

BACKUP LOG [AdventureWorks2008R2] 
TO  DISK = N'C:\Backup\AdventureWorks2008R2_Txn_20120815235319.bak'
 WITH NOFORMAT, NOINIT...