July 6, 2009 at 3:04 pm
I have spent all day researching this, seen several topics related to it, tried several things, but nothing seems to work for me. I have a weekly maintenance plan that runs every Saturday morning and does a full backup of my DB. Then daily, I have a maintenance plan running that should do a differential backup; however, I keep getting this message:
"Cannot perform a differential backup for database "DatabaseName", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I can run a full backup and then go right back and run an differential backup, the full is always successful and the differential always errors out with that same message. I'm lost here and would love it if anybody would help!!!
Thank you for your time!
Jordon
July 6, 2009 at 3:07 pm
Can you run the differential backup manually from SSMS?
July 6, 2009 at 3:10 pm
Afraid not! I can't get a differential backup to run anyway that I try it!!!
July 6, 2009 at 3:14 pm
Are you sure the full backup completes okay? Is there anything else happening to the database between the full backup and differential backup? Just for s and g's have your trid running them back to back from SSMS?
July 6, 2009 at 3:17 pm
I have tried running the back to back. I first run the Full backup, it says that its successful, everything looks perfect on it. Then I run the Diff and get that error message.
July 6, 2009 at 3:20 pm
Please show us all the code you use to run the backups back to back in SSMS.
July 6, 2009 at 3:30 pm
Ok, for the Full backup, I'm running:
EXECUTE master.dbo.xp_create_subdir N''C:\sqlbackup\master''
GO
EXECUTE master.dbo.xp_create_subdir N''C:\sqlbackup\model''
GO
EXECUTE master.dbo.xp_create_subdir N''C:\sqlbackup\msdb''
GO
EXECUTE master.dbo.xp_create_subdir N''C:\sqlbackup\TEST2''
GO
EXECUTE master.dbo.xp_create_subdir N''C:\sqlbackup\TEST1''
GO
BACKUP DATABASE [master] TO DISK = N''C:\sqlbackup\master\master_backup_200907061538.bak'' WITH NOFORMAT, NOINIT, NAME = N''master_backup_20090706153829'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [model] TO DISK = N''C:\sqlbackup\model\model_backup_200907061538.bak'' WITH NOFORMAT, NOINIT, NAME = N''model_backup_20090706153829'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [msdb] TO DISK = N''C:\sqlbackup\msdb\msdb_backup_200907061538.bak'' WITH NOFORMAT, NOINIT, NAME = N''msdb_backup_20090706153829'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [TEST2] TO DISK = N''C:\sqlbackup\TEST2\TEST2_backup_200907061538.bak'' WITH NOFORMAT, NOINIT, NAME = N''TEST2_backup_20090706153829'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [TEST1] TO DISK = N''C:\sqlbackup\TEST1\TEST1_backup_200907061538.bak'' WITH NOFORMAT, NOINIT, NAME = N''TEST1_backup_20090706153830'', SKIP, REWIND, NOUNLOAD, STATS = 10
Then for the Diff backup, I'm running:
EXECUTE master.dbo.xp_create_subdir N''C:\sqlbackup\TEST2''
GO
EXECUTE master.dbo.xp_create_subdir N''C:\sqlbackup\TEST1''
GO
EXECUTE master.dbo.xp_create_subdir N''C:\sqlbackup\model''
GO
EXECUTE master.dbo.xp_create_subdir N''C:\sqlbackup\msdb''
GO
BACKUP DATABASE [TEST2] TO DISK = N''C:\sqlbackup\TEST2\TEST2_backup_200907061544.bak'' WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N''TEST2_backup_20090706154400'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N''TEST2'' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N''TEST2'' )
if @backupSetId is null begin raiserror(N''Verify failed. Backup information for database ''''TEST2'''' not found.'', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N''C:\sqlbackup\TEST2\TEST2_backup_200907061544.bak'' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO
BACKUP DATABASE [TEST1] TO DISK = N''C:\sqlbackup\TEST1\TEST1_backup_200907061544.bak'' WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N''TEST1_backup_20090706154401'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N''TEST1'' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N''TEST1'' )
if @backupSetId is null begin raiserror(N''Verify failed. Backup information for database ''''TEST1'''' not found.'', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N''C:\sqlbackup\TEST1\TEST1_backup_200907061544.bak'' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO
BACKUP DATABASE [model] TO DISK = N''C:\sqlbackup\model\model_backup_200907061544.bak'' WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N''model_backup_20090706154401'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N''model'' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N''model'' )
if @backupSetId is null begin raiserror(N''Verify failed. Backup information for database ''''model'''' not found.'', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N''C:\sqlbackup\model\model_backup_200907061544.bak'' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO
BACKUP DATABASE [msdb] TO DISK = N''C:\sqlbackup\msdb\msdb_backup_200907061544.bak'' WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N''msdb_backup_20090706154401'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N''msdb'' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N''msdb'' )
if @backupSetId is null begin raiserror(N''Verify failed. Backup information for database ''''msdb'''' not found.'', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N''C:\sqlbackup\msdb\msdb_backup_200907061544.bak'' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
July 6, 2009 at 3:35 pm
Just to be sure, are you running anything between these two sets of code?
July 6, 2009 at 5:26 pm
You cannot backup the master database with a differential backup. You can only back up the master database with a full.
There is no reason to backup system databases with anything other than a daily full backup. You could even go with a weekly full backup schedule, but if there are any new jobs created they could be lost (msdb), or any system configuration changes (e.g. logins) then they would be lost.
I would recommend using two separate maintenance plans. One for system DB's only, and the other for User DB's.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 6, 2009 at 7:22 pm
Lynn, I'm positive there is nothing running in between those jobs, I manually ran them back to back.
Jeffrey, I'm not doing a differential backup on the Master DB. If you look at the code, I'm only doing that on the full backup, which runs once a week and the daily that doesn't include the Master is the differential backup jobs. With that being said, that still doesn't fix my issue that I'm having!
Any suggestions would be great!
Thanks,
Jordon
July 6, 2009 at 7:51 pm
Jordan,
I am just making sure that there isn't something occuring to the database between the time you complete the full backup and the differential.
Please post the complete error message you get while attempting to run the full and differential backups back to back from SSMS. Only run the full and differential for ONE of the databases, preferably one of the USER databases.
July 7, 2009 at 9:53 am
Using the COPY_ONLY option would cause that problem, however I didn't see that in the code.
July 7, 2009 at 11:30 am
Have you applied the patch found at: http://support.microsoft.com/kb/921106 yet?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply