Multiple packages at once

Forum for users of SQL Packager database archive utility

Moderator: David Atkinson

Multiple packages at once

Postby Shaydz » Wed May 09, 2007 8:42 pm

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
Shaydz
 
Posts: 1
Joined: Wed May 09, 2007 8:30 pm

Postby Brian Donahue » Sun May 13, 2007 2:41 pm

Hi,

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.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby David Atkinson » Mon May 14, 2007 3:42 pm

Brad,

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
David Atkinson
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase


Return to SQL Packager Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests