Getting old backup files from all drives

  • 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?

  • 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

  • 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

  • I need get the information  using T-sql programming and display all files with extension .BKP, .BAK, .TRN as a result set.

  • 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,

  • 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 ?

  • 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,

  • Thanks Karl.

    How to get the date of the file?

  • 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.

  • 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.

  • 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

  • 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

    ********************************************************************************

  • 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