Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Tlog backup false alarm or not?

Author  Topic 

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-04-02 : 10:54:18
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

5072 Posts

Posted - 2013-04-02 : 11:08:06
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

808 Posts

Posted - 2013-04-02 : 11:14:38
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

Wsql
Starting Member

5 Posts

Posted - 2013-04-02 : 11:14:54
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

5072 Posts

Posted - 2013-04-02 : 12:10:48
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

819 Posts

Posted - 2013-04-02 : 13:01:33
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

808 Posts

Posted - 2013-04-02 : 13:59:34
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
   

- Advertisement -