In this recipe, we will create a transaction log backup.
We will use the AdventureWorks2014 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 that the recovery model of the database you are backing up is either Full or BulkLogged. You can use the recipe Changing database recovery model as a reference. Execute the following command to query the current recovery model setting of your database:
$database.DatabaseOptions.RecoveryModel
You can also check this using SQL Server Management Studio. The steps are as follows:
Log in to SSMS.
Expand Databases and right-click on AdventureWorks2014.
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 this:
BACKUP LOG [AdventureWorks2014] TO DISK = N'C:\Backup\AdventureWorks2014_Txn_20150314235319...