SQL Database restore and update all object references?

SQL Server Administration
1

Is there a way in SQL Server to backup existing DB's and then restore as a temporary name DB and update the references to other DB's? Example:

DB1
DB2
DB3

Imagine DB1 having object references to DB2 and DB3. When I restore, I want to change the references of DB2 and DB3 to a different reference.

using SQL Server 2016

2

If those references are embedded in the code (stored procedures, functions, views) then you need to update all of those with the new references.

The method for avoiding that is to utilize synonyms for all references outside that database. I would utilize both a separate schema and synonyms.

CREATE SCHEMA db2 AUTHORIZATION dbo;

Then create synonyms:

CREATE SYNONYM db2.Object1 FOR db2.dbo.Object1;
CREATE SYNONYM db2.Object2 FOR db2.dbo.Object2;
...

In your code:

SELECT ... FROM db2.Object1;
SELECT ... FROM db2.Object2;

Once you have that, you can then backup DB1 and restore to another system. After restoring the database - drop the synonyms and recreate them using the new database:

DROP SYNONYM db2.Object1;
CREATE SYNONYM db2.Object1 FOR newdb2.dbo.Object1;
DROP SYNONYM db2.Object2;
CREATE SYNONYM db2.Object2 FOR newdb2.dbo.Object2;

Do that - and all database references in your code will be redirected to the new database without any code changes needed.

1 Like
3

Thank you.

Ouch :slight_smile:

Since we have 3 DB's, and LOTS of Tables, Views and SPROCs... looks like it won't be easy. Is there another alternative taking something we have already in terms of existing DB's?

4

If you keep all your database schema in a Visual Studio database project, and use it to do all deployments, you can:

  1. Modify your procedure code to use deployment variables for database, table, or other names that would change in different environments. For instance, DB1.dbo.myProcedure would be written as $(db_ref).dbo.myProcedure. You'd then set the project variable db_ref to "DB1" when you deploy it to the correct environment. This doesn't save you from editing code, and deploying this way won't include any data.

  2. Using DB projects in Visual Studio, there is a Refactor feature that allows you to rename objects, databases, etc. It will globally change all references in the project. This saves typing, but still doesn't deploy data. Note that this is different from doing a global find/replace in the procedure code, which may not work correctly. Refactor/rename does work correctly.

I've used both options, both are useful, but generally only for deploying empty databases. From my own experience working with a system that had about 25 databases, some with 1200 tables and 4000+ procedures, @jeffw8713 's suggestion to use synonyms is the way to go. It's also pretty easy to generate the script for creating the new synonyms, and easy to parameterize for multiple databases.

5

You are going to have to use synonyms in future anyway as three and four part naming is going to be depreciated. Search for Three-part in the following:

Deprecated Database Engine Features - SQL Server 2016 | Microsoft Learn

6

3-part naming is not deprecated in the FROM clause. 3-part and 4-part naming is deprecated as a column reference.

So you can still do this:

FROM db1.dbo.object1

But - this has been deprecated:

SELECT dbo.table1.column1
FROM dbo.table1
7

No - your options are limited. You must update the code if you have hard-coded references to specific database name(s) and you want to change the database name being referenced.

Either you take the time and effort now to update your code to use a different schema and synonyms - or you accept the fact that you have to update all of the code every time you want to deploy/copy that database to another server where the other databases can have different names.

Using schema and synonyms - you update the code one time and any new development follows the same pattern. Using any other approach and you are updating the code every deployment.

8

Thanks for the clarification.

9

Thanks @jeffw8713.
OK so let me add a twist.

Trying to do some integration testing. It references 3 other DBs and has huge amounts of data.
It is also shared with other teams so my idea was to isolate before running each integration testing with the data.

Even if I do what you say, it means we still need to copy the large amounts of data into the newly created DBs every time...

10

Then you either incorporate that data into your database project, which runs as a post-deployment script, or you figure out the correct synonyms to use in your procedure code and create the necessary scripts to repoint those synonyms to the correct DBs/tables/etc. for each environment you restore to.

I still agree with @jeffw8713 that synonyms are going to be your best solution.

11

I agree.
Unfortunately we can't use the project deployment stuff as it's all Devops and automated.

12

To avoid the need of changing all referenced names in stored procedures when restoring to test, we build dynamic SQL statements that get the referenced names from a table. After the restore we only need to change the names in the table.

1 Like