How to create Transaction (LOG) backups for MSSQL Database

Transaction Log Backup:
Create a folder for the backup in the Hardisk (Ex: C:\SQL_backup\Log)
1. Right-Click on Maintenance Plans and select New Maintenance Plans, Give naming Convention as Log_Backup.

2. In Left side of Server Management Studio there will be a Tool Box within Maintenance Plan Tasks
 
3. Drag and drop the Back Up Database Task and Maintenance Clean Task in the Log_Backup  Design area.
 
4. Right Click on the Back Up Database Task and select Edit.
 

5. Select the Type of Backup you want to do as Transaction Log

6. Select the Database In Database(s)  in these database list (Ex: TestDB.)
 

7. Select Create a Backup file for every database, select Create a sub-directory for each database Give the path where we want to store the database backup as C:\SQL_backup\Log and give the Backup file extension as trn and Click OK
  

8. Right Click on the Maintenance Cleanup Task  and select Edit 

9. Select  Search folders and delete files based on an extension 

10. Give the Folder Path  C:\SQL_Backup\Log  and give a file extension as trn in the file extension box and select Include first-level sub-folders. 






11. File Age:  Give the desired file retention period that how many days of backup you wanted to keep in the backup folder.
  

12. Click OK 
13.  Right Click on the Back Up Database Task,  select Add Precedence Constraint,  Select Back Up Database Task in From and Maintenance Cleanup Task in To from the Control Flow and click OK




14. Now you will see a link between Back Up Database Task and Maintenance Cleanup Task. Press Ctrl+S to save the task


15. Go to the SQL Server Agent in the SQL Server Management Studio, Select the Incremental_DB_Backup  and Right Click and select Properties  in the properties sheet select Schedules and click New
  

16.  Give some name in the name so that we can identify the Job Schedules 

17.  Select Daily in frequency, Recurs every  in 1 day. 

18.  Select 12:00:00 AM in Daily frequency and click OK 

19.  Select occurs every. Give 20 Minutes.
  

20.  Click OK in Job properties.

Keywords: Backups, MSSQL, Log Backups, Transaction Backups

Comments

Popular posts from this blog

Observium: Configuring Microsoft Windows 2008 Server SNMP Agent

AWS: Introduction to AWS (Amazon Web Services)

System/Server Health Status using PowerShell