January 12, 2012 at 11:40 am
Hi,
Every month end I have to create a database and load it with (all) data from another. I name the databases by the month they were created - for example, January2012. This process is fully automated, I have a package that is using a Database Transfer Task with a configuration file which I change every month (I was never able to set the DestinationDatabaseName and DestinationDatabaseFiles at runtime). Now I'd like to automatically back up the database I just created - I need to change the connection manager dynamically. What is the best way to accomplish this? To me, it doesn't look like I can use the "backup database" maintenance task. Should I use a TSQL task?
Thanks!
January 12, 2012 at 12:22 pm
I'd use a T-SQL task for that, and just issue a backup command. The backup command can accept a variable for the database name, but you might need to build the file name before the actual backup command.
Something like this:
declare @DB sysname, @File varchar(1000);
select @DB = 'BaseDBName_' + cast(datepart(year, getdate()) as varchar(1000)) + cast(datepart(month, getdate()) as varchar(1000));
select @File = 'MyBackupPath\' + @DB + '.bak';
backup database @DB to file @File...
And so on.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 12, 2012 at 12:29 pm
Thanks!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply