SQL Server Maintenance Wizard Backup Plans

Question

I used the SQL Server Maintenance wizard (along with a book) to generate both Full and Differentail backups. The Full backup runs on Monday morning and the Differentials run Tuesday through Saturday. The Full backup runs fine and generates the following email report...

Microsoft(R) Server Maintenance Utility (Unicode) Version 10.0.2531 Report was generated on "WIN-F9U4VJXGWF5".
Maintenance Plan: FIREDataFB
Duration: 00:31:31
Status: Succeeded.
Details:
Clean Up History (WIN-F9U4VJXGWF5)
Cleanup history on Local server connection History type: Backup,Job,Maintenance Plan
Age: Older than 1 Weeks
Task start: 2010-10-11T03:30:03.
Task end: 2010-10-11T03:30:03.
Success
Command:declare @dt datetime select @dt = cast(N''2010-10-04T03:30:02'' as datetime) exec msdb.dbo.sp_delete_backuphistory @dt GO EXEC msdb.dbo.sp_purge_jobhistory  @oldest_date=''2010-10-04T03:30:02''
GO
EXECUTE msdb..sp_maintplan_delete_log null,null,''2010-10-04T03:30:02''
GO

Back Up Database (Full) (WIN-F9U4VJXGWF5) Backup Database on Local server connection
Databases: FIREData
Type: Full
Append existing
Task start: 2010-10-11T03:30:03.
Task end: 2010-10-11T04:01:33.
Success
Command:EXECUTE master.dbo.xp_create_subdir N''F:\SQL Backups\FIREData''

GO
BACKUP DATABASE [FIREData] TO  DISK = N''F:\SQL Backups\FIREData\FIREData_backup_2010_10_11_033003_7444370.bak'' WITH NOFORMAT, NOINIT,  NAME = N''FIREData_backup_2010_10_11_033003_7424370'', SKIP, REWIND, NOUNLOAD,  STATS = 10 GO declare @backupSetId as int select @backupSetId = position from msdb..backupset where database_name=N''FIREData'' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N''FIREData'' ) if @backupSetId is null begin raiserror(N''Verify failed. Backup information for database ''''FIREData'''' not found.'', 16, 1) end RESTORE VERIFYONLY FROM  DISK = N''F:\SQL Backups\FIREData\FIREData_backup_2010_10_11_033003_7444370.bak'' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND

GO

The Differential backups do not run. They fail with an error indicating that the Differential jobs cannot find the Full backup result. The following email report is sent when a Differential job fails...

Microsoft(R) Server Maintenance Utility (Unicode) Version 10.0.2531 Report was generated on "WIN-F9U4VJXGWF5".
Maintenance Plan: FIREDataDB
Duration: 00:00:01
Status: Warning: One or more tasks failed.
Details:
Back Up Database (Differential) (WIN-F9U4VJXGWF5) Backup Database on Local server connection
Databases: FIREData
Type: Differential
Append existing
Task start: 2010-10-12T03:30:03.
Task end: 2010-10-12T03:30:03.
Failed:(-1073548784) Executing the query "BACKUP DATABASE [FIREData] TO  DISK = N'F:\\SQL Bac..." failed with the following error: "Cannot perform a differential backup for database "FIREData", 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.

Command:EXECUTE master.dbo.xp_create_subdir N''F:\SQL Backups\FIREData''

GO
BACKUP DATABASE [FIREData] TO  DISK = N''F:\SQL Backups\FIREData\FIREData_backup_2010_10_12_033003_4624370.bak'' WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME = N''FIREData_backup_2010_10_12_033003_4604370'', SKIP, REWIND, NOUNLOAD,  STATS = 10 GO declare @backupSetId as int select @backupSetId = position from msdb..backupset where database_name=N''FIREData'' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N''FIREData'' ) if @backupSetId is null begin raiserror(N''Verify failed. Backup information for database ''''FIREData'''' not found.'', 16, 1) end RESTORE VERIFYONLY FROM  DISK = N''F:\SQL Backups\FIREData\FIREData_backup_2010_10_12_033003_4624370.bak'' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND

GO

I must be missing something obvious because I would not expect the wizard to yield unusable scripts but I cannot find the problem. I have repeated the creation process twice now with book in hand and the Wizard at the ready, but no joy. Any help would be appreciated. I am using SQL Server 2008 Management Studio version 10.0.2531.0 on Windows 7.

Answer 1

I notice that your DIFFERENTIAL backup is being executed 24 hours after your FULL backup has completed. Are you absolutely certain that the FULL backup file exists at the time of the DIFFERENTIAL backup job execution? For example, do you have any clean-up jobs or processes that could have removed the backup file?

I suggest that you attempt to validate that you can indeed perform a FULL backup, followed by a DIFFERENTIAL backup, within your environment by completing the appropriate steps manually using T-SQL. If successful, you can then be sure that the issue exists solely somehow with your Maintenance plan implementation.

To further assist you with your own troubleshooting efforts you may also find the following thread from the MSDN forums to be interesting reading.

Cannot perform a differential backup for database