Extract specified length of backupfiles ina folder

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources