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
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 T-Log Backup Error

Author  Topic 

vaddi
Posting Yak Master

145 Posts

Posted - 2006-12-14 : 11:39:55
Hello

I have scheduled T-Log backup for every hour from 6.00 AM till 9.00 PM

But the Log backup is failing with the following error .

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

Till yestarday evening they were working fine. Till yestarday what I did was , have got database backup jobs of the DB every night and T-Log backup every one hour. Yestarday I created the maintenance plan with DB backup ,cleaning up old DB backups, Truncating the Log-File etc...

Did the creation the maintenance plan make any difference.

Or what might be the problem.

Thanks

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-12-14 : 11:48:05
Why are you "Truncating the Log-File" if you are doing transaction log backups?



CODO ERGO SUM
Go to Top of Page

vaddi
Posting Yak Master

145 Posts

Posted - 2006-12-14 : 12:00:44
At the night , after doing the database backup for the T-Log I am using the command :

USE [EMASUGTST]
GO
backup log EMASUGTST with truncate_only
USE [EMASUGTST]
GO
DBCC SHRINKFILE (N'emasugtst_log' , 50)
GO

And for the hourly back T-Log backups I have the command :

BACKUP LOG [ABSOLUTE] TO DISK = N'D:\MSSQL\BACKUP\ABSOLUTE\absolute_log.bak' WITH NOINIT , NOUNLOAD , NAME = N'absolute hourly log backup', SKIP , STATS = 10, NOFORMAT , NO_TRUNCATE

Am I doing something wrong in the statments.Like I wanted the T-Log files to be over written every one hour. Is this right or wrong. Or should I backup the T-Logs and delete them the next day or something like that.

Thanks in advance.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-12-14 : 12:09:38
See also: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=How%20Backup%20works,What%20type%20of%20backup%20should%20I%20use,Automating%20Backups,Backup
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-12-14 : 12:15:59
quote:
Originally posted by vaddi

At the night , after doing the database backup for the T-Log I am using the command :

USE [EMASUGTST]
GO
backup log EMASUGTST with truncate_only
USE [EMASUGTST]
GO
DBCC SHRINKFILE (N'emasugtst_log' , 50)
GO

And for the hourly back T-Log backups I have the command :

BACKUP LOG [ABSOLUTE] TO DISK = N'D:\MSSQL\BACKUP\ABSOLUTE\absolute_log.bak' WITH NOINIT , NOUNLOAD , NAME = N'absolute hourly log backup', SKIP , STATS = 10, NOFORMAT , NO_TRUNCATE

Am I doing something wrong in the statments.Like I wanted the T-Log files to be over written every one hour. Is this right or wrong. Or should I backup the T-Logs and delete them the next day or something like that.

Thanks in advance.


You don't seem to understand the concepts of what transaction log backups are for and how they are used for recovery.

You really need to read the sections in SQL Server Books Online that explain this, and make sure you understand the concepts before starting to implement a backup plan.







CODO ERGO SUM
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-12-14 : 13:22:21
quote:
Originally posted by vaddi

At the night , after doing the database backup for the T-Log I am using the command :

USE [EMASUGTST]
GO
backup log EMASUGTST with truncate_only
USE [EMASUGTST]
GO
DBCC SHRINKFILE (N'emasugtst_log' , 50)
GO

And for the hourly back T-Log backups I have the command :

BACKUP LOG [ABSOLUTE] TO DISK = N'D:\MSSQL\BACKUP\ABSOLUTE\absolute_log.bak' WITH NOINIT , NOUNLOAD , NAME = N'absolute hourly log backup', SKIP , STATS = 10, NOFORMAT , NO_TRUNCATE

Am I doing something wrong in the statments.Like I wanted the T-Log files to be over written every one hour. Is this right or wrong. Or should I backup the T-Logs and delete them the next day or something like that.

Thanks in advance.



Yes you are doing something wrong. You are invalidating your backup plan by running the with truncate_only statement, hence the error message. You should not be running that statement at all. The only time you need to run this is when you're getting the error that the tlog is full.

Why on earth are you shrinking the tlog file after that statement!?You should not be doing that. You are creating performance issues due to this.

And why do you want to overwrite the files each day!? Are you backing them up to tape or copying them over to another location first?

Tara Kizer
Go to Top of Page

vaddi
Posting Yak Master

145 Posts

Posted - 2006-12-14 : 13:42:35
Thanks Tara for the advice.

I will correct my plan.

The nightly backups and log backkups are written on to the disc and from there to the tape.

But the hourly backups are only on the disc. so I should be removing those log backups at the end of each day.

Thanks
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-12-14 : 13:45:22
I'd recommend keeping all backups on disk for at least 2 days, preferrably at least 5 days though. You should add disk space if you can only retain one day of backups.

Tara Kizer
Go to Top of Page

vaddi
Posting Yak Master

145 Posts

Posted - 2006-12-14 : 14:00:58
What about the Hourly Log backups. I think those can be removed at the end of the day as the log and Db backup are preformed every night.

And what sql command can I use the log backups.

Sorry for throwing too many questions. I have started the log backups on our Production DB's today and got confused with the T-Log backups , Turncate and No_Turncate and will start pouring into books.

Thanks
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-12-14 : 14:33:29
Hourly log backups are the same thing as a log backup. And no they shouldn't be removed each day. What is it that you are doing with the log at night that is different than what you are doing hourly? Show us the commands for both actions.

Do not bother putting truncate or any form of it in your backup log command.

Here is what our plan looks like in production:

Nightly full backup
Every 15 minutes transaction log backup

We keep 2 days of retention of all of those files on disk. Each day they are swept to tape as well. So we've got 2 full backups on disk plus 4*24*2 of transaction log backups on disk. That's 194 files on disk.

Here are sample commands that are run:

BACKUP DATABASE DBName
TO DISK = '...'
WITH INIT

BACKUP LOG DBName
TO DISK = '...'
WITH INIT

Notice there isn't anything about truncate in them. We just use the default, which is what we want and what you should be doing.

For the ... part, we have the database name and a timestamp put in the filename.

Check out my maintenance plans to see more of what we run in production. I've completely rewritten isp_Backup though, but it should give you an idea. I consolidated all of the code from isp_Backup and the tlog one. But here's the link to them:

http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Tara Kizer
Go to Top of Page

vaddi
Posting Yak Master

145 Posts

Posted - 2006-12-14 : 14:55:27
Thanks Tara for the information.

I will go through them and change my plans accordingly.

Thank you once again.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-12-15 : 02:45:07
One reason you should not delete your Log backups is in case you need to restore and your Full backup is damaged. You can then restore for an earlier Full backups and all the following Log backups.

You may also want to restore to a point-in-time some days ago - for example to examine what happened (rather than to make the users re-enter several days of data again!) You need your Log backups for that scenario too.

Please note that doing a Full backup each night is only "safe" if you successfully restore the full backup on a different computer and DBCC CHECKDB reports no errors (just doing a restore does not prove that the database is not damaged)

See also: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=How+Backup+works

Kristen
Go to Top of Page
   

- Advertisement -