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
 General SQL Server Forums
 New to SQL Server Administration
 Tlog backup false alarm or not?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Hommer
Aged Yak Warrior

784 Posts

Posted - 04/02/2013 :  10:54:18  Show Profile  Reply with Quote
Hi,

Our consultant has set up these two maintenance plans for
full and tlog backup.

The full backup has no problem.

The tlog backup is failing with this error:

"BACKUP LOG cannot be performed because there is no current database backup."

I cannot find the problem, and furthermore, the job has been creating these .trn files in the location specified. They are of variours sizes(30 GB to 2 MB). The server is under pre-production testing. And the db has been on full recovery mode.

How do I know if this is a false alarm, i.e. the backup are just fine or not?

I hate to redo their work. One reason is that this server is in the principal role of a 2008R2 mirroing with high security.

So how to fix this job if it is not a false alarm?

Thanks!

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 04/02/2013 :  11:08:06  Show Profile  Visit russell's Homepage  Reply with Quote
How many databases are being backed up? Maybe the maint plan is trying to do a log backup for a db that isn't backed up, or is in simple recovery model.

The only way to know if backups are any good is to restore them. We have a test server we restore our most critical backups to every day.
Go to Top of Page

Hommer
Aged Yak Warrior

784 Posts

Posted - 04/02/2013 :  11:14:38  Show Profile  Reply with Quote
That is a great point!

And, here are what I found:

Full: Selected: user_db1, user_db2, master, model, msdb
TLog: All user dbs.

If two of them have to be the same, I should remove master, model and msdb and put them into another full backup plan.


Edited by - Hommer on 04/02/2013 11:21:34
Go to Top of Page

Wsql
Starting Member

5 Posts

Posted - 04/02/2013 :  11:14:54  Show Profile  Reply with Quote
Try to do a restoration of the tlog files. The error seems that you didn't take full backup before you kick of the tlog backup. But if you have the .trn files then your job is working properly but there is a problem with setting the alerts. So, to check this go the alerts page under SQL Agenet for that specific page and see your set up. The best way is to test restoring the tlogs.
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 04/02/2013 :  12:10:48  Show Profile  Visit russell's Homepage  Reply with Quote
quote:
Originally posted by Hommer


Full: Selected: user_db1, user_db2, master, model, msdb
TLog: All user dbs.


If there are more user dbs than these 2 (user_db1, user_db2) then that's your problem.
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
789 Posts

Posted - 04/02/2013 :  13:01:33  Show Profile  Reply with Quote
The databases that are currently mirrored could not get in that state and cause that error without breaking the mirroring setup. So, you definitely have other user databases that are not being mirrored - where you are attempting to backup the transaction logs.

The maintenance plan task should exclude all databases that are not in full recovery model - when you check the All User Databases option. What that indicates to me is that you have another user database, in full recovery - where there is a process that is breaking the log chain. This can happen when a process switches the recovery model from full to simple - then back to full.

I would recommend you have separate plans (either maintenance plans - or agent jobs) for each type. One plan for system databases, one plan (at least) for user databases and one plan (at least) for transaction logs. Separate out multiple user database plans depending on the type of databases and the requirements for each.
Go to Top of Page

Hommer
Aged Yak Warrior

784 Posts

Posted - 04/02/2013 :  13:59:34  Show Profile  Reply with Quote
Aha, turned out ReportServer is considered user dbs. And the full backup did not have it in there.

quote:
Originally posted by Hommer

That is a great point!

And, here are what I found:

Full: Selected: user_db1, user_db2, master, model, msdb
TLog: All user dbs.

If two of them have to be the same, I should remove master, model and msdb and put them into another full backup plan.



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.05 seconds. Powered By: Snitz Forums 2000