none
Backup Maintenance Plans failed! SQL 2005 (x64)

    Question

  • The Maintenance Plan Full Backup is normal, but the diff backup is always failed! pls help!

    My system is Windows Server 2003 R2 ,Enterprise x64 Edition SP2,
    and The SQL Server: Enterprise Edtion(64-bit), SP2, 9.00.3042.00


    NEW COMPONENT OUTPUT
    Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3042
    Report was generated on "10.10.1.100".
    Maintenance Plan: DiffBackEveryday
    Duration: 00:00:03
    Status: Warning: One or more tasks failed..
    Details:
    Back Up Database (Differential) (10.10.1.100)
    Backup Database on Local server connection
    Databases: CentralMessageDB,EbayAPIDB,EmailAttachmentDb,EmailDb,IncomingEmailDb
    Type: Differential
    Append existing
    Task start: 2009-08-27T18:10:09.
    Task end: 2009-08-27T18:10:10.
    Failed:(-1073548784) Executing the query "BACKUP DATABASE [EbayAPIDB] TO  DISK = N'D:\\DBBackup\\EbayAPIDB\\EbayAPIDB_backup_200908271810.bak' WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME = N'EbayAPIDB_backup_20090827181009', SKIP, REWIND, NOUNLOAD,  STATS = 10
    " failed with the following error: "Cannot perform a differential backup for database "EbayAPIDB", 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''D:\DBBackup\CentralMessageDB''
    GO
    EXECUTE master.dbo.xp_create_subdir N''D:\DBBackup\EbayAPIDB''
    GO
    EXECUTE master.dbo.xp_create_subdir N''D:\DBBackup\EmailAttachmentDb''
    GO
    EXECUTE master.dbo.xp_create_subdir N''D:\DBBackup\EmailDb''
    GO
    EXECUTE master.dbo.xp_create_subdir N''D:\DBBackup\IncomingEmailDb''
    GO
    BACKUP DATABASE [CentralMessageDB] TO  DISK = N''D:\DBBackup\CentralMessageDB\CentralMessageDB_backup_200908271810.bak'' WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME = N''CentralMessageDB_backup_20090827181009'', SKIP, REWIND, NOUNLOAD,  STATS = 10
    GO
    BACKUP DATABASE [EbayAPIDB] TO  DISK = N''D:\DBBackup\EbayAPIDB\EbayAPIDB_backup_200908271810.bak'' WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME = N''EbayAPIDB_backup_20090827181009'', SKIP, REWIND, NOUNLOAD,  STATS = 10
    GO
    BACKUP DATABASE [EmailAttachmentDb] TO  DISK = N''D:\DBBackup\EmailAttachmentDb\EmailAttachmentDb_backup_200908271810.bak'' WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME = N''EmailAttachmentDb_backup_20090827181009'', SKIP, REWIND, NOUNLOAD,  STATS = 10
    GO
    BACKUP DATABASE [EmailDb] TO  DISK = N''D:\DBBackup\EmailDb\EmailDb_backup_200908271810.bak'' WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME = N''EmailDb_backup_20090827181009'', SKIP, REWIND, NOUNLOAD,  STATS = 10
    GO
    BACKUP DATABASE [IncomingEmailDb] TO  DISK = N''D:\DBBackup\IncomingEmailDb\IncomingEmailDb_backup_200908271810.bak'' WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME = N''IncomingEmailDb_backup_20090827181009'', SKIP, REWIND, NOUNLOAD,  STATS = 10
    • Edited by Josh Ji Saturday, August 29, 2009 2:57 AM modify
    Saturday, August 29, 2009 2:39 AM

Answers

  • <<But I see the 'SQL Server VSS Writer' in the Windows Services show the status is started, and the start type is Auto.>>

    Yes, and my point is that you *don't* want this service started. Try stopping it, set startup to manual or disabled and see if that sorts out your problem.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    • Marked as answer by Josh Ji Wednesday, September 02, 2009 1:39 AM
    Sunday, August 30, 2009 9:05 AM
    Moderator

All replies

  • Did you ever do a full backup of the EbayAPIDB database in the first place? Are you using any type of snapshot backups (which could be considered full backups by SQL Server) - does stopping the SQL Server VSS Writer service change the situation?
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Saturday, August 29, 2009 8:18 AM
    Moderator
  • Yes , and the full backup of the database is normal.
    I don't know how to stop or start the SQL Server VSS Writer service!!
    Saturday, August 29, 2009 10:11 AM
  • I assume you know how to start and stop a Windows Service (if not, talk to some Windows admin). With SQL Server comes a service named something like "SQL Server VSS Writer". My guess is that this service interferes with your backups, since SQL Server will consider the snapshot this does (with interaction with windows VSS support) a database back. Stopping this service will stop SQL Server consider OS snapshots from being SQL Server bacups.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Saturday, August 29, 2009 11:13 AM
    Moderator
  • Tibork, Thanks a lot, But I see the 'SQL Server VSS Writer' in the Windows Services show the status is started, and the start type is Auto.
    It is worth mentioning,the Full backup database is normal in the Maintenance Plan and manual, and the diff backup is also normal when execute a full backup in the same day,just the next day the execute plan will failed
    Sunday, August 30, 2009 8:55 AM
  • <<But I see the 'SQL Server VSS Writer' in the Windows Services show the status is started, and the start type is Auto.>>

    Yes, and my point is that you *don't* want this service started. Try stopping it, set startup to manual or disabled and see if that sorts out your problem.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    • Marked as answer by Josh Ji Wednesday, September 02, 2009 1:39 AM
    Sunday, August 30, 2009 9:05 AM
    Moderator
  • ok, I have been stopped it ,and will check it later, thanks!
    Sunday, August 30, 2009 9:23 AM
  • Hi TiborK, The diff backup show the same error also!
    Monday, August 31, 2009 1:40 AM
  • Can you please check the properties for this database and see when was the last time you made a full backup? Based on the name of your maintenance plan it's possible that this plan, for the differentials, is different from the plan you use for full backups. So I'm thinking about the possibility that your plan for full backups is skipping this database...
    Monday, August 31, 2009 2:12 AM
  • And did you do a full backup for that database since you stopped the service? See Rocardo's post.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    • Marked as answer by Josh Ji Wednesday, September 02, 2009 1:38 AM
    • Unmarked as answer by Josh Ji Wednesday, September 02, 2009 1:39 AM
    Monday, August 31, 2009 5:15 AM
    Moderator
  • No,not skipped any database.thanks!
    Monday, August 31, 2009 10:20 AM
  • I did the full backup on last SAT ,and stopped the service is on SUN.may be the error is here.
    So, today i have did a full backup again , then ,will check the diff backup in tomorrow!
    thanks!
    Monday, August 31, 2009 10:27 AM
  • TiborK, The problem was solved by your method! thanks!
    Wednesday, September 02, 2009 1:40 AM