Home | Troubleshooting |  Quick Setup  |  Cisco How to  |  Data Recovery  |  Forums   | Blog | IT Exam Practice | Services  | About Us | Chicagotech MVP  | Search  | Contact Us  |                 

 

 

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

 to launch Back up Database Task. 

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


 

 


 

 

Hit Counter   This web is provided "AS IS" with no warranties.
Copyright © 2002-2018 ChicagoTech.net, All rights reserved. Unauthorized reproduction forbidden.