Red Gate forums :: View topic - Transaction Log backups failing
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Backup 7
SQL Backup 7 forum

Transaction Log backups failing

Search in SQL Backup 7 forum
Post new topic   Reply to topic
Jump to:  
Author Message
SQL_ME_RICH



Joined: 08 May 2012
Posts: 64

PostPosted: Tue May 15, 2012 9:44 pm    Post subject: Transaction Log backups failing Reply with quote

Hello -

I have some SQL Server Agent Jobs that run a set of maintenance routines (DBCC CHECKDB WITH PHYSICAL_ONLY) on 4 different databases in my default instance of SQL Server 2005 SP3. Right after the maintenace runs, a full backup runs. Shouldn't I be able to do a Transaction Log backups then and there moving foward whenever needed? I have found that my jobs say successful in the activity monitor history in SSMS, but when I look in my backup directory - only 1 of the 4 databases has TLogs backed up, and then in Backup 7, the activity history of each of the other 3 databases shows that the Transaction Log outcome was a failure.

What am I missing? Fulls and Differentials are running fine. sa is the owner for all databases.
Back to top
View user's profile Send private message
SQL_ME_RICH



Joined: 08 May 2012
Posts: 64

PostPosted: Tue May 15, 2012 10:06 pm    Post subject: Reply with quote

Nevermind - found the problem. I went back into Backup 7, and saw that the Transaction Log backup wasn't even available to those other 3 databases, and it clicked - they must be in simple recovery mode.

Sure enough - that is what it was. Switched them over to Full Recovery, and they should be ok come 10pm tonight when our next Differential backup runs.
Back to top
View user's profile Send private message
eddie davis



Joined: 14 Jun 2006
Posts: 938
Location: Red Gate Software

PostPosted: Wed May 16, 2012 11:54 am    Post subject: Reply with quote

Hi SQL_ME_RICH,

When you switched the database recovery models from Simple recovery to Full recovery, did you perform a full backup of each database? If the answer is no, the Differential or transaction log backup will most likey fail as there is no full backup post change of the recovery model.

Many Thanks
Eddie
_________________
Eddie Davis
Technical Support Engineer
Red Gate Software Ltd
E-mail: support@red-gate.com
Back to top
View user's profile Send private message Send e-mail
SQL_ME_RICH



Joined: 08 May 2012
Posts: 64

PostPosted: Thu May 17, 2012 10:26 pm    Post subject: Reply with quote

Hi Eddie - no, I did not run a full, but made the change just before the Full was scheduled to kick off (missed it by a few hours). As you noted - even the Diffs made no different for the TLog until the Full ran, but I am happy to report that all my backups are running very well now.

I have posted a new thread to see about running the TLogs while the Fulls and Diffs are running though...Is that something that should not be done? Or does SQL Backup know what to do to simultaneously perform each backup type in parallel to one another?

Thank you again!
Back to top
View user's profile Send private message
eddie davis



Joined: 14 Jun 2006
Posts: 938
Location: Red Gate Software

PostPosted: Fri May 18, 2012 10:36 am    Post subject: Reply with quote

Hi SQL_ME_RICH,
Thank you for your reply and excellent news that your jobs are working successfully.

Unfortunately you cannot take simultaneous Full, Diff and TLog backups of the same database, using SQL Backup. I believe it is due to restriction placed upon the Virtual Device Interface (VDI). What ever backup type triggers first and creates the VDI wins, blocking the other backup types. Ideally you should revise the scheduling of each job so that you are not taking simultaneous backups of the same database.

Many Thanks
Eddie
_________________
Eddie Davis
Technical Support Engineer
Red Gate Software Ltd
E-mail: support@red-gate.com
Back to top
View user's profile Send private message Send e-mail
SQL_ME_RICH



Joined: 08 May 2012
Posts: 64

PostPosted: Fri May 18, 2012 7:17 pm    Post subject: Re: Reply with quote

eddie davis wrote:
Unfortunately you cannot take simultaneous Full, Diff and TLog backups of the same database, using SQL Backup...



So - should I delete the ones that ran at the same time as the Fulls and Diffs in the past? That of my TLog backups?

They all ran without error (simultaneously), but are you saying that they are corrupt and no good?
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group