quote:

---

Originally posted by dataguru1971

The rest of it is a time stamp. What is the harm of that?

Why do you want to strip off a relevant piece of information?

---

Poor planning on your part does not constitute an emergency on my part.

---

quote:

---

Originally posted by dataguru1971

Umm...what you have you have multiple files with the same date, but different time stamps?

Is your database in SIMPLE recovery model?

Your better bet would be to extract the file name , in full, out of the system tables. You can limit the resulting file name based on the date that way..

Declare @Date varchar(12)
SELECT @Date = '%' + REPLACE(Convert(VARCHAR(10),Getdate(),121),'-','_') +'%'

Select physical_device_name
from msdb.dbo.backupmediafamily
where PATINDEX(@Date,physical_device_name ) >0

---

Poor planning on your part does not constitute an emergency on my part.

---

declare @backup_filepath nvarchar(1000)
declare @dir table (Filepath nvarchar(1000))

insert @dir exec xp_cmdshell 'dir E:\Test_Backup\*.bak /b/s'

select @backup_filepath = max(Filepath) from @dir where Filepath like '%.bak'

print 'Backup file name = ' +isnull(@backup_filepath,'File Not Found')

quote:

---

Originally posted by Michael Valentine Jones

This would work better to get the full path and file for the restore that you could just plug into restore command. Notice the addition of the /s parameter to the dir command to force it to include the full file path when used with /b.

declare @backup_filepath nvarchar(1000)
declare @dir table (Filepath nvarchar(1000))

insert @dir exec xp_cmdshell 'dir E:\Test_Backup\*.bak /b/s'

select @backup_filepath = max(Filepath) from @dir where Filepath like '%.bak'

print 'Backup file name = ' +isnull(@backup_filepath,'File Not Found')

CODO ERGO SUM

---

quote:

---

here i have to write a restore job by taking the latest backup file and eliminating the timestamps from the backup file.

---

DECLARE @DATE char(8),@Filename varchar(100), @BkpPath varchar(100) ,@DBName varchar(55)
SELECT    @DBName = 'TestDB'
		 ,@Date = Convert(VARCHAR(8),Getdate(),112)
SELECT @FileName = @DBName + '_' + 'Backup' + '_' + LEFT(@Date,4)+ '_' + SUBSTRING(@Date,5,2) + '_'+ RIGHT(@Date,2) + '.BAK'
SELECT @BkpPath = 'E:\Test_Backup\'
SELECT @FileName = @BkpPath + @FileName 

DECLARE @SQL varchar(max)

SELECT @SQL = 
'BACKUP DATABASE ' + @DBName + ' TO  
	DISK ='+ @FileName + ' 
	WITH NOFORMAT, INIT,SKIP, NOREWIND, NOUNLOAD,  STATS = 10'

--EXEC (@SQL)
PRINT @SQL

/*Results 
BACKUP DATABASE TestDB TO  
	DISK =E:\Test_Backup\TestDB_Backup_2011_02_09.BAK 
	WITH NOFORMAT, INIT,SKIP, NOREWIND, NOUNLOAD,  STATS = 10
*/

quote:

---

Originally posted by Michael Valentine Jones

This would work better to get the full path and file for the restore that you could just plug into restore command. Notice the addition of the /s parameter to the dir command to force it to include the full file path when used with /b.

declare @backup_filepath nvarchar(1000)
declare @dir table (Filepath nvarchar(1000))

insert @dir exec xp_cmdshell 'dir E:\Test_Backup\*.bak /b/s'

select @backup_filepath = max(Filepath) from @dir where Filepath like '%.bak'

print 'Backup file name = ' +isnull(@backup_filepath,'File Not Found')

CODO ERGO SUM

---

quote:

---

Originally posted by dataguru1971

Two people have told you that you are better off with the full backup name. Why is michael's solution unacceptable? is it too easy or simple?

Since the file name is already in an easily accessible format, why is the time stamp an issue...you will still get the most recent backup, and you will still be able to code your T-SQL Restore commands using it.

quote:

---

here i have to write a restore job by taking the latest backup file and eliminating the timestamps from the backup file.

---

You haven't explained why you HAVE to do this...you have been shown you don't have to at all, which is far easier.

What if you have two backups with the same YY MM DD ? why risk it?

if you must, you can use T-SQL to backup your database, and you can format the backup file name as you wish

DECLARE @DATE char(8),@Filename varchar(100), @BkpPath varchar(100) ,@DBName varchar(55)
SELECT    @DBName = 'TestDB'
		 ,@Date = Convert(VARCHAR(8),Getdate(),112)
SELECT @FileName = @DBName + '_' + 'Backup' + '_' + LEFT(@Date,4)+ '_' + SUBSTRING(@Date,5,2) + '_'+ RIGHT(@Date,2) + '.BAK'
SELECT @BkpPath = 'E:\Test_Backup\'
SELECT @FileName = @BkpPath + @FileName 

DECLARE @SQL varchar(max)

SELECT @SQL = 
'BACKUP DATABASE ' + @DBName + ' TO  
	DISK ='+ @FileName + ' 
	WITH NOFORMAT, INIT,SKIP, NOREWIND, NOUNLOAD,  STATS = 10'

--EXEC (@SQL)
PRINT @SQL

/*Results 
BACKUP DATABASE TestDB TO  
	DISK =E:\Test_Backup\TestDB_Backup_2011_02_09.BAK 
	WITH NOFORMAT, INIT,SKIP, NOREWIND, NOUNLOAD,  STATS = 10
*/

Untested, but also not sure it is the right way to go.

---

Poor planning on your part does not constitute an emergency on my part.

---