August 14, 2017 at 9:43 am
Dear All,
I created backup by Maintenance Plan there are
1. Full Backup for once a week
2. Daily Differential Backup
3. Every 2 hours for Transaction Log Backup
All backup stored separately in the same folder as well (bak for Full and Differential other trn for Transaction Log).
My question is once the Weekly Full Backup are done I did right click the Database > Task > Restore > Database then the last week backup history disappeared from the list but the files still there as well.
Please kindly suggest me and thank you in advance.
August 14, 2017 at 11:14 am
Keetawat C. - Monday, August 14, 2017 9:43 AMDear All,
I created backup by Maintenance Plan there are
1. Full Backup for once a week
2. Daily Differential Backup
3. Every 2 hours for Transaction Log Backup
All backup stored separately in the same folder as well (bak for Full and Differential other trn for Transaction Log).
My question is once the Weekly Full Backup are done I did right click the Database > Task > Restore > Database then the last week backup history disappeared from the list but the files still there as well.
Please kindly suggest me and thank you in advance.
You may have a maintenance plan that has a History Cleanup task that is deleting backup history. Or you have a job or manual process where .sp_delete_backuphistory is executed. When the restore screen comes up, it's pulling the data from the backup history tables. So you may have the files on whatever drive but the backup is not in the history tables. You can check the oldest backup for each database from the backup history table with something like:
SELECT database_name, min(backup_finish_date)
FROM msdb.dbo.backupset
GROUP BY database_name
Sue
August 15, 2017 at 10:43 am
Sue_H - Monday, August 14, 2017 11:14 AMKeetawat C. - Monday, August 14, 2017 9:43 AMDear All,
I created backup by Maintenance Plan there are
1. Full Backup for once a week
2. Daily Differential Backup
3. Every 2 hours for Transaction Log Backup
All backup stored separately in the same folder as well (bak for Full and Differential other trn for Transaction Log).
My question is once the Weekly Full Backup are done I did right click the Database > Task > Restore > Database then the last week backup history disappeared from the list but the files still there as well.
Please kindly suggest me and thank you in advance.You may have a maintenance plan that has a History Cleanup task that is deleting backup history. Or you have a job or manual process where .sp_delete_backuphistory is executed. When the restore screen comes up, it's pulling the data from the backup history tables. So you may have the files on whatever drive but the backup is not in the history tables. You can check the oldest backup for each database from the backup history table with something like:
SELECT database_name, min(backup_finish_date)
FROM msdb.dbo.backupset
GROUP BY database_nameSue
Dear Sue,
Thank you for your replied. I was looking for the Cleanup History on maintenance plans but could not be found. How can I find it on other way.
Thank you in advance.
Best Regards,
Keetawat C,
August 15, 2017 at 2:00 pm
Keetawat C. - Tuesday, August 15, 2017 10:43 AMDear Sue,
Thank you for your replied. I was looking for the Cleanup History on maintenance plans but could not be found. How can I find it on other way.
Thank you in advance.
Best Regards,
Keetawat C,
Don't know for sure as I don't use Maintenance Plans myself and I don't have a SQL 2005 server to test out what you are looking for - sorry.
Look at sysmaintplan_logdetail. I could be wrong but I think that may have the tasks and/or commands for the maintenance plans.
Sue
August 16, 2017 at 8:55 pm
Sue_H - Tuesday, August 15, 2017 2:00 PMKeetawat C. - Tuesday, August 15, 2017 10:43 AMDear Sue,
Thank you for your replied. I was looking for the Cleanup History on maintenance plans but could not be found. How can I find it on other way.
Thank you in advance.
Best Regards,
Keetawat C,Don't know for sure as I don't use Maintenance Plans myself and I don't have a SQL 2005 server to test out what you are looking for - sorry.
Look at sysmaintplan_logdetail. I could be wrong but I think that may have the tasks and/or commands for the maintenance plans.Sue
Dear Sue,
I did not found any of History Cleanup in any of Maintenance Plan. Do we have any of setup to prevent or expand the period of SQL Backup History.
Thank you very much.
Best & Regards
Keetawat C.
August 17, 2017 at 8:43 am
Keetawat C. - Wednesday, August 16, 2017 8:55 PMSue_H - Tuesday, August 15, 2017 2:00 PMKeetawat C. - Tuesday, August 15, 2017 10:43 AMDear Sue,
Thank you for your replied. I was looking for the Cleanup History on maintenance plans but could not be found. How can I find it on other way.
Thank you in advance.
Best Regards,
Keetawat C,Don't know for sure as I don't use Maintenance Plans myself and I don't have a SQL 2005 server to test out what you are looking for - sorry.
Look at sysmaintplan_logdetail. I could be wrong but I think that may have the tasks and/or commands for the maintenance plans.Sue
Dear Sue,
I did not found any of History Cleanup in any of Maintenance Plan. Do we have any of setup to prevent or expand the period of SQL Backup History.
Thank you very much.Best & Regards
Keetawat C.
Probably there was a clean history task added by mistake and removed by somebody . Check this again after the new backups happen . Not sure if there is any such thing . The Microsoft link below talks about lot of stuff on this and more.
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-history-and-header-information-sql-server
August 17, 2017 at 9:46 am
Arsh - Thursday, August 17, 2017 8:43 AMProbably there was a clean history task added by mistake and removed by somebody . Check this again after the new backups happen . Not sure if there is any such thing . The Microsoft link below talks about lot of stuff on this and more.
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-history-and-header-information-sql-server
The poster was trying to find what maintenance plan has that specific task - they did not find it in when looking through Maintenance Plans. The information for that is not in the backup tables. And now his question is how to prevent the history from being deleted.
A script to check the oldest date of the backups was already provided.
Sue
August 17, 2017 at 10:25 am
Not sure if I'm helping or not, but I know where I work, we have a separate job for cleaning up the history. My preference is to have a maintenance plan do as little as possible so if/when it fails, I can figure out what failed and fix it.
For example, if you have backup and maintenance tasks all in 1 MP, lets say the backup succeeds, index rebuild succeeds, but rebuild statistics fails. You get the alert and now you need to decide do you re-run the whole MP OR would you rather have multiple MP's but a single job that calls them in order? I know I'd rather be able to re-start things at the rebuild stats step.
PLUS in cases like yours it is nice. If a cleanup task is hiding inside a MP it can be painful jumping through everything.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 17, 2017 at 10:40 am
You can do something like a trigger on the tables but I would think it's better to find out where it's coming from.
I would look at other servers as well - I think this kind of issue with maintenance plans happens a lot with those type of issues where things happen such as restoring msdb to a lower environment or if a production server was upgraded, the old original server was moved to a lower environment, etc. And it could simply be from a Maintenance plan on another server as you can select to run against different servers in maintenance plans.
If it's a regular job that is executing the stored procedure to delete backup history, you would want to search the commands column in sysjobsteps. Just something like:
SELECT *
FROM msdb.dbo.sysjobsteps
WHERE command like '%sp_delete_backuphistory %'
If someone is using their own script to deleted the history from all of the tables, you could just search the sysjobsteps the same way but look for backupset in the command as that would be one of the tables to delete from.
SELECT *
FROM msdb.dbo.sysjobsteps
WHERE command like '%backupset%'
Sue
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply