How can we help you today? How can we help you today?

Creating mulitple test databases -- can SQL Compare help?

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!
stephen_lee_moore
0

Comments

4 comments

Sort by
  • jerryhung
    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
    jerryhung
    0
  • stephen_lee_moore
    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.
    stephen_lee_moore
    0
  • Michelle T
    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.
    Michelle T
    0
  • stephen_lee_moore
    No problem, then. Just trying to get an idea of what my options are. Thanks for everybody's help!
    stephen_lee_moore
    0

Add comment

Please sign in to leave a comment.