June 20, 2011 at 4:48 am
Im trying to take differential back up on sql 2005 server with SP4 (Workgroup Edition) but it gives me below error...
Msg 3035, Level 16, State 1, Line 1
Cannot perform a differential backup for database "ABC", because a current database backup does not exist.
Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Fullbackup exists but its still giving above message. I found article "http://support.microsoft.com/kb/921106" but it seems this is applicable for sql server 2005 with no service pack.
I got below error when I tried to install above hotfix.
"This update requires service pack 0. The service pack for product instance MSSQLSERVER is 4.
Download the update for service pack 4."
This issue is going on for long and not able find solution for this ....
I appreciate your help
Thanks,
Shashi Bhushan.
June 20, 2011 at 5:02 am
polasabhushan (6/20/2011)
...Fullbackup exists but its still giving above message...
When was the full backup taken? Did anything break LSN?
June 20, 2011 at 5:06 am
Every tuesday full backup will be taken. Then differential backup script works fine for a day atleast and then this error is thrown
Msg 3035, Level 16, State 1, Line 1 Cannot perform a differential backup for database "ABC", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option. Msg 3013, Level 16, State 1, Line 1 BACKUP DATABASE is terminating abnormally.
June 20, 2011 at 5:40 am
I suspect some other processes are being run. Have you checked all scheduling to validate all the jobs that are run against the server? Have you checked to see if there indications in the error log?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 20, 2011 at 6:33 am
It definitely sounds to me like someone is doing something, like changing the recovery model back and forth or restoring the database, both of which can break the LSN.
June 20, 2011 at 6:40 am
Check in this table for backups that shouldn't have been running.
This script is not tailored to do exactly that but I'm sure you'll figure out the rest in no time.
SELECT
bs.database_name
, DATEADD(D , 0 , DATEDIFF(D , 0 , bs.backup_start_date)) AS BackupDate
, CONVERT(DECIMAL(18 , 1) , DATEDIFF(s , bs.backup_start_date ,
bs.backup_finish_date)
/ 60.0) AS MinutesForBackup
, CONVERT(DECIMAL(18 , 3) , bs.backup_size / 1024 / 1024 / 1024) AS GB_backup_size
, ROW_NUMBER() OVER ( PARTITION BY bs.database_name ORDER BY bs.backup_start_date ) AS seqFirst
, ROW_NUMBER() OVER ( PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC ) AS seqLast
FROM
msdb.dbo.backupset bs
WHERE
bs.[type] = 'D'
-- AND name IS NULL
June 20, 2011 at 6:51 am
Thank you both "Grant Fritchey" and "Brandie Tarvin" for your valuable suggestions.
I have not found anything on errorlog , probably not sure where to look and what to look. 🙁
But I can say that, before taking a full or differential backup, we are truncating log by changing recovery model from bulk-logged to simple and again to bulk-logged.
As per "Brandie Tarvin", if this causes issues then I will remove the truncating code and check whether this resolves the issue and reply back to this post on my findings.
Thanks again, I appreciate it.
June 20, 2011 at 6:56 am
If you don't need the log to allow point in time restore then move to simple.
That means that if you do weekly fulls and nothing else then you are prepared to lose UP TO 7 full days of data.
If that is unacceptable then you need to do regular log backups... in which case I'd possibly move to FULL.
You can use my script to filter on the name column, I know backupexec uses it to name its backups. You might have yet another person screwing with this.
June 20, 2011 at 7:00 am
polasabhushan (6/20/2011)
Thank you both "Grant Fritchey" and "Brandie Tarvin" for your valuable suggestions.I have not found anything on errorlog , probably not sure where to look and what to look. 🙁
But I can say that, before taking a full or differential backup, we are truncating log by changing recovery model from bulk-logged to simple and again to bulk-logged.
As per "Brandie Tarvin", if this causes issues then I will remove the truncating code and check whether this resolves the issue and reply back to this post on my findings.
Thanks again, I appreciate it.
And that's your problem right there.
Why keep the log if you're not doing log backups?
For that matter, why aren't you doing log backups? What you're telling the business is, in an emergency, we'll only guarantee you yesterday's data. I'd make sure your business is OK with that much data loss. If so, set the recovery model to simple and these problems will stop.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 20, 2011 at 7:00 am
polasabhushan (6/20/2011)
But I can say that, before taking a full or differential backup, we are truncating log by changing recovery model from bulk-logged to simple and again to bulk-logged.
That's your problem right there. Once you move back to Bulk Logged, you need to do another Full Backup to reinstate a new LSN chain. Simple breaks the former LSN.
June 20, 2011 at 7:03 am
Brandie Tarvin (6/20/2011)
polasabhushan (6/20/2011)
But I can say that, before taking a full or differential backup, we are truncating log by changing recovery model from bulk-logged to simple and again to bulk-logged.That's your problem right there. Once you move back to Bulk Logged, you need to do another Full Backup to reinstate a new LSN chain. Simple breaks the former LSN.
OK. Get out of my head. It's crowded in there already.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 20, 2011 at 7:05 am
Grant Fritchey (6/20/2011)
Brandie Tarvin (6/20/2011)
polasabhushan (6/20/2011)
But I can say that, before taking a full or differential backup, we are truncating log by changing recovery model from bulk-logged to simple and again to bulk-logged.That's your problem right there. Once you move back to Bulk Logged, you need to do another Full Backup to reinstate a new LSN chain. Simple breaks the former LSN.
OK. Get out of my head. It's crowded in there already.
No offense, but BOTH of you get out of mine! :w00t:
June 20, 2011 at 7:05 am
Grant Fritchey (6/20/2011)
polasabhushan (6/20/2011)
But I can say that, before taking a full or differential backup, we are truncating log by changing recovery model from bulk-logged to simple and again to bulk-logged.Why keep the log if you're not doing log backups?
Ditto to this. It makes sense to change Recovery model from FULL to Bulk Logged for high import / export operations, but why are you changing from BL to Simple? Truncating the log is not a business case for this type of change. And it's a dangerous change to boot.
If you don't need to keep the log, stay in Simple Recovery mode. I believe you can still do a type of Differential backup in Simple recovery mode. If you do need to restore log information, then I suggest taking Transaction Log backups.
June 20, 2011 at 8:11 am
You can restart a log chain by issuing a differential backup, so a switch from bulk-logged to simple and back again will not break a differential chain. The base LSN for a differential is held in the primary file.
Query sys.databases to get info on the base full backup for the differential chain.
There is something else going on here.
Agreed though that the OP should not be doing this log truncation
---------------------------------------------------------------------
June 21, 2011 at 7:01 am
george sibbald (6/20/2011)
You can restart a log chain by issuing a differential backup,
indeed, see Paul Randalls article at this link
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply