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

Question

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?

Answer 1

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:\temp\Synonym_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:\temp\Synonym_Targ.bak'
go
exec p
go
use [master]
go
drop database if exists Synonym_Targ, Synonym_Home
go