Home | Troubleshooting |  Quick Setup  |  Cisco How to  |  Wireless  |  Forums   | Site Map | 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
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-2017 ChicagoTech.net, All rights reserved. Unauthorized reproduction forbidden.