Skip to content

Check if Backup file still exists on disk

Some time back we started getting to many sapace issues on our database servers. On analysis we found that there are multiple copies of database backup files stored in the server at different locations (most of these backups were taken as a special request or before making any changes) and no one bothered to check back if these backup files still exists or we can remove these backup files. Since, these backup files were kept at path different from where we take backup using our maintenance plan jobs, the Maintenance plan cleanup job was not able to delete these files.

To make sure this will not happen again, we have written a script which will check if any database backup file older than 7-days exists on the server. We will get a list of backup file location, associated database name and the backup time as output.

NOTE:-

1) In this script we are making use of extended stored procedure xp_fileexist which can check if file exist or not.

exec master..xp_fileexist ‘D:\sqlserver.txt’

Output is:-

File Exists     File is a Directory       Parent Directory Exists

———–       ——————-           ———————–

0                      0                                    1

(1 row(s) affected)

If “File Exists” result is 1 that mean file exists else it does not exist.

2) We will be quering to dbo.backupset and dbo.backupmediafamily system tables in MSDB database.

Script:-

 set nocount on

create table #DoFileExist (

      FileExist smallint,

      FileIsADir smallint,

      ParentDirExist smallint);

create table #FilesExists (BackupFileName varchar(1000));

declare @BackupPath varchar(1000)

declare @CheckIfFileExist varchar(1500)

declare @TheseFileExists varchar(1500)

declare @BackupFiles cursor

set @BackupFiles = cursor for

select BMF.physical_device_name from dbo.backupset BS, dbo.backupmediafamily BMF

where BS.media_set_id=BMF.media_set_id

and BS.backup_start_date < (getdate()-7)

— above condition to check backup files older than 7 days

open @BackupFiles

fetch next from @BackupFiles into @BackupPath

while @@FETCH_STATUS = 0

begin

set @CheckIfFileExist= ‘Insert into #DoFileExist exec master..xp_fileexist “‘ + @BackupPath + ‘”‘

–print @CheckIfFileExist

exec (@CheckIfFileExist)

–select * from #DoFileExist

If exists (select FileExist from #DoFileExist where FileExist = 1)

begin

set @TheseFileExists = ‘Insert into #FilesExists (BackupFileName) values (‘ + ”” + @BackupPath + ”” +’)’

–print @TheseFileExists

Exec (@TheseFileExists)

–truncate table #DoFileExist

end

–Else Begin

–truncate table #DoFileExist

–End

truncate table #DoFileExist

fetch next from @BackupFiles into @BackupPath

end

close @BackupFiles

deallocate @BackupFiles

–select * from #FilesExists

select BS.database_name, FE.BackupFileName, BS.backup_finish_date BackupDateTime from dbo.backupset BS, dbo.backupmediafamily BMF,  #FilesExists FE

where BS.media_set_id=BMF.media_set_id and BMF.physical_device_name=FE.BackupFileName

drop table #DoFileExist

drop table #FilesExists

Output will be like:-

database_name            BackupFileName                           BackupDateTime

———————-   —————–                        ——————

AdventureWorksDW2008R2   E:\New Folder\AdventureWorksDW2008R2.bak 2011-07-22 10:38:27.000