I have been asked to create a Test Database that will house copies of tables from 2 different databases( the First Database is hosted on the local server and the other one at a remote site).This database will be used for research purpose.Any suggestions on how to approach this project?
Under normal testing and development I would assume two databases for the two databases that you're working with. The real issue is, what if they both have a Sales.SalesOrderDetail table? How can you put them both into the same database? But, let's assume for a moment, like a lot of systems out there, they're both just in the dbo. schema. Then, if you're forced to put them both into the same database, you can [use a schema] for each. Then you can manage each one independently within the single database. It's still not a good way to go. From there you'll need to build an SSIS package to move the data into the schema's. It's going to be quite a bit of work. And, it's only going to be possible if they're both different schemas. Better, get a backup of each database and restore it to your local server. Now you have the copies you need. First, get the entire structure of each database into source control. If you're doing development, treat the database like code. Then you'll need to work on a deployment process. For more information around this, [read here]. :