Solved

Want to recreate database including etl packages

Posted on 2012-03-23
5
307 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Great Help

Thanks
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Problem with SqlConnection 5 108
Full Text Search string 5 32
Updating variable table 9 16
while loop in html mail format 5 31
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now