none
sql backup Full and differential problem

    Question

  • i have maintenance plan for full backup ones a week, and plan for differential backup every day.

    Problem is when it's execute differential backup it give me this error:

     
    Backup failed for Server 'ULTRA2007'.  (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Backup+Server&LinkId=20476

    ------------------------------
    ADDITIONAL INFORMATION:

    System.Data.SqlClient.SqlError: Cannot perform a differential backup for database "pulmo15032010", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.  (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&LinkId=20476

    I make full backup only on that database (pulmo...)and is successful , and after full backup manualy make differential backup and give me same error again?

    I can see that for other databsae is made differential backup when i execute maintenance plan for differential backup


    Monday, February 27, 2012 3:12 PM

All replies

  • Are you able to supply the syntax you are using to perform the full backup.

    If you use something like the below does it work (please alter drive and folder locations as appropriate)

    BACKUP DATABASE pulmo TO DISK='c:\test\pulmo_full.bak' -- full backup

    GO

    BACKUP DATABASE pulmo TO DISK='c:\test\pulmo_diff.bak' WITH DIFFERENTIAL -- differential backup

    GO

    If you are using maintenance plans, you'll may need to reconfirm that the full backup task for pulmo shows the below

    Monday, February 27, 2012 7:09 PM
  • Are you able to supply the syntax you are using to perform the full backup.

    If you use something like the below does it work (please alter drive and folder locations as appropriate)

    BACKUP DATABASE pulmo TO DISK='c:\test\pulmo_full.bak' -- full backup

    GO

    BACKUP DATABASE pulmo TO DISK='c:\test\pulmo_diff.bak' WITH DIFFERENTIAL -- differential backup

    GO

    If you are using maintenance plans, you'll may need to reconfirm that the full backup task for pulmo shows the below

    i dig this error also:

    ecuting the query "BACKUP DATABASE [pulmo15032010] TO  DISK = N'H:\\Backup\\DifferentialBackup\\pulmo15032010_backup_201202241649.bak' WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME = N'pulmo15032010_backup_20120224164903', SKIP, REWIND, NOUNLOAD,  STATS = 10
    " failed with the following error: "Cannot perform a differential backup for database "pulmo15032010", 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.


    Monday, February 27, 2012 7:34 PM
  • This error is basically saying that SQL Server cannot confirm that a full has occurred for the database pulmo15032010 and therefore it cannot perform a differential backup.

    As above, you'll need to check the full backup task step and you could try execute the below in a new query window in SQL Server Management Studio to see if both a full and differential backup works for the pulmo15032010 database (you can change paths etc if necessary)

    BACKUP DATABASE pulmo15032010 TO DISK='H:\Backup\DifferentialBackup\pulmo15032010_full.bak'-- full backup

    GO

    BACKUP DATABASE pulmo15032010 TO DISK='H:\Backup\DifferentialBackup\pulmo15032010_diff.bak' WITH DIFFERENTIAL -- differential backup

    GO

    Monday, February 27, 2012 8:11 PM
  • This error is basically saying that SQL Server cannot confirm that a full has occurred for the database pulmo15032010 and therefore it cannot perform a differential backup.

    As above, you'll need to check the full backup task step and you could try execute the below in a new query window in SQL Server Management Studio to see if both a full and differential backup works for the pulmo15032010 database (you can change paths etc if necessary)

    BACKUP DATABASE pulmo15032010 TO DISK='H:\Backup\DifferentialBackup\pulmo15032010_full.bak'-- full backup

    GO

    BACKUP DATABASE pulmo15032010 TO DISK='H:\Backup\DifferentialBackup\pulmo15032010_diff.bak' WITH DIFFERENTIAL -- differential backup

    GO

    Full backup is ok for pulmo and ot work with maintenance plan, what kind of permission i need to have over pulmo database?

    thank's

    Monday, February 27, 2012 8:14 PM
  • Hi,

    For backups - as per http://technet.microsoft.com/en-us/library/ms186865.aspx you'll need one of the below to backup the database

    • member of the sysadmin fixed server role, or
    • db_owner fixed database roles, or
    • db_backupoperator fixed database roles

    You'll also need permissions on the folder where you save the backups.

    For maintenance plans - as per http://msdn.microsoft.com/en-us/library/ms189953.aspx 'To create or manage Maintenance Plans, you must be a member of the sysadmin fixed server role.'

    Monday, February 27, 2012 8:30 PM
  • This error is basically saying that SQL Server cannot confirm that a full has occurred for the database pulmo15032010 and therefore it cannot perform a differential backup.

    As above, you'll need to check the full backup task step and you could try execute the below in a new query window in SQL Server Management Studio to see if both a full and differential backup works for the pulmo15032010 database (you can change paths etc if necessary)

    BACKUP DATABASE pulmo15032010 TO DISK='H:\Backup\DifferentialBackup\pulmo15032010_full.bak'-- full backup

    GO

    BACKUP DATABASE pulmo15032010 TO DISK='H:\Backup\DifferentialBackup\pulmo15032010_diff.bak' WITH DIFFERENTIAL -- differential backup

    GO

    i execute

    BACKUP DATABASE pulmo15032010 TO DISK='H:\Backup\FullBackup\pulmo15032010_fullZZ.bak'

    GO

    For full backup and is successful


    after that i execute

    BACKUP DATABASE pulmo15032010 TO DISK='H:\Backup\DifferentialBackup\pulmo15032010_diffZZ.bak' WITH DIFFERENTIAL

    GO

    and it give me this error:

    Msg 3035, Level 16, State 1, Line 1
    Cannot perform a differential backup for database "pulmo15032010", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.
    Msg 3013, Level 16, State 1, Line 1
    BACKUP DATABASE is terminating abnormally.

    Tuesday, February 28, 2012 9:27 AM
  • two that I know of are:

    1) NTBackup - see this article: http://support.microsoft.com/kb/903643
    2) Volume Shadow Copy Service: http://technet.microsoft.com/en-us/library/cc785914(WS.10).aspx

    If you do not have something like NTBackup running and performing additional backups, then you most likely have the volume shadow copy service enabled which is causing the problem.

    Volume Shadow Copy Service is started can this make problem with differential backup?
    Tuesday, February 28, 2012 9:32 AM
  • i run on test server with sql vss runing and it's all good full and differential backup.

    I run ones again maintenance plan for differential backup and it's start backup some database and when it come to pulmo database, give me error that no full backup is perform, but i have full backup of pulmo database, and also i check pulmo database backup type = FULL.

    Tuesday, February 28, 2012 10:28 AM
  • Most likely the NTBackup and/or VSS service issue. Try to disable the "SQL Server VSS Writer Service" and you will liely see these errors go away.

    Tibor Karaszi, SQL Server MVP | web | blog

    Tuesday, February 28, 2012 6:16 PM
    Moderator
  • It's odd though... If you issue the Full Backup manually and you still see this it looks a little bad. What version of SQL are you using?
    Tuesday, February 28, 2012 7:26 PM
  • It's odd though... If you issue the Full Backup manually and you still see this it looks a little bad. What version of SQL are you using?

    it's odd really, but it's windows XP SP3 and sql server 2005 RTM.

    I also read that need some hotfix for this error.

    Do you know something about that?

    Thanl's

    Tuesday, February 28, 2012 7:49 PM
  • Well OK, that explains that... You really should get the latest Service Pack for that. (I think we are at SP4 already for SQL 2005...) Chances are pretty good that your problem will be gone with that.
    Tuesday, February 28, 2012 9:01 PM
  • Hi zzdravkin,

    Can you send the output of following statement. 

    USE [Master];
    GO

    DECLARE @FileSpace TABLE ( 
    [database_id] INT, 
    [db_name] NVARCHAR(100),
    [file_id] INT, 
    [file_name] NVARCHAR(100), 
    [space_used] INT 
    );
    INSERT INTO @FileSpace  EXEC sp_MSforeachdb 'USE [?] SELECT db_id(''?''),''?'' AS DBname,fileid, name ,FILEPROPERTY(name, ''SpaceUsed'')  AS spaceused FROM sys.sysfiles';


    SELECT 
    S.name AS [DatabaseName], 
    S.recovery_model_desc AS [RecoveryModel], 
    S.create_date AS [DatabaseCreatedDate],
    S.collation_name AS [DatabaseCollation],
    F.[Last_Full_Backup_Date] AS [LastFullBackupDate], 
    F.backup_size AS [FullBackupSize], 
    L.Last_Log_Backup_Date AS [LastLogBackupDate],
    L.backup_size AS [LogBackupSize],
    I.[Last_Diff_Backup_Date] AS [LastDiffBackupDate],
    I.backup_size AS [DiffBackupSize],
    S.state_desc [DatabaseState],
    CURRENT_TIMESTAMP AS [ReportDate]
    FROM 
    SYS.DATABASES S
    OUTER APPLY
    (SELECT TOP  1 database_name, backup_finish_date AS [Last_Full_Backup_Date] , backup_size  FROM msdb.dbo.backupset 
    WHERE TYPE='D' and database_name =S.name  ORDER BY backup_finish_date DESC ) F
     
    OUTER APPLY
    (SELECT TOP  1 database_name, backup_finish_date AS [Last_Log_Backup_Date] , backup_size  FROM msdb.dbo.backupset 
    WHERE TYPE='L' and database_name =S.name  ORDER BY backup_finish_date DESC ) L
    OUTER APPLY
    (SELECT TOP  1 database_name, backup_finish_date AS [Last_Diff_Backup_Date] , backup_size  FROM msdb.dbo.backupset 
    WHERE TYPE='I' and database_name =S.name  ORDER BY backup_finish_date DESC ) I  
    ORDER BY 
    S.name;




    Thanks, Harpreet http://myharpreet.blogspot.com

    Thursday, March 01, 2012 4:55 PM
  • Your error code shows you are still in SQL Server 2005 RTM, there was a bug in this which was fixed in SP2.  (http://support.microsoft.com/kb/921106)

    Please apply atleast Sp2 and check, i am sure this will be resolved. (if you can apply Sp3 that would help you in long run since there are many other bugs which were resolved in SP3)

    Thanks.


    SP Anand http://www.sqlproblems.com/

    • Proposed as answer by Matt Morrell Tuesday, April 03, 2012 7:52 AM
    Monday, March 05, 2012 10:38 PM