Delen via

  • Artikel

How to: Create the Initial Snapshot (Replication Transact-SQL Programming)

Initial snapshots can be programmatically created either by creating and running a Snapshot Agent job or by running the Snapshot Agent executable file from a batch file. After an initial snapshot has been generated, it is transferred to and applied at the Subscriber when the subscription is first synchronized. If you run the Snapshot Agent from a command prompt or a batch file, you will need to rerun the agent whenever the existing snapshot becomes invalid.

Merge publications that use parameterized filters require a two-part snapshot. For more information, see How to: Create a Snapshot for a Merge Publication with Parameterized Filters (Replication Transact-SQL Programming).

Security noteSecurity Note

When possible, prompt users to enter security credentials at runtime. If you must store credentials in a script file, you must secure the file to prevent unauthorized access.

To create and run a Snapshot Agent job to generate the initial snapshot

  1. Create a snapshot, transactional, or merge publication. For more information, see How to: Create a Publication (Replication Transact-SQL Programming).

  2. Execute sp_addpublication_snapshot (Transact-SQL). Specify @publication and the following parameters:

    • The @job_login, which specifies the Windows Authentication credentials under which the Snapshot Agent runs at the Distributor.

    • **The @job_password**, which is the password for the supplied Windows credentials.

    • (Optional) A value of 0 for @publisher_security_mode if the agent will use SQL Server Authentication when connecting to the Publisher. In this case, you must also specify the SQL Server Authentication login information for @publisher_login and @publisher_password.

    • (Optional) A synchronization schedule for the Snapshot Agent job. For more information, see How to: Specify Synchronization Schedules (Replication Transact-SQL Programming).

    Security noteSecurity Note

     When configuring a Publisher with a remote Distributor, the values supplied for all parameters, including job_login and job_password, are sent to the Distributor as plain text. You should encrypt the connection between the Publisher and its remote Distributor before executing this stored procedure. For more information, see Encrypting Connections to SQL Server.

  3. Add articles to the publication. For more information, see How to: Define an Article (Replication Transact-SQL Programming).

  4. At the Publisher on the publication database, execute sp_startpublication_snapshot (Transact-SQL), specifying the value of @publication from step 1.

To run the Snapshot Agent to generate the initial snapshot

  1. Create a snapshot, transactional, or merge publication. For more information, see How to: Create a Publication (Replication Transact-SQL Programming).

  2. Add articles to the publication. For more information, see How to: Define an Article (Replication Transact-SQL Programming).

  3. From the command prompt or in a batch file, start the Replication Snapshot Agent by running snapshot.exe, specifying the following command-line arguments:

    • -Publication

    • -Publisher

    • -Distributor

    • -PublisherDB

    • -ReplicationType

    If you are using SQL Server Authentication, you must also specify the following arguments:

    • -DistributorLogin

    • -DistributorPassword

    • -DistributorSecurityMode = 0

    • -PublisherLogin

    • -PublisherPassword

    • -PublisherSecurityMode = 0

Example

This example shows how to create a transactional publication and add a Snapshot Agent job for the new publication (using sqlcmd scripting variables). The example also starts the job.

-- To avoid storing the login and password in the script file, the values 
-- are passed into SQLCMD as scripting variables. For information about 
-- how to use scripting variables on the command line and in SQL Server
-- Management Studio, see the "Executing Replication Scripts" section in
-- the topic "Programming Replication Using System Stored Procedures".

DECLARE @publicationDB AS sysname;
DECLARE @publication AS sysname;
DECLARE @login AS sysname;
DECLARE @password AS sysname;
SET @publicationDB = N'AdventureWorks2008R2'; --publication database
SET @publication = N'AdvWorksCustomerTran'; -- transactional publication name
SET @login = $(Login);
SET @password = $(Password);

USE [AdventureWorks2008R2]

-- Enable transactional and snapshot replication on the publication database.
EXEC sp_replicationdboption 
  @dbname = @publicationDB, 
  @optname = N'publish',
  @value = N'true';

-- Execute sp_addlogreader_agent to create the agent job. 
EXEC sp_addlogreader_agent 
  @job_login = @login, 
  @job_password = @password,
  -- Explicitly specify the security mode used when connecting to the Publisher.
  @publisher_security_mode = 1;

-- Create new transactional publication, using the defaults. 
USE [AdventureWorks2008R2]
EXEC sp_addpublication 
  @publication = @publication, 
  @description = N'transactional publication';

-- Create a new snapshot job for the publication, using the defaults.
EXEC sp_addpublication_snapshot 
  @publication = @publication,
  @job_login = @login,
  @job_password = @password;

-- Start the Snapshot Agent job.
EXEC sp_startpublication_snapshot @publication = @publication;
GO

This example creates a merge publication and adds a Snapshot Agent job (using sqlcmd variables) for the publication. This example also starts the job.

-- To avoid storing the login and password in the script file, the value 
-- is passed into SQLCMD as a scripting variable. For information about 
-- how to use scripting variables on the command line and in SQL Server
-- Management Studio, see the "Executing Replication Scripts" section in
-- the topic "Programming Replication Using System Stored Procedures".

DECLARE @publicationDB AS sysname;
DECLARE @publication AS sysname;
DECLARE @login AS sysname;
DECLARE @password AS sysname;
SET @publicationDB = N'AdventureWorks2008R2'; 
SET @publication = N'AdvWorksSalesOrdersMerge'; 
SET @login = $(Login);
SET @password = $(Password);

-- Enable merge replication on the publication database.
USE master
EXEC sp_replicationdboption 
  @dbname = @publicationDB, 
  @optname=N'merge publish',
  @value = N'true';

-- Create new merge publication, using the defaults. 
USE [AdventureWorks2008R2]
EXEC sp_addmergepublication 
  @publication = @publication, 
  @description = N'Merge publication.';

-- Create a new snapshot job for the publication, using the defaults.
EXEC sp_addpublication_snapshot 
  @publication = @publication,
  @job_login = @login,
  @job_password = @password;

-- Start the Snapshot Agent job.
EXEC sp_startpublication_snapshot @publication = @publication;
GO

The following command-line arguments start the Snapshot Agent to generate the snapshot for a merge publication.

Note

Line breaks were added to improve readability. In a batch file, commands must be made in a single line.

REM -- Declare variables
SET Publisher=%InstanceName%
SET PublicationDB=AdventureWorks2008R2 
SET Publication=AdvWorksSalesOrdersMerge 

REM --Start the Snapshot Agent to generate the snapshot for AdvWorksSalesOrdersMerge.
"C:\Program Files\Microsoft SQL Server\100\COM\SNAPSHOT.EXE" -Publication %Publication% 
-Publisher %Publisher% -Distributor %Publisher% -PublisherDB %PublicationDB% 
-ReplicationType 2 -OutputVerboseLevel 1 -DistributorSecurityMode 1