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

chandrurec
chandrurec Member Posts: 560
edited 2010-10-19 in NAV/Navision Classic Client
Hi all,

I have configured the Job scheduler in SQL Server 2008 to take automatic backup in D:\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.

Comments

  • rhpnt
    rhpnt Member Posts: 688
    Scheduled SQL Server backup jobs, if not defined manualy, always write the date and time of the backup (somename_backup_yyyymmddhhmm).
  • Shedman
    Shedman Member Posts: 194
    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
    kriki Member, Moderator Posts: 9,121
    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
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • chandrurec
    chandrurec Member Posts: 560
    Hi Kriki,

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

    Thank you so much.

    Regards,
    Chandru.

Categories