December 14, 2020 at 5:23 pm
I need to restore the database from one server to another with different name and need to add the database in AG on the other server. How to change the reference of the objects not to point to the old database which I restored from the source . I want the objects point to the new database name which I restored with different name .
December 14, 2020 at 8:49 pm
It isn't very clear what you are trying to accomplish...are you saying you have code in a database that references another database? Or - you have code that uses 3-part naming in that database that references the same database?
If the latter - then you have to modify the code and either remove the database reference or change the code to reference the new name.
If the former - you are still going to need to change the code, but you can change the code to use a synonym instead of the 3-part name so in the future all you have to change is the synonyms.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 15, 2020 at 2:14 am
Sone of the stored procedures having 3 naming in the database if we restored with different name it will point to the old database name . How to resolve the issue to point to the new database name also steps where to modify.
December 15, 2020 at 1:29 pm
The restore operation using WITH MOVE will allow you to rename the database and create new data files. However, the only thing that will change the code is changing the code. This is by design. A restore is a page by page copy of the database in question. We wouldn't want it any other way.
In short, if you've got multipart names inside of views, procedures or functions, you'll have to modify those. There's no other way to deal with it... Well, or, you modify the core code to use synonyms and then you only have to modify the synonyms after you move the database. That is easier, but still requires the code to be modified.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 16, 2020 at 5:25 am
The restore operation using WITH MOVE will allow you to rename the database and create new data files. However, the only thing that will change the code is changing the code. This is by design. A restore is a page by page copy of the database in question. We wouldn't want it any other way.
In short, if you've got multipart names inside of views, procedures or functions, you'll have to modify those. There's no other way to deal with it... Well, or, you modify the core code to use synonyms and then you only have to modify the synonyms after you move the database. That is easier, but still requires the code to be modified.
Amen to using ONLY 2 part naming and synonyms for anything that would otherwise require more parts. They've been a real life and time saver for me.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 16, 2020 at 1:32 pm
And for reference, here's the documentation on synonyms.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 16, 2020 at 3:43 pm
Thanks for the update .
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply