SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Transaction Logs backup job running full backup
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Roccofox
Starting Member

United Kingdom
6 Posts

Posted - 11/04/2013 :  10:48:14  Show Profile  Reply with Quote
Hi Folks,

I'm new to the administration side of SSMS and recently had to create a backup job so that it backed up the transactions logs to a .trn file rather than storing them in the LDF file.

However when my backup job for the transaction logs runs, every 30 mins, it seems to be doing a FULL backup and slowing down our system.

My current backup schedule should be a Full backup at 1am every Saturday and then differential backups at 1am every night apart from Saturday and then the transaction logs every 30 mins.

Using SQL 2008 R2 and the recovery model of the db in question is Full.

So where am I going wrong folks?

Many Thanks

Tom

There are 10 types of people in this world. Those that understand binary and those that don't.

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 11/04/2013 :  12:44:39  Show Profile  Reply with Quote
Open up SSMS, under Servername -> Management -> Maintenance Plans, right click on the maintenance plans you have, select modify and examine what they do. YOu can right-click and edit the steps. For the transaction log backup, the Backup Type should show "Transaction Log". If that is not the case, make appropriate changes.
Go to Top of Page

Roccofox
Starting Member

United Kingdom
6 Posts

Posted - 11/05/2013 :  03:14:40  Show Profile  Reply with Quote
Hi James,

Thanks for the reply but everything that side in management plans is OK.

The backup type is already set to transaction log.

There are 10 types of people in this world. Those that understand binary and those that don't.
Go to Top of Page

Roccofox
Starting Member

United Kingdom
6 Posts

Posted - 11/05/2013 :  03:44:04  Show Profile  Reply with Quote
i think i have probably not described this issue correctly. I am backing up the db Full backup once a week, differential every night and transaction logs every 30 mins.

Looking at the job activity it looks like the transaction logs are backing up as the job succeeds and only lasts 10 seconds or so depending on activity. I can see the .trn files in the location where i have them set to backup to disk.

The issue is that when i got to restore a Point in Time it shows one backup and says it is a full backup when it is not its a transaction backup.

so why does SSMS think or label that the last transaction backup was a FULL backup

hope that makes more sense to someone.

There are 10 types of people in this world. Those that understand binary and those that don't.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000