Solved

Want to recreate database including etl packages

Posted on 2012-03-23
5
309 Views
Last Modified: 2012-03-23
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
0
Comment
Question by:ac_davis2002
  • 3
  • 2
5 Comments
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 37756480
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
 

Author Comment

by:ac_davis2002
ID: 37756736
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
 
LVL 17

Accepted Solution

by:
Barry Cunney earned 500 total points
ID: 37756829
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
 

Author Comment

by:ac_davis2002
ID: 37756935
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
 

Author Closing Comment

by:ac_davis2002
ID: 37757332
Great Help

Thanks
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question