Want to recreate database including etl packages

Hi

I have created a database call reportsdev, this was initially a dev database. I now want to use this as a live database and recreate a second identical database from this
The dev database also has etl packages that depend on it, i want to recreate those as well so in the end i have two identical database environments one called reportslive and one called reportsdev both the same with the same packages.

What is the best way for me to do this also bearing in mind some of the package and views have the name reportsdev hard coded in them (a lesson learnt!!)


Thanks
ac_davis2002Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Barry CunneyCommented:
For your DDL you could use the Generate Scripts Wizard.
Right-click on your DEV database, from the pop-up menu choose Tasks and then Generate Scripts - this will take you through a wizard that will alow you to script objects in your database - carefully assess each option. You can then change the database name and file names in the generated script file so as the same DEV objects are created in a new LIVE database.

With regard to the ETL packages, I presume you mean SSIS: Are these stored in the SQL Server or on the File System?

If you have the time I would rexamine how these were created/designed - you should have a BIDS project with the packages or are your packages DTS packages pre-SSIS.
If they are SSIS and you have the BIDS project you should maybe look into doing a bit of re-design - create a config file and store DB connection details in the config file - Create/alter the required Connection Managers to get their connection details from the config file.
This will leave you in a great generic position - then all you will have to do is recompile a LIVE version of your SSIS project but with the config file updated with LIVE db connection details(only 1 change) as opposed to possibly having to create a copy of all the packages and alter multiple connection details
ac_davis2002Author Commented:
ok that sounds like a really good approched i will do that.

One thought, is there anyway i can write a script maybe using isql or something that will loop through all of my database scripts and update the value reportsdev and change it to reportslive...

Cheers
Barry CunneyCommented:
well you can use find and replace in SQL Server itself, but take a few steps back even before this I would try and do some kind of design or other approach so you avoid having to do multiple changes of the database name - see can you re-engineer so as you are only changing an entry once  - come back to me with any specific questions

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ac_davis2002Author Commented:
ok that has worked well...i removed all hardcoded references and have now created the new database.......

just a thought but is there any check i can do to make sure the same number of objects exist on both databases...for example the same number of views exist on both and if any are missing i can work out why?

Thanks again and appologies for extra question!
ac_davis2002Author Commented:
Great Help

Thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.