Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Extract specified length of backupfiles ina folder

Author  Topic 

Ravikumarc
Yak Posting Veteran

51 Posts

Posted - 2011-02-09 : 12:57:24
Dear All,

There are two backup files in a folder"E:\Test_Backup\"

TestDB_Backup_2011_02_08_230002_7317713.bak
TestDB_Backup_2011_02_05_230002_7318761.bak

Please let me know how to compare & get the latest file & also let me know how to extract the specified length of file name,I mean i want the result as "TestDB_Backup_2011_02_08" neglecting _230002_7317713

Please fine the query below i have wrote,were its not satisfied my condition..

DECLARE @Date VARCHAR(10)
DECLARE @DD CHAR(2)
DECLARE @MM CHAR(2)
DECLARE @YY CHAR(4)
DECLARE @FileName VARCHAR(100)
DECLARE @DBName VARCHAR(50)
DECLARE @BkpPath VARCHAR(100)


SELECT @DBName = 'TestDB'

SELECT @Date = Convert(VARCHAR(10),Getdate(),121)
SELECT @YY = LEFT(@Date,4)
SELECT @MM = SUBSTRING(@Date,6,2)
SELECT @DD = RIGHT(@Date,2)

SELECT @FileName = @DBName + '_' + 'Backup' + '_' + @YY+ '_' + @MM + '_'+ @DD + '.BAK'

SELECT @BkpPath = 'E:\Test_Backup\'

SELECT @FileName = @BkpPath + @FileName

SELECT @FileName

Thanks & Regards
Ravi

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-10 : 03:54:09


declare @t table(file_path varchar(100))
insert into @t
select 'E:\Test_Backup\TestDB_Backup_2011_02_08_230002_7317713.bak' union all
select 'E:\Test_Backup\TestDB_Backup_2011_02_05_230002_7318761.bak'
select top 1 file_path from @t
order by cast(replace(substring(right(file_path ,charindex('\',reverse(file_path))-1),15,10),'_','') as datetime) desc


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -