locked
Possible to clear diff backup data from database? RRS feed

  • Question

  • I'm using Check_MK (/OMD/nagios) to monitor my SQL and last backup date.

    2153 days ago a diff backup was made of 2 of my databases and my monitoring system sees this as an error. I always do full backup so I want to wipe diff backup data from the database.

    Is it possible to completely wipe this backup date/data and make the database se to "never did a diff backup"?


    Regards,
    Allan Kjær
    blog

    Monday, July 31, 2017 6:53 AM

Answers

  • Yes, this is where your app is getting this information from. You need to clear that row. They delete history stored procedure should take care of it.

    Have you run it yet? You might want to try to run this:

    exec msdb.dbo.sp_delete_backuphistory @oldest_date ='2012-01-01'

    • Marked as answer by Allan Kjaer Monday, July 31, 2017 1:19 PM
    Monday, July 31, 2017 1:13 PM

All replies

  • my monitoring system sees this as an error

    Hello Allan,

    Then the used SQL script to check the backup state is wrong; you could e.g. filter on backup type to exclude diff backups.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, July 31, 2017 7:07 AM
  • Hi Olaf,

    Thanks for your reply.

    I don't really get the point? The state reported back is right (full recovery model, backed up today at 08:00). Log is also backed up. But 2153 days ago a diff. backup was run.

    I want to clear this date/data, so that it looks like my database never did a diff. backup.


    Regards,
    Allan Kjær
    blog

    Monday, July 31, 2017 7:11 AM
  • You should be pruning your backup job history.

    Use

    exec msdb.dbo.sp_delete_backuphistory @Date

    For this. Put a date in for @Date. I usually use 4 weeks.

    • Marked as answer by Allan Kjaer Monday, July 31, 2017 10:14 AM
    • Unmarked as answer by Allan Kjaer Monday, July 31, 2017 11:38 AM
    Monday, July 31, 2017 9:58 AM
  • Hi Hilary,

    Very nice. Thank you. I'm not very good at SQL queries at all, but executing this command will not delete any other data from my databases, right? There's no risk in running this command? :-|


    Regards,
    Allan Kjær
    blog

    Monday, July 31, 2017 10:06 AM
  • It will delete the records of your backups older than 4 weeks if you put '2017-07-03' in there. That is in US date format. I am not sure what country you are in so I would try

    declare @date date

    set @date=getdate()-7*4

    exec msdb.dbo.sp_delete_backuphistory @Date

    If for whatever reason you need to keep backup history (and most people don't), you should not need to run this command. It will solve your problem though.

    Monday, July 31, 2017 10:11 AM
  • Thanks. I used the date format in this MS article:

    sp_delete_backuphistory


    Regards,
    Allan Kjær
    blog

    Monday, July 31, 2017 10:14 AM
  • Hi Hilary,

    I thought the problem was fixed, but my nagios still reports that diff date was XXX days ago :-|


    Regards,
    Allan Kjær
    blog

    Monday, July 31, 2017 11:39 AM
  • It must be getting this information from somewhere else.

    See if the backup still shows up here:

    select backup_finish_date from  backupset  where 
    database_name='Name'
    and type='i'
    
    

    Replace Name with the name of the backup.

    Monday, July 31, 2017 11:45 AM
  • Hi Hilary;

    I tried running the query. The message is:

    Msg 208, Level 16, State 1, Line 1
    Invalid object name 'backupset'.

    Regards,
    Allan Kjær
    blog


    Monday, July 31, 2017 11:51 AM
  • run this in the msdb database.
    Monday, July 31, 2017 12:00 PM
  • Ahh, very nice. Now it works:


    Regards,
    Allan Kjær
    blog

    Monday, July 31, 2017 12:01 PM
  • Yes, this is where your app is getting this information from. You need to clear that row. They delete history stored procedure should take care of it.

    Have you run it yet? You might want to try to run this:

    exec msdb.dbo.sp_delete_backuphistory @oldest_date ='2012-01-01'

    • Marked as answer by Allan Kjaer Monday, July 31, 2017 1:19 PM
    Monday, July 31, 2017 1:13 PM
  • You are awesome! Thanks - this last command did the trick! :-)

    Regards,
    Allan Kjær
    blog

    Monday, July 31, 2017 1:20 PM