Creating mulitple test databases -- can SQL Compare help?

edited April 3, 2008 10:54AM in SQL Compare Previous Versions
Hello. We have multiple databases that we use together. For example, we have a payroll database that comes from a vendor, and a custom database with our custom code. We have stored procs in the custom DB that reference objects in both databases, so we fully qualify the object names with database name and schema. In other words, a stored proc might join payroll.dbo.table with custom.dbo.table.

We would like to create a test environment in the same instance for both databases, so we would want to create testpayroll and testcustom. And the stored procs would need to reference testpayroll.dbo.table and testcustom.dbo.table at the same time.

I didn't know if SQL Compare (or another redgate tool) could both propagate objects from one db to another and then change the fully qualified references inside stored procs, functions, views, etc. I don't think it would be a problem if all of the relevant objects were in the same DB, as we could just leave off the db qualifier.

The only other option I can think of is a separate instance, that way the db names can stay the same.

Any help is appreciated!

Comments

  • so is it not preferable to have a test SQL server with payroll+custom database? or like you said separate instance

    that way SPs won't need to be changed
    Jerry Hung
    DBA, MCITP
  • I would prefer to keep things in the same instance if possible. If the redgate tools can help me out with the renaming then that would be the ideal situation for us. If the tools aren't meant to deal with these kinds of problems, then I'll just do another instance.
  • Michelle TMichelle T Posts: 566 Gold 1
    I'm afraid that SQL Compare doesn't change the contents of stored procedures and similar textual objects in this way - we generally try to leave the contents of textual objects alone as much as possible.
    Software Developer
    Redgate Software
  • No problem, then. Just trying to get an idea of what my options are. Thanks for everybody's help!
Sign In or Register to comment.