Restore production database to multiple test databases SQL server

Question

What is the best way to copy a production database to multiple test databases, while maintaining the integrity of fully qualified names?

Currently to refresh a test environment we restore the test database from the production database. Then, we script all of the stored procedures/views/etc. and do a find/replace on all of the database references to point at the test objects. After we have all of the references correct, we alter them.

For example, after the database is copied from production, we'll have a stored procedure like so:

alter procedure dbo.SomeProcedure
as
select SomeColumn
from DB.dbo.SomeTable
join Validation.dbo.AnotherTable on SomId = AnoId

For the test database, it needs to be:

alter procedure dbo.SomeProcedure
as
select SomeColumn
from DBQA1.dbo.SomeTable
join ValidationQA1.dbo.AnotherTable on SomId = AnoId

Each test database has views/stored procedures/functions can reference up to 30 different other test databases, so the "find/replace" process is very time consuming and is prone to a lot of errors.

What is the best way to restore these test environments?

We are using SQL Server 2008R2.

StuartLC · Accepted Answer · 2012-08-02 05:52:47Z

1

Assuming that the different environments are on different SQL Servers (or at least on different Instances), then would recommend that you keep the database names on all environments exactly the same. Use permissions (e.g. Integrated security) to ensure that only the correct environment systems and users access the appropriate environment databases.

However, if you do need to keep different database names for different environments (e.g. all environments on the same SQL instance), you could look at using sqlcmd with the -v switch to parameterize the database name.

Your change scripts would then need to be rewritten like so:

alter procedure dbo.SomeProcedure
as
   select SomeColumn
   from [$(InternetSecurity)].dbo.SomeTable
   join [$(Validation)].dbo.AnotherTable on SomId = AnoId

And then you could write batch files to pass the correct parameter values to sqlcmd.

Alternatively, you could use a .dbproj project in Visual Studio to setup multiple configurations to provide different values for each environment, and generate scripts / publish from Visual Studio.

Also, AFAIK SQL Synonyms are't really going to help here. You would need to replace the 3 part table names in all procs and functions with synonyms, which could confuse the issue as it doesn't make it clear whether the table is local or external.

edited Aug 2, 2012 at 5:52

answered Aug 2, 2012 at 5:15

StuartLC

108k18 gold badges225 silver badges294 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Garrett Geier Over a year ago

This accept is long overdue. But sqlcmd was best solution to this problem. Thankfully, it's not needed anymore since we were able to get away from that setup. We use instances now.

AnandPhadke · Accepted Answer · 2012-08-02 05:24:00Z

0

As far as I know,There is no other simpler way than replacing the database names in the script here.

answered Aug 2, 2012 at 5:24

AnandPhadke

13.5k5 gold badges28 silver badges34 bronze badges

Collectives™ on Stack Overflow

Restore production database to multiple test databases SQL server

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related