Solved

Want to recreate database including etl packages

Posted on 2012-03-23
5
312 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Turn Insights Into Action

You’ve already invested in ITSM tools, chat applications, automation utilities, and more. Fortify these solutions with intelligent communications so you can drive business processes forward.

With xMatters, you'll never miss a beat.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

690 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