How to redirect views to another database?

Bruno71

I have a 2 production databases (ProdA, ProdB) that contain views pointing to each other.  (ProdA <-> ProdB)
These 2 production databases get backed up and restored as 2 testing databases. (TestA, TestB)
The result is that the views in the test databases still point to the other production database.
(TestA <-> ProdB, ProdA <-> TestB)

I need to update the views in the test databases to point to each other.  Right now, I can accomplish it via this method in SSMS...

 1. Right-click on TestA > Tasks... > Generate Scripts...

 2. Select Views only

 3. Save to new query window

 4. Click Advanced

 5. Script as DROP and CREATE

 6. Replace [ProdB]. with [TestB]. in resulting scripts

 7. Run scripts
...and repeat for the other database.

Is there a way to automate this with the Red Gate tools?  I currently have the Toolbelt Essentials.
Thanks!

way0utwest

There isn't a great solution, and we don't really automate this.

I'd do what you listed above, but I'd also think about repeating this. You ought to have a script that fixes these views after a restore. In SQL Clone or RG Clone, we'd link a script to the creation of the test database image, but as a general rule, your process should be scripted as follows:

1. backup ProdA
2. backup ProdB (these can be in parallel)
3. restore TestB (from ProdA backup)
4. restore TestB (from ProdB backup)
5. run CREATE OR ALTER VIEW on TestB to fix all references
   
6. run CREATE OR ALTER VIEW on TestA to fix all references.

If you have the views (or other code) in version control, you can search/replace the code and then run that. 

You could use SQL Compare once you've done this once to save a project that looks at your specific views  (or all views) and then writes to a scripts folder. You could then compare this folder on disk to the restored db, but if you added/changed views, this wouldn't work.

My long term suggestion is to move to synonyms for each object and have all code reference these. Then when you restore, you still need steps 5/6 above, but you are only changing synonyms, which is less maintenance as multiple views/procs/etc could reference one synonym.