December 12, 2007 at 8:02 am
I have a large number of databases that need to be backed up nightly. Our archiving solution that also runs nighly needs the files to be named the same every night.
The maintenance wizard works perfectly except it forces the name of the resulting BAK file to include the date/time. There is no option available to exclude it or force it to be overwritten.
Is there a template that this wizard uses to generate the T-SQL code each night or do I need to create my own T-SQL to do this? We generate new databses all the time so the code would have to use a case statement to traverse through the table names.
I know ... I'm being lazy.:D
December 12, 2007 at 8:25 am
You have limited options because the maintenace plan is actually a SSIS package. The wizard creates a job to call the package.
Your best bet would be to create your own TSQL. The wizard generates the TSQL based on your choices while creating the plan. You can have SSMS generate the backup script for you by performing a backup and generate to query window. Then all you have to do is:
exec master.dbo.sp_msforeachdb
"USE [?]
BEGIN
-- code goes here
END"
December 12, 2007 at 8:32 am
Thanks ...
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply