none
Cannot perform a differential backup for database RRS feed

  • Question

  • hi seniors ,

    I am running sql server 2005 under details

    Microsoft SQL Server 2005 - 9.00.3033.00 (X64)

    Copyright (c) 1988-2005 Microsoft Corporation
     
    Standard Edition (64-bit)
    on Windows NT 5.2 (Build 3790: Service Pack 1)

    When i attempt to take differentail backup i receieved following message


    "Cannot perform a differential backup for database "abc",
     because a current database backup does not exist.
     Perform a full database backup by reissuing BACKUP DATABASE,
     omitting the WITH DIFFERENTIAL option. [SQLSTATE 42000] (Error 3035)
     BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013)."

    when i search this msg on net than i find  that it is microsoft bug 863 so i found hotfixes
    about this problem and tried to  install but unable to install (On installation  screen select feature i am unable to click on check box)

    Can any one help me regarding to this issue .And more is.. full back of db "abc"  exists and i never changed its location nor rename file name.

     

    Regards

     

    Tuesday, February 13, 2007 8:45 AM

Answers

  • ooh i just solve it by the help of follwoing article

     

    http://support.microsoft.com/kb/903643

    Friday, March 2, 2007 11:17 AM
  • I found the solution to my problem. I posted on a thread in Newsgroups on this but thought I'd post my findings here as well.

     

    I couldn't understand why all of a sudden I was getting broken differential chains when NT BACKUP was run using volume shadow copy service after
    upgrading the servers to SP2 of SQL Server 2005 and SP2 of Windows Server 2003. As in this KB http://support.microsoft.com/kb/903643. I hadn't had
    this problem until this upgrade.

    Here's the reason:

    The "Readme for Microsoft SQL Server 2005 Service Pack 2" at
    http://download.microsoft.com/download/2/b/5/2b5e5d37-9b17-423d-bc8f-b11ecd4195b4/ReadmeSQL2005SP2.htm
    states:

    3.2.6 SQL Server 2005 SP2 Changes the SQL Writer Service Default Startup Type and Default State

    Note the following changes to the SQL Writer service default startup type and default state:

    The SQL Writer service startup type is changed from Manual to Automatic.

    The SQL Writer service default state is changed from Stopped to Started.


    So I changed the service back to manual and stopped the service. This of course
    behaved as expected, the VSS didn't try to lock my Full recovery model
    databases and break the differential chain. I can still allow /volsnapSurprisen
    option with my NTBACKUP commands to backup all the other "open" files on the
    volume without breaking the database differential chain.


    The thing that started me looking into this was this line in the BOL article
    that a guy on the newsgroup mentioned.

    This SQL Writer Service is automatically installed but not enabled by
    default. It must be explicitly enabled to run on the server machine and must
    be running at the time that the Volume Shadow Copy Service (VSS) application
    requests a backup or restore. Use the Microsoft Windows Services applet to
    configure the service. The SQL Writer Service installs on all operating
    systems but is only needed on server systems. For Microsoft Windows XP, use
    the MSDE Writer.


    Hope the above helps somebody else out of the same situation.

    Regards
    Nick

    Friday, April 13, 2007 6:32 AM

