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
