다음을 통해 공유

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. For more information about creating snapshots for merge publications with parameterized filters, see How to: Create a Snapshot for a Merge Publication with Parameterized Filters (Replication Transact-SQL Programming).

ms147352.security(ko-kr,SQL.90).gif보안 정보:
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).
    ms147352.security(ko-kr,SQL.90).gif보안 정보:
     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 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

예

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'AdventureWorks'; --publication database
SET @publication = N'AdvWorksCustomerTran'; -- transactional publication name
SET @login = $(Login);
SET @password = $(Password);

USE [AdventureWorks]

-- 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 [AdventureWorks]
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'AdventureWorks'; 
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 [AdventureWorks]
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.

[!참고] 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=AdventureWorks 
SET Publication=AdvWorksSalesOrdersMerge 

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

참고 항목

작업

How to: Create a Snapshot for a Merge Publication with Parameterized Filters (Replication Transact-SQL Programming)
How to: Create the Initial Snapshot (RMO Programming)

개념

Programming Replication Using System Stored Procedures

관련 자료

스냅숏 만들기 및 적용
스크립팅 변수와 함께 sqlcmd 사용

도움말 및 정보

SQL Server 2005 지원 받기