How to modify default timestamp in .BAK filename

Question

How to modify default timestamp in .BAK filename

Ryan-209402

Hall of Fame

Points: 3707
More actions
May 29, 2007 at 10:08 am

#178849

I would like to rename some SQL backup files to not inlcude the hour and minutes in the time stamp. For example, I'd like to change the following filenames from this:
db1_db_200705290101.bak
db2_db_200705290105.bak
db3_db_200705290110.bak
to this:
db1_db_20070529.bak
db2_db_20070529.bak
db3_db_20070529.bak
I could just write a script to rename the files or to just create backups with a different filename but but I wanted to check first to see if there is a way to have SQL Server automatically leave the hour and minutes off of the filename by default. Does anyone know if this is an easy fix? I'm using a scheduled maintenance job to run the backups. Thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Anders Pedersen SSChampion Points: 11410 More actions · Answer 1

Ryan,

somewhere on here I posted the scripts I use to backup my servers. With that you can configure the file names as you please. The way it is written emulates the way SQL does it, but should be easy to change.

Anders Pedersen SSChampion Points: 11410 More actions · Answer 2

I think this discussion might be of interrest, includes my code, but also other peoples take on it:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=359034

Ryan-209402 Hall of Fame Points: 3707 More actions · Answer 3

Thanks for the suggestion. It sounds like I won't be able to modify the filename of the backup if I'm using a maintenance plan so I'll need to schedule a job using a script. I can probably make use of your script but I'll need to modify it to change the timestamp and to use the Litespeed stored procedure, etc. I'll give it a try.