Posted by loudfish on 06/14/07 15:50

Hi there,

I'm looking to get SSIS to run database backup and restore tasks as
part of an overall process. But I have a few problems.

1. The "Backup Database Task" does not seem to allow parameterisation
- ie let you dynamically set which database is to get backed up, and
where the backup is to be put. Ultimately I'd like to put the
"database" and "backup location" into the nice xml config file I've
already got up and running, but the package configuration doesn't seem
to allow this.

2. There is no "Restore Database Task". So I've currently got "Execute
SQL Task" which excutes a sproc that does it. I guess I could
parameterise the sproc to accept @Database @Location as parameters.
Also, I suspect, to expand the sproc out to include the Backup Tasks
with an additional @BackuporRestoreFlag.

3. If I go for the sproc route, I suspect that I can't use the sproc
to backup the database which is holding the sproc? Does that mean I
need to dynamically build the sproc in SSIS as a string, then just
execute the string?

Looking for some hints on best practice here - what's a good/bad idea
from the above?

Thanks in advance

Andrew

[Back to original message]