Author |
Topic |
vaddi
Posting Yak Master
145 Posts |
Posted - 2006-12-14 : 11:39:55
|
HelloI have scheduled T-Log backup for every hour from 6.00 AM till 9.00 PMBut 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 |
|
|
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]GObackup log EMASUGTST with truncate_onlyUSE [EMASUGTST]GODBCC SHRINKFILE (N'emasugtst_log' , 50)GOAnd 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. |
|
|
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 |
|
|
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]GObackup log EMASUGTST with truncate_onlyUSE [EMASUGTST]GODBCC SHRINKFILE (N'emasugtst_log' , 50)GOAnd 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 |
|
|
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]GObackup log EMASUGTST with truncate_onlyUSE [EMASUGTST]GODBCC SHRINKFILE (N'emasugtst_log' , 50)GOAnd 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 |
|
|
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 |
|
|
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 |
|
|
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 |
|
|
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 backupEvery 15 minutes transaction log backupWe 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 DBNameTO DISK = '...'WITH INITBACKUP LOG DBNameTO DISK = '...'WITH INITNotice 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.aspxTara Kizer |
|
|
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. |
|
|
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+worksKristen |
|
|
|