copy sql objects to user supplied database instance

[jcaulder]

I need to copy all objects(procedures, functions, tables) from a master database to a newly created database. The database name is unknown until the user selects it from within a vb application so it doesn't appear that a standard DTS package will work.

Is DTS capable of accepting a variable database name and copying objects to it? Or is there some better way to accomplish this?

The scenario is that there is a master facility database that contains all objects needed to create a new facility. When a new facility is to be created, the user gives a name for the new database instance and the application should create the database and then populate it with all objects from the master facility database.

I am using Sql Server 2000.

Thanks,

J

 

[TomKane]

Hi J,

You could try doing using a stored procedure that did a backup of your master database and then a restore to the new database name. AS the file name can change it would have to be a piece of dynamic SQL.

If you wanted to use DTS, well, you could run the stored procedure from a DTS using the Execute SQL Task.

It is possible to pass parameters into a DTS there are a number of ways - if you're using DTSRun then /A is the command line parameter to help you there.

If you wanted to use a lot of tasks in DTS, well, I had a look at this and I think you could do it with a "Dynamic Properties Task", a stored procedure (Execute SQL Task) and a "Copy SQL Server Objects Task"

It is possible to pass parameters into a DTS at run time. If you were using DTSRUN to execute your DTS then you can add the /A parameter to pass in variable values.

Once your variable name has been passed in you would call a stored procedure that would create a database for you using dynamic SQL

You would then use your Dynamic Properties task to set the destination database to the content of your global variable

and then you would run the Copy SQL Server Objects Task

Hope this helps,
Tom