0

I would like to say that i had configured "Automatic DB Backup" as well as "Automatic DB Cleanup Task" through "Maintenance Plan" in MS SQL Server 2012 on my Production Database. And that is working properly. I had set up my "Maintenance Clean up" task "Older than 4 Weeks".

Using the following T-SQL Statement:-

use msdb

select * from

dbo.backupmediafamily;

I am able to view the backup history of my DB. That is working properly through maintenance plan schedule.

I setup Automatic Backup of 3 Databases through the "Maintenance Plan", But I had configured Maintenance Cleanup Task to delete files older than 4 weeks. During the last month there are 90 (Backup) files . I configured this plan on 26-03-2015, so now my Maintenance Cleanup Task is working now and I also delete (backup files, .bak) from the specified location. enter image description here

Now I am able to check out deleted backup file from specified location with Maintenance Plan. But after few days or few months I may be not able to collect exact deleted backup file records as well as not recognize exact days delete backup file records.

So, I want to know exact deleted (.bak) records from through T-SQL.

1

If you want to be able to view the exact backup history for longer time than those four weeks that you keep the backup files on disk, make sure that you set the cleanup task not to delete the backup history from the msdb database, you can then run the backup report from SSMS (right click database, select reports, standard reports, backup and restore events) or run the following to view the backup history:

First create this function - I'll just have it in msdb - which is not a good idea.

create FUNCTION dbo.fc_FileExists(@path varchar(8000))
RETURNS BIT
AS
BEGIN
     DECLARE @result INT
     EXEC master.dbo.xp_fileexist @path, @result OUTPUT
     RETURN cast(@result as bit)
END;
GO

Then you can run the following

SELECT     
 sd.name AS database_name, 
 ISNULL(bs.backup_finish_date, 0) AS backup_date,
 ISNULL(CASE bs.[type] 
  WHEN 'D' THEN 'Full' 
  WHEN 'I' THEN 'Differential'
  WHEN 'L' THEN 'Transaction Log' 
  END, 'NEVER') as type ,
 bm.physical_device_name, 
 dbo.fc_FileExists(bm.physical_device_name) as DoesFileExists,
 bs.backup_size / 1024 / 1024 AS backup_size, 
 DATEDIFF(mi, bs.backup_start_date, bs.backup_finish_date) AS duration
 FROM master.dbo.sysdatabases AS sd 
 LEFT OUTER JOIN msdb..backupset AS bs ON sd.name = bs.database_name 
 LEFT OUTER JOIN msdb..backupmediafamily AS bm ON bm.media_set_id =     bs.media_set_id
  GROUP BY 
   sd.name, 
   bm.physical_device_name, 
   bs.backup_size / 1024 / 1024, 
   DATEDIFF(mi, bs.backup_start_date, bs.backup_finish_date),
   bs.backup_finish_date,
   bs.[type],
   dbo.fc_FileExists(filename)
 order by bs.backup_finish_date desc
  • ,My Backup (.bak) file is already on server disk. I only want to know only deleted backup file from log history. – Md Haidar Ali Khan Apr 29 '15 at 5:21
  • I am able to view my backup log history . Also try to check out my backup history through your script it shows 'Invalid object name 'backupset' . – Md Haidar Ali Khan Apr 29 '15 at 5:25
  • You have to run the query in the MSDB database. You can create a query that will check the files on disk or a powershell script that loops through the backup history and checks if the files are on the disk. – Spörri Apr 29 '15 at 8:29
  • once again go through my question. pls – Md Haidar Ali Khan Apr 29 '15 at 8:48
  • Just updated the answer.. – Spörri Apr 29 '15 at 9:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.