I setup some Maintenance Plans on a SQL Server 2005 machine with the following parameters:

System DB's: every evening at 21:00
User DB's: full backup every evening at 21:40
Differential Backups every 20 mins from 08:00 to 19:00
SQL backups deleted after 3 days (they are backed up to tape and synched to another machine)

When I was told by the System Administrator that there were 3041 errors and logged onto the server, I initially thought it was a space problem: there was only 15.6 GB free on a 250 GB machine ... but the full backups work just fine, no errors!

Here is the SQL Server info:

Microsoft SQL Server Management Studio - 9.00.5000.00
Microsoft Analysis Services Client Tools - 2005.090.5000.00
Microsoft Data Access Components (MDAC) - 6.1.7601.17514 (win7sp1_rtm.101119-1850)
Microsoft MSXML - 3.0 6.0
Microsoft Internet Explorer - 9.0.8112.16421
Microsoft .NET Framework - 2.0.50727.5456
Operating System - 6.1.7601

All Full Backups complete without errors and all differential backups create errors. Here is a sample:

Log Name: Application
Source: MSSQLSERVER
Date: 7/5/2012 4:20:02 PM
Event ID: 3041
Task Category: (6)
Level: Error
Keywords: Classic
User: N/A
Computer: CLIAS3.CompanyName.local
Description:
BACKUP failed to complete the command BACKUP DATABASE MaConfig WITH DIFFERENTIAL. Check the backup application log for detailed messages.
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/ev...">
<System>
<Provider Name="MSSQLSERVER" />
<EventID Qualifiers="49152">3041</EventID>
<Level>2</Level>
<Task>6</Task>
<Keywords>0x80000000000000</Keywords>
<TimeCreated SystemTime="2012-07-05T20:20:02.000000000Z" />
<EventRecordID>146642</EventRecordID>
<Channel>Application</Channel>
<Computer>CLIAS3.CompanyName.local</Computer>
<Security />
</System>
<EventData>
<Data>BACKUP DATABASE MaConfig WITH DIFFERENTIAL</Data>
<Binary>E10B0000100000000700000043004C0049004100530033000000090000004D00610043006F006E006600690067000000</Binary>
</EventData>
</Event>


Any idea as to cause & solution?

Thanks very much,

John

John Marrett
Crystal Reports Trainer & Consultant
Maximizer CRM Trainer & Consultant
www.e-reporting.ca

can you post the sql error log and include entries from just before you see the error.

Biglebowskis Razor - with all things being equal if you still can't find the answer have a shave and go down the pub.

Is the database is FULL Recovery mode or SIMPLE Recovery mode? If it is in Simple, you won't be able to do Differential backups.

If it is in FULL mode, you should also be doing Transaction Log backups.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875: What should I know before I post?

I have attached a copy of the ERRORLOG file as requested...

Can one of you please point me to the best URL on setting up SQL Server Maintenance Plans? I have been setting them up the same way for years and it seems that there may a better way to do this!

Thanks very much,

John

John Marrett
Crystal Reports Trainer & Consultant
Maximizer CRM Trainer & Consultant
www.e-reporting.ca

• https://www.yousendit.com/download/QlVnY050NmNPSHdUWThUQw

Personally - i would say transaction log backups every 20 mins.

Now depending the length of time required for a full backup - either a diff backup nightly and a full backup at the weekend..... or if time\space isnt issue then full backup nightly.

Obviously whatever you choose do a test restore to a PITR so that you are happy the backup rountine works.

Dan

----------------------------------------
www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------