Saturday, February 25, 2012

Accidentally dropped these tables

I dropped these tables MSreplication_subscriptions and MSsubscription_agents
used for replication on the subscribing end.
How can I recreate them since its a system table.
From sp_MScreate_sub_tables
CREATE TABLE dbo.MSreplication_subscriptions
(
publisher sysname NOT NULL,
publisher_db sysname NOT NULL,
publication sysname NULL,
independent_agent bit NOT NULL,
subscription_type int NOT NULL,
distribution_agent sysname NULL,
time smalldatetime NOT NULL,
description nvarchar(255) NULL,
transaction_timestamp varbinary(16) NOT NULL,
-- SyncTran
update_mode tinyint NOT NULL,
agent_id binary(16) NULL,
subscription_guid binary(16) NULL,
subid binary(16) NULL,
immediate_sync bit NOT NULL default 1 -- sync_mode with a default of 1
)
CREATE TABLE dbo.MSsubscription_agents
(
id int identity,
publisher sysname NOT NULL,
publisher_db sysname NOT NULL,
publication sysname NOT NULL,
subscription_type int NOT NULL,
queue_id sysname NULL,
update_mode tinyint default 0 not null, -- 0 = read only, 1 =
sync/immediate, 2 = queued, 3 = failover, 4 = sqlqueued, 5 = sqlqueued
failover
failover_mode bit default 0 not null, -- 0 - sync/immediate, 1 = queued
spid int NOT NULL,
login_time datetime NOT NULL,
allow_subscription_copy bit default 0 not null,
attach_state int default 0 not null, -- 0: not attached 1 attached but
not processed 2 attached and processed.
attach_version binary(16) default newid() not null,
last_sync_status int NULL, -- allow null for upgrade
last_sync_summary sysname NULL, -- allow null for upgrade
last_sync_time datetime NULL, -- allow null for upgrade
queue_server sysname NULL -- only used for MSMQ based updating
subscribers
)
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23TZtfkaXFHA.3712@.TK2MSFTNGP09.phx.gbl...
> I dropped these tables MSreplication_subscriptions and
MSsubscription_agents
> used for replication on the subscribing end.
> How can I recreate them since its a system table.
>
|||But I guess just creating those tables may not be enough.. Am i right ? I
know it has some data in there .. Will the agents still fail ?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:O6Qwki6XFHA.2124@.TK2MSFTNGP14.phx.gbl...
> From sp_MScreate_sub_tables
> CREATE TABLE dbo.MSreplication_subscriptions
> (
> publisher sysname NOT NULL,
> publisher_db sysname NOT NULL,
> publication sysname NULL,
> independent_agent bit NOT NULL,
> subscription_type int NOT NULL,
> distribution_agent sysname NULL,
> time smalldatetime NOT NULL,
> description nvarchar(255) NULL,
> transaction_timestamp varbinary(16) NOT NULL,
> -- SyncTran
> update_mode tinyint NOT NULL,
> agent_id binary(16) NULL,
> subscription_guid binary(16) NULL,
> subid binary(16) NULL,
> immediate_sync bit NOT NULL default 1 -- sync_mode with a default of 1
> )
>
> CREATE TABLE dbo.MSsubscription_agents
> (
> id int identity,
> publisher sysname NOT NULL,
> publisher_db sysname NOT NULL,
> publication sysname NOT NULL,
> subscription_type int NOT NULL,
> queue_id sysname NULL,
> update_mode tinyint default 0 not null, -- 0 = read only, 1 =
> sync/immediate, 2 = queued, 3 = failover, 4 = sqlqueued, 5 = sqlqueued
> failover
> failover_mode bit default 0 not null, -- 0 - sync/immediate, 1 = queued
> spid int NOT NULL,
> login_time datetime NOT NULL,
> allow_subscription_copy bit default 0 not null,
> attach_state int default 0 not null, -- 0: not attached 1 attached but
> not processed 2 attached and processed.
> attach_version binary(16) default newid() not null,
> last_sync_status int NULL, -- allow null for upgrade
> last_sync_summary sysname NULL, -- allow null for upgrade
> last_sync_time datetime NULL, -- allow null for upgrade
> queue_server sysname NULL -- only used for MSMQ based updating
> subscribers
> )
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23TZtfkaXFHA.3712@.TK2MSFTNGP09.phx.gbl...
> MSsubscription_agents
>

No comments:

Post a Comment