Creating mulitple test databases -- can SQL Compare help?

stephen_lee_moore

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!

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

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.

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.

stephen_lee_moore

No problem, then. Just trying to get an idea of what my options are. Thanks for everybody's help!