List backup files with spaces in the file name

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

SQL Server 2005 Forums

Transact-SQL (2005)

List backup files with spaces in the file name

Author

Topic

dhjackal
Starting Member

42 Posts

Posted - 2009-03-12 : 11:26:05

I'm trying to return a list of database backup files for backups which contain spaces in the file name


DECLARE @CmdString VARCHAR(200)
, @SysDate INT

SET @SysDate = 101

DROP TABLE #tmpFiles

CREATE TABLE #tmpFiles
(FileName varchar(150) )

INSERT INTO #tmpFiles
EXEC master.dbo.xp_cmdshell 'dir "D:\Backups\*.bak"'

DELETE
FROM #tmpFiles
WHERE Filename NOT LIKE '%bak'
OR Filename IS NULL

SELECT REVERSE( SUBSTRING( REVERSE(Filename), 0, CHARINDEX(' ', REVERSE(Filename) ) ) ), 
SUBSTRING(Filename, 1, 22)
FROM #tmpFiles
WHERE DATEDIFF( dd, CONVERT(DATETIME, LEFT( Filename, 20),@SysDate), GETDATE() ) > 3

The above will return all the files but not if they have a space in the file name

ie.

I have 2x backup files in D:\Backups called ;

test_backup.bak
test backup.bak

All that gets returned by the SELECT statement is ;

test_backup.bak
backup.bak

Anybody got any ideas how i change the select to return the entire file name and not work off the leading space?

Any help appreciated

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-13 : 06:19:28

Instead of using

SELECT REVERSE( SUBSTRING( REVERSE(Filename), 0, CHARINDEX(' ', REVERSE(Filename) ) ) ),
SUBSTRING(Filename, 1, 22)
FROM #tmpFiles
WHERE DATEDIFF( dd, CONVERT(DATETIME, LEFT( Filename, 20),@SysDate), GETDATE() ) > 3

use this

SELECT Filename,right(Filename,patindex('%[0-9]%',REVERSE(Filename))-1)
FROM #tmpFiles
WHERE DATEDIFF( dd, CONVERT(DATETIME, LEFT( Filename, 20),@SysDate), GETDATE() ) > 3

Madhivanan

Failing to plan is Planning to fail

dhjackal
Starting Member

42 Posts

Posted - 2009-03-20 : 09:43:01

Madhivanan,

Thanks for the reply. Apologies I haven't commented earlier but I was on holiday. I'm going to try this solution now.

Thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources