Home | Troubleshooting |  Quick Setup  |  Cisco How to  |  Data Recovery  |  Forums   | Blog | IT Exam Practice | Services  | About Us | Chicagotech MVP  | Search  | Contact Us  |               Select a Topic VPN How To Network How To Remote Access How To Windows How To Internet How To New Topics   

Why and How to Create SQL Transaction Log backup

By design, Microsoft SQL log records will never be deleted from the transaction log. The logical log would grow until it filled all the available space on the disks holding the physical log files. Truncation of the transaction logs is the process to reduce the size of the logical log (not physical) by to deleting inactive log records no longer necessary for recovering or restoring a database.

To truncate the inactive logs, you can create transaction log backup beside the daily full recovery model or bulk-logged recovery model. Here is the how to.

1. Follow this article

How to create SQL Database backup using Maintenance Plan

2. Create the Transaction log backup by selecting Transaction log as Backup type, trn as Backup file extension.

3. Since SQL Transaction log backups generally use fewer resources than database backups. So that you can create them more frequently than database backups.
 

Or run a SQL command, for example,

BACKUP LOG [PE1] TO DISK = N'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\PE1\PE1_backup_201202231507.trn' WITH NOFORMAT, NOINIT, NAME = N'PE1_backup_20120223150702', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [RE7] TO DISK = N'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\PE1\RE7_backup_201202231507.trn' WITH NOFORMAT, NOINIT, NAME = N'RE7_backup_20120223150702', SKIP, REWIND, NOUNLOAD, STATS = 10

Post your questions, comments, feedbacks and suggestions

Contact a consultant

Related Topics