April 25, 2012 at 5:36 pm
Hi all,
I need to create a backup that runs once a month and it should have a custom filename, such as backup01_31_2012.bak and backup02_28_2012.bak and so forth.
I wanted to know how I can create file names dynamically? Thanks in advance.
April 25, 2012 at 5:58 pm
To create backup filenames dynamically you'll want to generate the entire backup statement in a variable and execute it as dynamic sql. Something along the lines of this:
DECLARE @stmt NVARCHAR(255);
SET @stmt = 'BACKUP DATABASE DBName TO DISK ...'
sp_executesql @stmt;
[/code]
Just add an expression to generate the filename you want.
April 26, 2012 at 4:20 am
You will have to use T-SQL and set it up to execute in a dynamic manner as noted above. Use GETDATE() to get the date and time and format it as you need it. You'll have to build automation around generating this for each individual database.
Another option would be take advantage of Ola Hollengren's scripts. I think they allow for customization of the name.
Yet another option would be to look into a 3rd party tool like my company's SQL Backup Pro. That also will allow you to customize the names but avoid all the automation you'll have to do.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 26, 2012 at 10:36 am
Beautiful, thank you!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply