Options
Multiple packages at once
Shaydz
Posts: 2
I've searched a bit and haven't found this question.
Please indulge me a moment.
I have been looking at this product for a specific need regarding a training environment setup. Would like to know if this is the product to consider
I have 3 different databases (call them prime).
1) I need to detach these 3 databases from 8 instances of SQL Server. (same box)
2) copy the 3 prime databases to the physical location for each instance
3) reattach the databases in each instance (the location has to be changed from prime to each instance location)
4) Reset the permissions
5) Go on my merry way.
I know that I can create a .exe to do these actions for 1 database on 1 instance but I have essentially 24 of these (3 db's x 8 instances).
Finally, the question. Can a single .exe package do this and/or can this be accomplished through a batch of some sort? Basically I'm looking for a way to kick off manually to create this environment. (Set back to prime each day).
Thanks you for any and all suggestions.
Brad
Please indulge me a moment.
I have been looking at this product for a specific need regarding a training environment setup. Would like to know if this is the product to consider
I have 3 different databases (call them prime).
1) I need to detach these 3 databases from 8 instances of SQL Server. (same box)
2) copy the 3 prime databases to the physical location for each instance
3) reattach the databases in each instance (the location has to be changed from prime to each instance location)
4) Reset the permissions
5) Go on my merry way.
I know that I can create a .exe to do these actions for 1 database on 1 instance but I have essentially 24 of these (3 db's x 8 instances).
Finally, the question. Can a single .exe package do this and/or can this be accomplished through a batch of some sort? Basically I'm looking for a way to kick off manually to create this environment. (Set back to prime each day).
Thanks you for any and all suggestions.
Brad
Comments
Certainly you can use SQL Packager to do this. Once you have created a package for each of the three databases, you can run them through a batch file. You could run a quick SQLCMD script to drop the databases if they already exist, then run the package using its' command-line arguments where you can specify the username, password, server, database name, and the /makedatabase switch to recreate the database anew.
The only thing to watch out for is if you have SQL security users in the database as they well be created with a password of 'P@ssword' if the login doesn't exist. Windows users can also be a headache if they're qualified to the machine name. The best strategy is to try to make sure all of the necessary security logins already exist on the server before running the package.
Do let us know how you get on with this.
Can I ask why you aren't simply using a sqlcmd script using sp_detach_db and sp_attach_db?
We're looking at ways to improve how SQL Packager deals with users and logins so it would be useful if you could tell us what works and more importantly, what doesn't work for you and we can discuss ways in which we can fix this going forward.
Best regards,
David Atkinson
Product Manager
Red Gate Software
Product Manager
Redgate Software