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
 How to format the name ofSQL Server 2008 backupfil

Author  Topic 

Ravikumarc
Yak Posting Veteran

51 Posts

Posted - 2011-02-09 : 14:34:41
Dear All,

I define a Maintenance task for backup form the SQL Server 2008 Management Studio.The task creates backup files with name db_backup_YYYY_MM_DD_HHMMSS_ID.bak.

Please let me know can we change the Backup format? If so how we can change..

TestDB_Backup_2011_02_08_230002_7317713.bak hence i want the backup in the format as TestDB_Backup_2011_02_08.bak.. Please advice is it possible

Thanks & Regards
Ravi

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-09 : 14:38:20
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.
Go to Top of Page

Ravikumarc
Yak Posting Veteran

51 Posts

Posted - 2011-02-09 : 14:50:05
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.




Hi,

I Have a problem while creating a restore job ..pls find the info below

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 lattest 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 find 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
Go to Top of Page

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-09 : 16:04:50
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.
Go to Top of Page

Ravikumarc
Yak Posting Veteran

51 Posts

Posted - 2011-02-09 : 16:25:39
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.




Hi,

No My Database is in Full recovery model, here i have to write a restore job by taking the latest backup file and eliminating the timestamps from the backup file.i have multiple files with the different dates and time stamps.

also i have come up with a query as mentioned below..

create table #dir(FileName1 nvarchar(300))
insert #dir exec xp_cmdshell 'dir E:\Test_Backup\*.bak /b'
select SUBSTRING(FileName1, 1, 24)+ '.BAK' from #dir
drop table #dir

Please advice..

Thanks
Ravi
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-02-09 : 17:35:41
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
Go to Top of Page

Ravikumarc
Yak Posting Veteran

51 Posts

Posted - 2011-02-09 : 18:41:32
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



Hi Michael,

Thanks for yr replay..But here i dont want the full backup file name i need only the portion of the file name.for example
E:\Test_Backup\TestDB_backup_2011_02_08_230002_7317713.bak

Hence i want in this format
E:\Test_Backup\TestDB_backup_2011_02_08.bak..Can you please advice in this regard

Thanks
Ravi


Go to Top of Page

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-09 : 19:12:43
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.
Go to Top of Page

Ravikumarc
Yak Posting Veteran

51 Posts

Posted - 2011-02-09 : 19:33:25
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



Hi Michael,

Thanks a lot,Your query works fine for me..I Have created a job and tested it, works fine..
Thanks again Michael

With Regards
Ravi


Go to Top of Page

Ravikumarc
Yak Posting Veteran

51 Posts

Posted - 2011-02-09 : 19:36:21
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.




Hi Guru,

Yep agreed and got yr point..Thanks for yr immense help

With Regards
Ravi
Go to Top of Page
   

- Advertisement -