January 5, 2004 at 6:14 am
I have several DTS Packages which run to backup databases, and would like to consolidate them all in one job - passing a parameter would be a great way of doing this - does anyone know how to actually do this? All I've ever used in DTS is the packages that can be created automatically using the import/export wizards...
Kind Regards
...oooOO*OOooo...
January 5, 2004 at 7:08 am
Do you mean a generic package or a SQL Job? If you just need to backup the databases, you can setup a SQL job with a step for each backup that is performed.
USE master
EXEC sp_addumpdevice 'disk', 'myDiskBackup', 'D:\backups\myDatabase_20031223.bak'
-- Back up the full myDiskBackup database.
BACKUP DATABASE myDatabase TO myDiskBackup
GO
EXEC sp_DropDevice 'myDiskBackup'
GO
For more information lookup BACKUP DATABASE in BOL.
January 5, 2004 at 7:27 am
Sorry, should of been clearer. The backup I'm talking about is actually transferring data from the production server to my home server, after that is run I run the BACKUP DATABASE command like you say.
I'd like the data transfer to be one package, but with a parameter specifying which database to apply it too.
...oooOO*OOooo...
January 5, 2004 at 7:41 am
Well, as the Question states, you need to setup a global variable that holds the name of the database you'd like to have transfered and backed up.
Then, use the Dynamic Properties task to assign the database name to the connection for transfering the data.
After that, run your "Copy SQL Server Objects" task to get the data to your local.
Setup an EXECUTE SQL task and change the SQL shown before to read:
USE master
EXEC sp_addumpdevice 'disk', 'myDiskBackup', 'D:\backups\myDatabase_20031223.bak'
-- Back up the full myDiskBackup database.
BACKUP DATABASE ? TO myDiskBackup
GO
EXEC sp_DropDevice 'myDiskBackup'
GO
I think that will work. If not, create a stored procedure that will do the needed backup and accepts the database name as a sysname parameter. You'd have to remove the GO statements, but it should work.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply