Is it possible to save under userdefined name in sql backup?

chandrurec

Hi all,

I have configured the Job scheduler in SQL Server 2008 to take automatic backup in \Backup folder. But It is storing in some default name. Is it possible to append the date on which the backup was taken along with the time at which the backup is taken.

If anyone knows how to append the date and time information to backup filename in SQL Server 2008, Kindly tell me how to do that?

Thanks in advance.

Regards,
Chandru.

rhpnt

Scheduled SQL Server backup jobs, if not defined manualy, always write the date and time of the backup (somename_backup_yyyymmddhhmm).

Shedman

That depends if you use 'Backup database across one or more files' or 'Create backup file for every database'. Using the file for every database option, SQL Server will create backup files like '[database_name]_backup_2010_10_18_162501_5892022.bak'.

kriki

If you use a maintenance plan, it is easy to do it by playing with the parameters.

If you use a SQL Server agent job, you need to do it your self. This is the TSQL code I use to create a name with current date+time in it:

DECLARE @file AS VARCHAR(200);

SET @file='E:\Backup\DB_backup_' + REPLACE(CONVERT(DATE,GETDATE(),20),'-','') + '_' + REPLACE(REPLACE(CONVERT(TIME,GETDATE(),20),':',''),'.','') + '.BAK';


select @file

chandrurec

Hi Kriki,

I got the required output by following the T-Script You have given.

Thank you so much.

Regards,
Chandru.