June 29, 2006 at 7:06 am
I am trying to get old backup files (.BKP, .BAK, .TRN) from all drives on a server before a particular date .
Can any one help me solving this?
June 29, 2006 at 7:21 am
You can use the search functionality in windows explorer. Not sure if you can script this as a Windows command batch, though.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
June 29, 2006 at 7:26 am
In the Windows search facility, type "*.BKP; *.BAK; *.TRN" (without the inverted commas) in the field All or part of the file name, then fill in the dates under When was it modified. That should get you what you want.
John
June 29, 2006 at 7:55 am
I need get the information using T-sql programming and display all files with extension .BKP, .BAK, .TRN as a result set.
June 29, 2006 at 8:39 am
Hi,
I you really need to do this using T-SQL:
create table #temp_table
(
filename varchar(1000),
depth int,
isFile bit
)
insert into #temp_table
exec master..xp_dirtree 'c:\',0,1
--this returns the entire file/folder structure for the c: drive.
select * from #temp_table
where isFile = 1
and (filename like '%.bak'
or filename like '%.trn'
--etc
)
You could improve on the above select statement by ensuring you only get the last 4 characters instead.
Now, that will give you the file names. Unfortunately, it doesn't contain the folders to which they belong.
You're going to have to use recursion or some other method to expand the heirarchy. In other words, for each file, find it's parent folder - which has a depth that is one less than the file and isFile = 0. Then find that folder's parent folder and you get to depth = 1.
Have a look at this article on expanding a heirarchy using T-SQL.
http://support.microsoft.com/default.aspx?scid=kb;en-us;q248915
Another alternative is to use xp_cmdshell
exec master..xp_cmdshell 'dir c:\*.bak /s /b'
By the way, if you need to find all your local drives, use xp_fixeddrives.
Hope all of that helps,
June 29, 2006 at 9:38 am
Thanks Karl, I am almost there for the output.
But when I use xp_dirtree on c:\ drive it is giving so many rows which is unnecessary.
Can we exclude Program Files and Windows subdirectory?
Also, how to get only files with depth 1 ?
June 29, 2006 at 10:13 am
Unfortunately you cannot exclude subdirectories (that I know of).
To only go to a depth of 1 you can set the second parameter to 1
exec master..xp_dirtree 'c:\',1,1
Hope that helps,
June 29, 2006 at 2:01 pm
Thanks Karl.
How to get the date of the file?
June 29, 2006 at 4:29 pm
No problem.
To get the date of a file you could use xp_getfiledetails '@filename'.
But you'd need the full path for the file name.
June 30, 2006 at 8:04 am
Karl, If some how I got the filename what I want but how can I get the full path of the filename to use the above xp_getfiledetails?
I am going somewhere to solve this problem.
June 30, 2006 at 11:05 am
You can run dir /B/O-D /s C: command using xp_cmdshell and passing Drive as a parameter. This way you'll get all files on a drive (drives) with full path. Insert them into a temp table and query the daya the way you need.
Hope it'll help.
Julia
June 30, 2006 at 11:59 am
I am almost there at the solution..
Can anyone correct my following query.. I am getting so many duplicate files ?
********************************************************************
set nocount on
create table #DrivesOnServer
(
Drive varchar(10) not null,
FreeSpaceInMB dec(19,4) NULL
)
create table #FilesInDrive
(
[FileName] varchar(1000),
depth int,
IsFile bit
)
create table #FileNames
(
[FileName] varchar(1000),
PathOfFile varchar(4000)
)
create table #scratch
(
[Filename] varchar(4000),
depth int,
IsFile bit
)
Insert #DrivesOnServer (Drive, FreeSpaceInMB) EXEC xp_fixeddrives
declare @cmd varchar(4000),
@var varchar(400),
@drive varchar(10),
@file varchar(1000),
@subfile varchar(4000),
@cmd2 varchar(4000),
@cmd3 varchar(4000)
declare GetDrive cursor for
select Drive from #DrivesOnServer
open GetDrive
fetch next from GetDrive into @drive
while @@fetch_status = 0
begin
set @var = ''
set @var = @drive +':\'
set @cmd = @var
Insert into #FilesInDrive EXEC master..xp_dirtree @cmd,1,1
declare GetFile cursor for
select [FileName] from #FilesInDrive
open GetFile
fetch next from GetFile into @file
while @@fetch_status = 0
begin
If((select IsFile from #FilesInDrive where [FileName] = @file)= 1)
Insert into #FileNames
select [FileName],PathOfFile = @cmd from #FilesInDrive
where IsFile = 1
and ([filename] like '%.bak'or [filename] like '%.trn'or [filename] like '%.BKP')
else
set @cmd2 = ''
set @cmd2 = @var+@file+'\'
Insert into #scratch EXEC master..xp_dirtree @cmd2,1,1
declare GetSubFile cursor for
select [FileName] from #scratch
open GetSubFile
fetch next from GetSubFile into @subfile
while @@fetch_status = 0
begin
If((select IsFile from #scratch where [FileName] = @subfile)= 1)
Insert into #FileNames
select [FileName],PathOfFile = @cmd2 from #scratch
where IsFile = 1
and ([filename] like '%.bak'or [filename] like '%.trn'or [filename] like '%.BKP')
else
delete #scratch
set @cmd3 = ''
set @cmd3 = @cmd2+@subfile+'\'
Insert into #scratch EXEC xp_dirtree @cmd3,1,1
fetch next from GetSubFile into @subfile
end
close GetSubFIle
deallocate GetSubFile
fetch next from Getfile into @file
end
close GetFile
deallocate GetFile
fetch next from GetDrive into @drive
end
close GetDrive
deallocate GetDrive
drop table #scratch
drop table #FilesInDrive
drop table #DrivesOnServer
set nocount off
go
select * from #FileNames
drop table #FileNames
go
********************************************************************************
July 5, 2006 at 7:21 am
Can anybody help me with the above query?
I need to get the backup files from all drives in a server.
I went some where wrong and I am getting duplicates instead of one and also cannot search in folders with a depth of 2 and above.
Can anyone help where I went wrong?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply