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?
 
Barry CunneyConnect With a Mentor Commented:
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
0
 
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
0
 
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
0
 
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!
0
 
ac_davis2002Author Commented:
Great Help

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.