SQL Server initializes the subscription with a backup

Original: SQL Server initializes the subscription with a backup

When you create a transactional replication, if the publication database is large, the snapshot initialization is used for a long time, and if a problem occurs, it may be reinitialized. Using Backup initialization can save a lot of time, but the database cannot be accessed during the creation of a publication subscription. Use Backup instead of snapshot to migrate data, backup can compress or use FTP transmission, if in the same room, copy copy is faster.

Test Now:

Create a test library (publisher)

--Create Test library (publisher) use [Master]gocreate DATABASE [Pubdb]on  PRIMARY (NAME = n ' pubdb ', FILENAME = N ' D:\Program files\ Microsoft SQL server\mssql12. Mssqlserver\mssql\data\pubdb.mdf ', SIZE = 5120KB, filegrowth = 1024KB) LOG on (NAME = n ' pubdb_log ', FILENAME = N ' D:\Prog Ram Files\Microsoft SQL Server\mssql12. Mssqlserver\mssql\data\pubdb_log.ldf ', SIZE = 1024KB, filegrowth = 10%) go--Create test table (publisher) use [Pubdb]gocreate table [dbo]. [Pubtab] ([ID] [int] IDENTITY (max) not null,[name] [varchar] (a) not null,[value] [decimal] (4) null,constraint [Pk_pubtab] PRIM ARY KEY CLUSTERED ([id] ASC),) on [Primary]goinsert to [dbo]. [Pubtab] ([Name],[value]) VALUES (' AA ', ' Max '), (' BB ', ' Max ') GO

Add a login account and database user for replication. Subscriptions are not added to the same instance of the login account (publisher/subscriber)

--Add the login account and database user for replication. Subscription is not the same instance of the Add login account (publisher/subscriber) use [master]gocreate login [Repluser] with password=n ' Repluser ', default_database=[ Master], Check_expiration=off, check_policy=offgoalter SERVER ROLE [sysadmin] ADD MEMBER [repluser]gouse [PUBDB] Gocreate USER [Repluser] for LOGIN [Repluser]go

Create a publication (publisher)

After a complete copy is created, the settings allow initialization from the backup file (Publisher publishing database)

--Allow initialization from backup file (publisher publish database) use [pubdb]goexec sp_changepublication    @publication = n ' publname ',     @property = N ' Allow_initialize_from_backup ',     @value = Truego

backing up a database (publisher)

Use [Master]gobackup DATABASE [pubdb] to  DISK = N ' D:\Program Files\Microsoft SQL Server\mssql12. Mssqlserver\mssql\backup\pubdb.bak ' with Noformat, noinit,  NAME = N ' pubdb-full database backup ', SKIP, Norewind, Nounload,  STATS = 10GO

Restore the most recent backup database as a subscription repository (subscriber)

Use [Master]gorestore DATABASE [subdb] from  DISK = N ' D:\Program Files\Microsoft SQL Server\mssql12. Mssqlserver\mssql\backup\pubdb.bak '  with FILE = 1,  MOVE n ' pubdb ' to n ' D:\Program Files\Microsoft SQL Server\ MSSQL12. Mssqlserver\mssql\data\subdb.mdf ', MOVE n ' pubdb_log ' to n ' D:\Program Files\Microsoft SQL Server\mssql12. Mssqlserver\mssql\data\subdb_log.ldf ',  nounload,  STATS = 5GO

Add subscription, specify backup file (Publisher publication database)

Use [pubdb]goexec sp_addsubscription@publication = n ' publname ', @subscriber = N ' hzc ', @destination_db = N ' subdb ', @ subscription_type = N ' Push ', @sync_type = N ' Initialize with backup ', @backupdevicetype = ' disk ', @backupdevicename = ' d:\ Program Files\Microsoft SQL Server\mssql12. Mssqlserver\mssql\backup\pubdb.bak '

After creating the subscription, change the Subscriber connection account

Now that the publish subscription has been configured, look at the data in the table.

SELECT * from  [pubdb].[ DBO]. [Pubtab] SELECT * from  [subdb].[ DBO]. [Pubtab]

Add a row of records to view, copy normal!

INSERT into [pubdb]. [dbo]. [Pubtab] ([Name],[value]) VALUES (' cc ', 0) goselect * from  [pubdb].[ DBO]. [Pubtab] SELECT * from  [subdb].[ DBO]. [Pubtab]

However, when you add a new table publication, you cannot use snapshot publishing to synchronize! ~

Create test table (Publisher publication database), new table identity field needs to be incremented for REPLICATION

--Create a test table (Publisher publication database) use [pubdb]gocreate table [dbo]. [PUBTAB02] ([ID] [int] IDENTITY (max) not for REPLICATION not null,[name] [varchar] (a) not null,[value] [decimal] (4) Null,constra INT [PK_PUBTAB02] PRIMARY KEY CLUSTERED ([id] ASC),) on [Primary]go

Normal add publication (Publisher publication database)

Snapshot synchronization cannot be enabled at this time and the snapshot is not used.

Right-click the published table to view the structure of the created table (Publisher publication database), followed by the Subscriber database execution.

Use [Subdb]gocreate TABLE [dbo]. [PUBTAB02] ([ID] [int] IDENTITY (4) not null,[name] [varchar] (+) not null,[value] [decimal] (REPLICATION) NULL, Constr AINT [PK_PUBTAB02] PRIMARY KEY CLUSTERED ([ID] ASC) with (Pad_index = off, Statistics_norecompute = off, Ignore_dup_key = O FF, allow_row_locks = on, allow_page_locks = on) on [PRIMARY]) on [Primary]go

OK, add is done, now test add data (Publisher publication database)

Use [Pubdb]goinsert into [dbo]. [PUBTAB02] ([Name],[value]) VALUES (' AA ', goselect) * from  [pubdb].[ DBO]. [PUBTAB02] SELECT * from  [subdb].[ DBO]. [PUBTAB02]

If you add/remove fields, you can do so directly at the Publisher database by adding/removing them as normal.

Use [Pubdb]goalter TABLE [dbo]. [PUBTAB02] ADD insertdate DATETIME nullgoinsert into [dbo]. [PUBTAB02] ([Name],[value],[insertdate]) VALUES (' AA ', 99,getdate ()) goselect * from  [pubdb].[ DBO]. [PUBTAB02] SELECT * from  [subdb].[ DBO]. [PUBTAB02]

Reference:

Initializing a transactional subscription (without using a snapshot)

SQL Server initializes replication through backup files

SQL Server initializes the subscription with a backup