All replies

  • First action of any backup activity should be full backup. For differential backup u should have a Baseline/Full backup. SO first take full backup and the try differential backup

     backup database SomeDtabase to Disk='D:\Somefile.bak' with init

    the above statment will create the full backup of your database. then do the diffrential backup . Read more in BOL

    Madhu

    Tuesday, February 13, 2007 12:27 PM
    Moderator
  • thanxs madhu , When i saw deatils about with INIT...I think it should work now. But one thing more wheather i should use this clasue with differential backup or not...

     

    thanxs again

    Ahmad Drshen

    Wednesday, February 14, 2007 7:03 AM
  • it depends .... whether u need to overwrite the backup file or not... its again depend upon the Backup strategy of your server....

    Read more about INIT in BOL

     

    Madhu

     

    Wednesday, February 14, 2007 7:19 AM
    Moderator
  • Hi Madhu K Nair and other Seinors ,

                                          Agian  , i am fall in problem 

          " Cannot perform a differential backup for database "AAA", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option. [SQLSTATE 42000] (Error 3035)  BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). 

     I have just Taken backup of db  "AAA" on friday  at 7:30 PM through script

    BACKUP DATABASE AAA TO DISK = 'C:\LV_full_AAA_20071702.bkp'

    WITH INIT , NOUNLOAD ,

    NAME = 'AAAbackup', NOSKIP , STATS = 10,

    NOFORMAT

    DECLARE @i INT

    select @i = position from msdb..backupset where database_name='AAA'

    and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='AAA')

    RESTORE VERIFYONLY FROM DISK =  'C:\LV_full_AAA_20071702.bkp' WITH FILE = @i

     

    Database backup  has done successfully. :)

    Again I ran the script to take differentail backup on friday  at 11:30 PM through script

    BACKUP DATABASE AAA TO DISK = 'C:\LV_Diff_AAA_20071702.bkp'

    WITH INIT , NOUNLOAD ,  DIFFERENTIAL ,

    NAME = 'AAAbackup', NOSKIP , STATS = 10,

    NOFORMAT

    DECLARE @i INT

    select @i = position from msdb..backupset where database_name='AAA'

    and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='AAA')

    RESTORE VERIFYONLY FROM DISK =  'C:\LV_Diff_AAA_20071702.bkp' WITH FILE = @i

     Differentail backup has also  done successfully.

     

    But on saturaday when the script ran for taking differentail backup of  db AAA on 11:30 PM

    it repoted follwoing error

                                      " Cannot perform a differential backup for database "AAA", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option. [SQLSTATE 42000] (Error 3035)  BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). "

     

    while these script are working properly on another production server even there is not mention WITH INIT option with full backup.

    wat the hell is going in taking backup my this server .One thing more i just installed the SP2 exe  name was "SQLServer2005SP2-KB921896-x64-ENU.exe" on my server perhaps i am stucking with this problem after installing this  plz let me know  wat is the solution to fix this hell.

     

    thanxs in advance

    Ahmad Drshen

     

     

    Monday, February 19, 2007 7:49 AM
  • How recent is your full backup of the database?  Have there been any significant changes since then?

    This is CTP2 for SP2.  Did you enable the VarDecimal compression?  This would invalidate the previous full, as the database version number is changed.

     

    Monday, February 19, 2007 11:12 PM
    Moderator
  • thanxs ,VarDecimal compression ! as far as i know this is not supported in standard edtion of Sql server. any how i also try to enable this anf got the messahe that  "

    Vardecimal Storage Format is not supported on Standard Edition (64-bit).

    "

     I just tookfull backup on friday on 7:30  and then took  differential backup on 11:30 on firday it happened successfully.

    But on saturady i was unable to take differential backup again due to this error.

    "Cannot perform a differential backup for database "abc",
     because a current database backup does not exist.
     Perform a full database backup by reissuing BACKUP DATABASE,
     omitting the WITH DIFFERENTIAL option. [SQLSTATE 42000] (Error 3035)
     BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013)."

    plz any other solution

    Tuesday, February 20, 2007 6:51 AM
  • ooh i just solve it by the help of follwoing article

     

    http://support.microsoft.com/kb/903643

    Friday, March 2, 2007 11:17 AM
  • Hi there,

     

    I've had a similar problem with Differential Backups for SQL Server however I am on SP2 for SQL Server 2005 and SP2 of Windows Server 2003. The differential backups work perfectly until an NTBACKUP is run with the snapSurprisen option so that it uses the Volume Shadow Copy. The next differential backup executed by SQL Server then fails with the same sorts of errors reported here in this thread. If I run NTBACKUP with snapSurpriseff then the problem disappears and SQL Server differential backups carry on working as usual.

     

    Kind Regards

    Thursday, March 29, 2007 9:47 AM
  • HI there specially Post21, u r quite right , this problem is due to Ntbackup  utility but unluckly i change the option sanp oFF but still facing problem. can u guide me some Backup command switches which can help in this issue.

     

    Monday, April 2, 2007 11:30 AM
  • I found the solution to my problem. I posted on a thread in Newsgroups on this but thought I'd post my findings here as well.

     

    I couldn't understand why all of a sudden I was getting broken differential chains when NT BACKUP was run using volume shadow copy service after
    upgrading the servers to SP2 of SQL Server 2005 and SP2 of Windows Server 2003. As in this KB http://support.microsoft.com/kb/903643. I hadn't had
    this problem until this upgrade.

    Here's the reason:

    The "Readme for Microsoft SQL Server 2005 Service Pack 2" at
    http://download.microsoft.com/download/2/b/5/2b5e5d37-9b17-423d-bc8f-b11ecd4195b4/ReadmeSQL2005SP2.htm
    states:

    3.2.6 SQL Server 2005 SP2 Changes the SQL Writer Service Default Startup Type and Default State

    Note the following changes to the SQL Writer service default startup type and default state:

    The SQL Writer service startup type is changed from Manual to Automatic.

    The SQL Writer service default state is changed from Stopped to Started.


    So I changed the service back to manual and stopped the service. This of course
    behaved as expected, the VSS didn't try to lock my Full recovery model
    databases and break the differential chain. I can still allow /volsnapSurprisen
    option with my NTBACKUP commands to backup all the other "open" files on the
    volume without breaking the database differential chain.


    The thing that started me looking into this was this line in the BOL article
    that a guy on the newsgroup mentioned.

    This SQL Writer Service is automatically installed but not enabled by
    default. It must be explicitly enabled to run on the server machine and must
    be running at the time that the Volume Shadow Copy Service (VSS) application
    requests a backup or restore. Use the Microsoft Windows Services applet to
    configure the service. The SQL Writer Service installs on all operating
    systems but is only needed on server systems. For Microsoft Windows XP, use
    the MSDE Writer.


    Hope the above helps somebody else out of the same situation.

    Regards
    Nick

    Friday, April 13, 2007 6:32 AM
  • thats quite fine and i have got the solution . thanks for all of participents.
    Monday, April 16, 2007 10:37 AM
  • Hey Nick,

    I'm running SQL Server 2008 but your trick of setting the SQL VSS Writer service to manual fixed this problem for me too.

    Cheers
    Ben

    Monday, April 12, 2010 11:49 PM