Does restoring a database break external synonyms targeting objects in that database?
If one has a database where synonyms in another database refer to objects in that database, does restoring a backup into that database invalidate the synonyms?
To be specific, imagine this situation:
- Database Synonym_Targ on a SQL 2008 R2 server has some database objects in it, e.g. a table called dbo.foo
- Database Synonym_Home has a synonym dbo.foo referring to the table dbo.foo in the database Synomym_Targ.
- A backup of the database normally resident in Synonym_Targ is restored into it. This contains an object dbo.foo.
Should one expect this process to invalidate the dbo.foo synonym on Synonym_Host?
sql-server sql-server-2008-r2 restore synonyms
edited Mar 8 at 12:10
Paul White♦
53.1k14283457
asked Mar 8 at 10:16
ConcernedOfTunbridgeWellsConcernedOfTunbridgeWells
15.9k24868
add a comment |
If one has a database where synonyms in another database refer to objects in that database, does restoring a backup into that database invalidate the synonyms?
To be specific, imagine this situation:
- Database Synonym_Targ on a SQL 2008 R2 server has some database objects in it, e.g. a table called dbo.foo
- Database Synonym_Home has a synonym dbo.foo referring to the table dbo.foo in the database Synomym_Targ.
- A backup of the database normally resident in Synonym_Targ is restored into it. This contains an object dbo.foo.
Should one expect this process to invalidate the dbo.foo synonym on Synonym_Host?
sql-server sql-server-2008-r2 restore synonyms
edited Mar 8 at 12:10
Paul White♦
53.1k14283457
asked Mar 8 at 10:16
ConcernedOfTunbridgeWellsConcernedOfTunbridgeWells
15.9k24868
1
Found out what the problem was - the tester's publish configuration had the DB name configured incorrectly for his test environment.
– ConcernedOfTunbridgeWells
Mar 8 at 10:36
add a comment |
If one has a database where synonyms in another database refer to objects in that database, does restoring a backup into that database invalidate the synonyms?
To be specific, imagine this situation:
- Database Synonym_Targ on a SQL 2008 R2 server has some database objects in it, e.g. a table called dbo.foo
- Database Synonym_Home has a synonym dbo.foo referring to the table dbo.foo in the database Synomym_Targ.
- A backup of the database normally resident in Synonym_Targ is restored into it. This contains an object dbo.foo.
Should one expect this process to invalidate the dbo.foo synonym on Synonym_Host?
sql-server sql-server-2008-r2 restore synonyms
edited Mar 8 at 12:10
Paul White♦
53.1k14283457
asked Mar 8 at 10:16
ConcernedOfTunbridgeWellsConcernedOfTunbridgeWells
15.9k24868
If one has a database where synonyms in another database refer to objects in that database, does restoring a backup into that database invalidate the synonyms?
To be specific, imagine this situation:
- Database Synonym_Targ on a SQL 2008 R2 server has some database objects in it, e.g. a table called dbo.foo
- Database Synonym_Home has a synonym dbo.foo referring to the table dbo.foo in the database Synomym_Targ.
- A backup of the database normally resident in Synonym_Targ is restored into it. This contains an object dbo.foo.
Should one expect this process to invalidate the dbo.foo synonym on Synonym_Host?
sql-server sql-server-2008-r2 restore synonyms
sql-server sql-server-2008-r2 restore synonyms
edited Mar 8 at 12:10
Paul White♦
53.1k14283457
asked Mar 8 at 10:16
ConcernedOfTunbridgeWellsConcernedOfTunbridgeWells
15.9k24868
edited Mar 8 at 12:10
Paul White♦
53.1k14283457
asked Mar 8 at 10:16
ConcernedOfTunbridgeWellsConcernedOfTunbridgeWells
15.9k24868
edited Mar 8 at 12:10
Paul White♦
53.1k14283457
edited Mar 8 at 12:10
Paul White♦
53.1k14283457
edited Mar 8 at 12:10
Paul White♦
53.1k14283457
53.1k14283457
asked Mar 8 at 10:16
ConcernedOfTunbridgeWellsConcernedOfTunbridgeWells
15.9k24868
asked Mar 8 at 10:16
ConcernedOfTunbridgeWellsConcernedOfTunbridgeWells
15.9k24868
asked Mar 8 at 10:16
ConcernedOfTunbridgeWellsConcernedOfTunbridgeWells
15.9k24868
15.9k24868
1
Found out what the problem was - the tester's publish configuration had the DB name configured incorrectly for his test environment.
– ConcernedOfTunbridgeWells
Mar 8 at 10:36
add a comment |
1
Found out what the problem was - the tester's publish configuration had the DB name configured incorrectly for his test environment.
– ConcernedOfTunbridgeWells
Mar 8 at 10:36
1
1
Found out what the problem was - the tester's publish configuration had the DB name configured incorrectly for his test environment.
– ConcernedOfTunbridgeWells
Mar 8 at 10:36
Found out what the problem was - the tester's publish configuration had the DB name configured incorrectly for his test environment.
– ConcernedOfTunbridgeWells
Mar 8 at 10:36
add a comment |
1 Answer
1
active
oldest
votes
This process should not invalidate the synonym. As per the docs:
The binding between a synonym and its base object is by name only. All existence, type, and permissions checking on the base object is deferred until run time. Therefore, the base object can be modified, dropped, or dropped and replaced by another object that has the same name as the original base object.
You can test this behavior with the following.
use [master]
go
drop database if exists Synonym_Targ, Synonym_Home
go
create database Synonym_Targ
create database Synonym_Home
go
alter authorization on database ::Synonym_Targ to sa
alter authorization on database ::Synonym_Home to sa
go
use Synonym_Targ
go
create table dbo.foo ( i int default 1);
go
insert dbo.foo default values
go
use Synonym_Home
go
create synonym dbo.foo for Synonym_Targ.dbo.foo
go
create or alter proc p
as
select * from dbo.foo
go
exec p
go
backup database Synonym_Targ to disk = 'c:tempSynonym_Targ.bak'
go
drop database Synonym_Targ
go
exec p
/*
Msg 5313, Level 16, State 1, Procedure p, Line 3 [Batch Start Line 30]
Synonym 'dbo.foo' refers to an invalid object.
*/
go
restore database Synonym_Targ from disk = 'c:tempSynonym_Targ.bak'
go
exec p
go
use [master]
go
drop database if exists Synonym_Targ, Synonym_Home
go
answered Mar 8 at 10:24
Peter VandivierPeter Vandivier
1,2141722
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f231638%2fdoes-restoring-a-database-break-external-synonyms-targeting-objects-in-that-data%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
This process should not invalidate the synonym. As per the docs:
The binding between a synonym and its base object is by name only. All existence, type, and permissions checking on the base object is deferred until run time. Therefore, the base object can be modified, dropped, or dropped and replaced by another object that has the same name as the original base object.
You can test this behavior with the following.
use [master]
go
drop database if exists Synonym_Targ, Synonym_Home
go
create database Synonym_Targ
create database Synonym_Home
go
alter authorization on database ::Synonym_Targ to sa
alter authorization on database ::Synonym_Home to sa
go
use Synonym_Targ
go
create table dbo.foo ( i int default 1);
go
insert dbo.foo default values
go
use Synonym_Home
go
create synonym dbo.foo for Synonym_Targ.dbo.foo
go
create or alter proc p
as
select * from dbo.foo
go
exec p
go
backup database Synonym_Targ to disk = 'c:tempSynonym_Targ.bak'
go
drop database Synonym_Targ
go
exec p
/*
Msg 5313, Level 16, State 1, Procedure p, Line 3 [Batch Start Line 30]
Synonym 'dbo.foo' refers to an invalid object.
*/
go
restore database Synonym_Targ from disk = 'c:tempSynonym_Targ.bak'
go
exec p
go
use [master]
go
drop database if exists Synonym_Targ, Synonym_Home
go
answered Mar 8 at 10:24
Peter VandivierPeter Vandivier
1,2141722
add a comment |
This process should not invalidate the synonym. As per the docs:
The binding between a synonym and its base object is by name only. All existence, type, and permissions checking on the base object is deferred until run time. Therefore, the base object can be modified, dropped, or dropped and replaced by another object that has the same name as the original base object.
You can test this behavior with the following.
use [master]
go
drop database if exists Synonym_Targ, Synonym_Home
go
create database Synonym_Targ
create database Synonym_Home
go
alter authorization on database ::Synonym_Targ to sa
alter authorization on database ::Synonym_Home to sa
go
use Synonym_Targ
go
create table dbo.foo ( i int default 1);
go
insert dbo.foo default values
go
use Synonym_Home
go
create synonym dbo.foo for Synonym_Targ.dbo.foo
go
create or alter proc p
as
select * from dbo.foo
go
exec p
go
backup database Synonym_Targ to disk = 'c:tempSynonym_Targ.bak'
go
drop database Synonym_Targ
go
exec p
/*
Msg 5313, Level 16, State 1, Procedure p, Line 3 [Batch Start Line 30]
Synonym 'dbo.foo' refers to an invalid object.
*/
go
restore database Synonym_Targ from disk = 'c:tempSynonym_Targ.bak'
go
exec p
go
use [master]
go
drop database if exists Synonym_Targ, Synonym_Home
go
answered Mar 8 at 10:24
Peter VandivierPeter Vandivier
1,2141722
add a comment |
This process should not invalidate the synonym. As per the docs:
The binding between a synonym and its base object is by name only. All existence, type, and permissions checking on the base object is deferred until run time. Therefore, the base object can be modified, dropped, or dropped and replaced by another object that has the same name as the original base object.
You can test this behavior with the following.
use [master]
go
drop database if exists Synonym_Targ, Synonym_Home
go
create database Synonym_Targ
create database Synonym_Home
go
alter authorization on database ::Synonym_Targ to sa
alter authorization on database ::Synonym_Home to sa
go
use Synonym_Targ
go
create table dbo.foo ( i int default 1);
go
insert dbo.foo default values
go
use Synonym_Home
go
create synonym dbo.foo for Synonym_Targ.dbo.foo
go
create or alter proc p
as
select * from dbo.foo
go
exec p
go
backup database Synonym_Targ to disk = 'c:tempSynonym_Targ.bak'
go
drop database Synonym_Targ
go
exec p
/*
Msg 5313, Level 16, State 1, Procedure p, Line 3 [Batch Start Line 30]
Synonym 'dbo.foo' refers to an invalid object.
*/
go
restore database Synonym_Targ from disk = 'c:tempSynonym_Targ.bak'
go
exec p
go
use [master]
go
drop database if exists Synonym_Targ, Synonym_Home
go
answered Mar 8 at 10:24
Peter VandivierPeter Vandivier
1,2141722
This process should not invalidate the synonym. As per the docs:
The binding between a synonym and its base object is by name only. All existence, type, and permissions checking on the base object is deferred until run time. Therefore, the base object can be modified, dropped, or dropped and replaced by another object that has the same name as the original base object.
You can test this behavior with the following.
use [master]
go
drop database if exists Synonym_Targ, Synonym_Home
go
create database Synonym_Targ
create database Synonym_Home
go
alter authorization on database ::Synonym_Targ to sa
alter authorization on database ::Synonym_Home to sa
go
use Synonym_Targ
go
create table dbo.foo ( i int default 1);
go
insert dbo.foo default values
go
use Synonym_Home
go
create synonym dbo.foo for Synonym_Targ.dbo.foo
go
create or alter proc p
as
select * from dbo.foo
go
exec p
go
backup database Synonym_Targ to disk = 'c:tempSynonym_Targ.bak'
go
drop database Synonym_Targ
go
exec p
/*
Msg 5313, Level 16, State 1, Procedure p, Line 3 [Batch Start Line 30]
Synonym 'dbo.foo' refers to an invalid object.
*/
go
restore database Synonym_Targ from disk = 'c:tempSynonym_Targ.bak'
go
exec p
go
use [master]
go
drop database if exists Synonym_Targ, Synonym_Home
go
answered Mar 8 at 10:24
Peter VandivierPeter Vandivier
1,2141722
answered Mar 8 at 10:24
Peter VandivierPeter Vandivier
1,2141722
answered Mar 8 at 10:24
Peter VandivierPeter Vandivier
1,2141722
answered Mar 8 at 10:24
Peter VandivierPeter Vandivier
1,2141722
1,2141722
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f231638%2fdoes-restoring-a-database-break-external-synonyms-targeting-objects-in-that-data%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Found out what the problem was - the tester's publish configuration had the DB name configured incorrectly for his test environment.
– ConcernedOfTunbridgeWells
Mar 8 at 10:36