Invalid object name 'master.dbo.MSreplication_options' when restoring DB

I've tried Googling for an answer to this but have got nowhere.

I have one SQL Server instance whose replication is set up to be a publisher and a distributor of FooDatabase. I have a second instance whose replication is set up to be a subscriber of FooDatabase on the first instance. Replication updates work. What I want to do is backup the subscription FooDatabase, delete it on the subscriber instance, and restore the backup. However, when I do this and try to restore the backup, I get the following error:


Restore failed for Server 'DAVEG1525-162'. (Microsoft.SqlServer.SmoExtended)


System.Data.SqlClient.SqlError: Invalid object name 'master.dbo.MSreplication_options'. (Microsoft.SqlServer.Smo)

I've checked and dbo.MSreplication_options does exist as a system table in the master database on the production instance, but not on the subscription instance.

My questions are: if it doesn't exist on the subscription instance, why is it being included on the backup made from the subscription instance of SQL Server? and how can I fix this problem?

As far as I can tell, the table master.dbo.MSreplication_options, amongst various other replication-related tables, are supposed to exist on any SQL Server 2008 r2 install whether or not you've set up replication. Somehow the one on my SQL Server instance got dropped. I eventually found this handy script on Google (cached!) which 'fixes up' various replication things, including recreating master.dbo.MSreplication_options if it doesn't already exist:

```
-- *
-- Copyright (c) 1997 - 2003 Microsoft Corporation.
-- All Rights Reserved
--
-- @File: ReplicationFixup.sql
--
-- Purpose:
-- Procedures/extended stored procedures (XPs)/functions that are owned by replication and are created on master database
--
-- Notes: Created 2001/02/07 10:28 (RMak)
--
-- History:
--
-- @Version: Yukon
--
-- @EndHeader@
--
exec sys.sp_configure 'update',1
reconfigure with override

set ANSI_NULLS off

use master
go

-- Make sure that we remove procedures that got accidentally installed in
-- master by an 80 sp2 QFE
if object_id('dbo.sp_MSreplremoveuncdir', 'P') > 0
drop procedure dbo.sp_MSreplremoveuncdir

if object_id('dbo.sp_MSdeletefoldercontents', 'P') > 0
drop procedure dbo.sp_MSdeletefoldercontents

-- drop extended procedures that were created in master

if object_id('xp_mergexpusage', 'local') is not null
execute sys.sp_dropextendedproc 'xp_mergexpusage'

if object_id('xp_mergelineages', 'local') is not null
execute sys.sp_dropextendedproc 'xp_mergelineages'

if object_id('xp_mapdown_bitmap', 'local') is not null
execute sys.sp_dropextendedproc 'xp_mapdown_bitmap'

if object_id('xp_ORbitmap', 'local') is not null
execute sys.sp_dropextendedproc 'xp_ORbitmap'

if object_id('xp_firstonly_bitmap', 'local') is not null
execute sys.sp_dropextendedproc 'xp_firstonly_bitmap'

if object_id('xp_varbintohexstr', 'local') is not null
execute sys.sp_dropextendedproc 'xp_varbintohexstr'

if object_id('xp_intersectbitmaps', 'local') is not null
execute sys.sp_dropextendedproc 'xp_intersectbitmaps'

if object_id('xp_displayparamstmt', 'local') is not null
execute sys.sp_dropextendedproc 'xp_displayparamstmt'

if object_id('xp_printstatements', 'local') is not null
execute sys.sp_dropextendedproc 'xp_printstatements'

if object_id('xp_makecab', 'local') is not null
exec sys.sp_dropextendedproc 'xp_makecab'

if object_id('xp_unpackcab', 'local') is not null
exec sys.sp_dropextendedproc 'xp_unpackcab'

if object_id('sp_repldone', 'local') is not null
exec sys.sp_dropextendedproc 'sp_repldone'

if object_id('sp_repltrans', 'local') is not null
exec sys.sp_dropextendedproc 'sp_repltrans'

if object_id('sp_replcounters', 'local') is not null
exec sys.sp_dropextendedproc 'sp_replcounters'

if object_id('sp_replhelp', 'local') is not null
exec sys.sp_dropextendedproc 'sp_replhelp'

if object_id('sp_replddlparser', 'local') is not null
exec sys.sp_dropextendedproc 'sp_replddlparser'

if object_id('sp_replcmds', 'local') is not null
exec sys.sp_dropextendedproc 'sp_replcmds'

if object_id('sp_replflush', 'local') is not null
exec sys.sp_dropextendedproc 'sp_replflush'

if object_id('sp_replpostcmd', 'local') is not null
exec sys.sp_dropextendedproc 'sp_replpostcmd'

if object_id('sp_replincrementlsn_internal', 'local') is not null
exec sys.sp_dropextendedproc 'sp_replincrementlsn_internal'

if object_id('sp_replupdateschema', 'local') is not null
exec sys.sp_dropextendedproc 'sp_replupdateschema'

if object_id('sp_replsetoriginator_internal', 'local') is not null
exec sys.sp_dropextendedproc 'sp_replsetoriginator_internal'

if object_id('sp_replsetsyncstatus', 'local') is not null
exec sys.sp_dropextendedproc 'sp_replsetsyncstatus'

if object_id('sp_replpostsyncstatus_int', 'local') is not null
exec sys.sp_dropextendedproc 'sp_replpostsyncstatus_int'

if object_id('xp_dsninfo', 'local') is not null
exec sys.sp_dropextendedproc 'xp_dsninfo'

if object_id('xp_enumdsn', 'local') is not null
exec sys.sp_dropextendedproc 'xp_enumdsn'

if object_id('xp_oledbinfo', 'local') is not null
exec sys.sp_dropextendedproc 'xp_oledbinfo'

if object_id('xp_repl_encrypt', 'local') is not null
exec sys.sp_dropextendedproc 'xp_repl_encrypt'

if object_id('xp_repl_convert_encrypt', 'local') is not null
exec sys.sp_dropextendedproc 'xp_repl_convert_encrypt'

if object_id('xp_repl_help_connect', 'local') is not null
exec sys.sp_dropextendedproc 'xp_repl_help_connect'

if object_id('xp_replproberemsrv', 'local') is not null
exec sys.sp_dropextendedproc 'xp_replproberemsrv'

go

--
-- Create table dbo.MSreplication_options in master if needed
--
if object_id(N'dbo.MSreplication_options', 'local') is null
BEGIN
-- table does not exist
raiserror('Creating table MSreplication_options',0,1)

CREATE TABLE dbo.MSreplication_options
(

StackExchange Database Administrators Q#45988, answer score: 5