How do I configure SQL Server to name database backup files by date or other pattern?

Question

When SQL Server 2005/2008 database backup jobs are run from Maintenance Plans, can they be configured to include some sequence number (or other indicator) in the filename, so that previous files with the sane name aren't overwritten? (Note I'd like to have just a single database backup in a given backup file.) For example, if my backup job runs and creates:

MyDatabase.bak

and then runs again while MyDatabase.bak stil exists, I'd like the new file to be called MyDatabase_1.bak, and so on. If I later delete MyDatabase.bak and the backup job runs, the new backup file should again be called MyDatabase.bak.

Solution

By default a database backup created from a maintenence plan will create a file based on the database name and timestamp, so a backup of a database named 10x would create a file named 10x_backup_201001062019.bak. I know this doesn't exactly answer your question but it should give you some insight into how backups are named when they're created from a maintenance plan.

Comments

[ron]

@joeqwerty: << I know this doesn't exactly answer your question... >> Actually, this exactly answers my question. I'm not sure where I was looking when I asked this question but I created a fresh new maintenance plan and I see the timestamp exactly as you said. Thanks!

[ron]

@SuperCoolMoss - this looks like a great resource, I'm going to check it out. Thank you.

[joeqwerty]

Glad to help...