Fdtxjr

What is Tony Stark injecting into himself in Iron Man 3?

Boss Telling direct supervisor I snitched

Is it appropriate to ask a former professor to order a library book for me through ILL?

Why isn't P and P/poly trivially the same?

Should I file my taxes? No income, unemployed, but paid 2k in student loan interest

Why aren't there more Gauls like Obelix?

Issue with units for a rocket nozzle throat area problem

How can I have x-axis ticks that show ticks scaled in powers of ten?

What is the oldest European royal house?

Exempt portion of equation line from aligning?

Can I negotiate a patent idea for a raise, under French law?

Should I apply for my boss's promotion?

Propulsion Systems

How to distinguish easily different soldier of ww2?

ESPP--any reason not to go all in?

How to educate team mate to take screenshots for bugs with out unwanted stuff

Vector-transposing function

Interpretation of linear regression interaction term plot

Was this cameo in Captain Marvel computer generated?

Is there a logarithm base for which the logarithm becomes an identity function?

Paper published similar to PhD thesis

How can I portion out frozen cookie dough?

Can I challenge the interviewer to give me a proper technical feedback?

How to write a chaotic neutral protagonist and prevent my readers from thinking they are evil?

Does restoring a database break external synonyms targeting objects in that database?

Quickly restoring a backup over top of an existing database - error “database in use”After Restore Log_reuse_wait_desc of ReplicationErrors restoring a differential backupUse database to track locking/unlocking of objectsMy stored procedure randomly fails due to invalid object in a swapping database instanceSQL Server Database Migration - Backup/Restore while Original DB still in ProductionSQL Server message 3154 Server 2005 to 2008Is a partial restore of data from backup possible?SQL Server - redirect synonym without downtimesqlserver RESTORE missing views

4

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

share|improve this question

edited 18 hours ago

Paul White♦

53k14281457

asked 19 hours ago

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
  19 hours ago
  

  
  
  
  

add a comment | 

4

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

share|improve this question

edited 18 hours ago

Paul White♦

53k14281457

asked 19 hours ago

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
  19 hours ago
  

  
  
  
  

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

share|improve this question

edited 18 hours ago

Paul White♦

53k14281457

asked 19 hours ago

ConcernedOfTunbridgeWellsConcernedOfTunbridgeWells

15.9k24868

share|improve this question

edited 18 hours ago

Paul White♦

53k14281457

asked 19 hours ago

ConcernedOfTunbridgeWellsConcernedOfTunbridgeWells

15.9k24868

share|improve this question

edited 18 hours ago

Paul White♦

53k14281457

asked 19 hours ago

ConcernedOfTunbridgeWellsConcernedOfTunbridgeWells

15.9k24868

edited 18 hours ago

Paul White♦

53k14281457

edited 18 hours ago

Paul White♦

53k14281457

asked 19 hours ago

ConcernedOfTunbridgeWellsConcernedOfTunbridgeWells

15.9k24868

asked 19 hours ago

ConcernedOfTunbridgeWellsConcernedOfTunbridgeWells

15.9k24868

1 Answer
1

active

oldest

votes

7

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

share|improve this answer

answered 19 hours ago

Peter VandivierPeter Vandivier

1,1791722

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
);



);

draft saved

draft discarded

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

Name

Email

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

Name

Email

Required, but never shown

7

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

share|improve this answer

answered 19 hours ago

Peter VandivierPeter Vandivier

1,1791722

add a comment | 

7

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

share|improve this answer

answered 19 hours ago

Peter VandivierPeter Vandivier

1,1791722

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

share|improve this answer

answered 19 hours ago

Peter VandivierPeter Vandivier

1,1791722

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

share|improve this answer

answered 19 hours ago

Peter VandivierPeter Vandivier

1,1791722

answered 19 hours ago

Peter VandivierPeter Vandivier

1,1791722

answered 19 hours ago

Peter VandivierPeter Vandivier

1,1791722