December 13, 2007 at 8:57 am
I have a stored procedure that does the following differential backup each night
BACKUP DATABASE @Name TO DISK = @sql WITH DIFFERENTIAL , NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM
This has been giving me the following error on these databases:
Msg 3035, Level 16, State 1, Procedure s_DiffBackupAllDatabases, Line 153
Cannot perform a differential backup for database "sampledb", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.
I have verified that the recovery mode on these databases is set to Full. I've also verified that the full backup was succesfully created. I've tried recreating the full backups and don't have any problems with doing this, but the differential backup still fails. The command to create the full backup is:
BACKUP DATABASE @Name TO DISK = @sql WITH INIT
I am doing this on SQL Server 2005 Express with Service Pack 2. I noticed that there was a hotfix for this same issue, but it was for Service Pack 1. Does anyone have any insight on this?
December 14, 2007 at 10:36 am
If you take your backup command out of the stored procedure, and supply a db name, does the differential backup work?
It may have something to do with the variable types you have choosen for @Name in the stored procedure. Is @Name variable
unicode or ansi? And is each variable the same length in both stored procedures? I am thinking the name doesnt match the
msdb..BACKUPSET record.
I just created two stored procedures with your tsql and they ran fine.
December 14, 2007 at 11:08 am
Thanks for the reply. Actually I will have to try this again tomorrow. I recently did a full backup of all the databases, and it seems like whenever I do that, the differential backup works fine until the next day.
That brings up another idea. In the SQL Server error log, there are messages at 3am each night saying that a full backup of each database is being made to a virtual device. This is also when we have Jungle Disk scheduled to do an offsite backup of our data. I'm not sure how Jungle Disk performs the backup, but would it make sense that they do a full backup to the virtual disk at night when doing the backup. Then, when doing a differential backup in the morning, SQL Server can't find the full backup because the virtual disk is no longer present. It then says it can't do the differential backup because it doesn't have the most current full backup.
I'm pretty new to SQL Server, but this would explain why the stored procedure works occasionally on our system, works all the time at another office, and why it's not seeing the full backup. Any thoughts on this? Thanks again for all your help.
December 14, 2007 at 1:06 pm
I don't believe the full backup needs to be available, it just needs to have been created.
The message refers to "sampledb" - is this perhaps a database created daily, or one that doesn't get covered by your full backup schedules? If that's the case and you really don't need it, then you can just exclude it from your differential script.
December 14, 2007 at 1:15 pm
This is definetly the problem. query the msdb..BACKUPSET folder and see the last place the file was backup.
Then see if you can get to it. You may have to do another backup after their process completes in order for you stored procs to work.
December 18, 2007 at 9:23 am
Adam Haines (12/14/2007)
This is definetly the problem. query the msdb..BACKUPSET folder and see the last place the file was backup.Then see if you can get to it. You may have to do another backup after their process completes in order for you stored procs to work.
This was definitely the issue I was having. The physical device name for these nightly backups were {C3C858D9-BB0B-438F-BE21-7BF8263D4C9B}6 for example. I will speak with my boss about a different backup procedure to get around this issue, but I really appreciate the help. I had no idea there were backup tables that would show me these details, so it's great to learn some new stuff.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply