TRANSACTION LOG Backups in SQL Server Step by Step Tutorial with Examples

Transaction log backup is only possible when your database is in FULL or BULK-LOGGED recovery model. With the help of Transaction Log backup one can achieve Point in Time recovery for the database in case of any disaster It is highly recommended to perform transaction log backups at regular intervals to minimise the loss of work and to truncate the transaction log.

This is Part 10 of 16 Part SQL Server Database Backup Tutorial. Click here to read it from the beginning….

Create a TRANSACTIONAL LOG Backups Using TSQL commands

BACKUP LOG MyTechMantra
TO DISK = 'C:\DBBackups\MyTechMantra.TRN'
WITH NOFORMAT, NOINIT, 
SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 25 

/* Verify the Database Backup File Once the Backup has completed successfully */

RESTORE VERIFYONLY
FROM DISK = 'C:\DBBackups\MyTechMantra.TRN'
GO

Create a TRANSACTIONAL LOG Backup Using SQL Server Management Studio

• Open SQL Server Management Studio, connect to the appropriate SQL Server Database Instance, in Object Explorer, click the SERVER NAME to expand the server tree.
• Expand Databases node and then right click the database which you want to take a transaction log backup.Select Tasks, and then click Back Up…option as shown to open up Back Up Database dialog box.Select Backup Type as Transaction Log and Select Destination as Disk
• Click on Add button to add a backup file and specify the backup file name and click OK to save the changes
• Finally to take Transaction Log backup click OK

Clicking Next Page button to continue reading the topics and click on the Previous Page button to revisit the previous topic.

Related Articles

• SQL Server WHERE Clause T-SQL Tutorial with Examples
• SQL Server ORDER BY Clause T-SQL Tutorial with Examples
• SQL Server SELECT TOP Clause T-SQL Tutorial with Examples
• SQL SELECT | SQL QUERY | SQL SELECT Statement T-SQL Tutorial with Examples
• SERVERPROPERTY Function T-SQL Enhancement in SQL Server 2016
• FORMATMESSAGE Statement T-SQL Enhancement in SQL Server 2016
• STRING_SPLIT Function and STRING_ESCAPE Function in SQL Server 2016
• COMPRESS and DECOMPRESS T-SQL Enhancement in SQL Server 2016
• ALTER DATABASE SET AUTOGROW_ALL_FILES T-SQL Enhancement in SQL Server 2016
• ALTER DATABASE SET AUTOGROW_SINGLE_FILE T-SQL Enhancement in SQL Server 2016
• ALTER TABLE WITH (ONLINE=ON | OFF) T-SQL Enhancement in SQL Server 2016
• DROP IF EXISTS SQL Server T-SQL Enhancement in SQL Server 2016
• TRUNCATE TABLE WITH PARTITIONS T-SQL Enhancement in SQL Server 2016
• T-SQL Enhancements in SQL Server 2016 for Developers and DBAs
• TAIL-LOG Backup in SQL Server Step by Step Tutorial with Examples
• PARTIAL Backup in SQL Server Step by Step Tutorial with Examples