Share via

Create and Apply the Initial Snapshot

  • Article

This topic describes how to create and apply the initial snapshot in SQL Server 2014 by using SQL Server Management Studio, Transact-SQL, or Replication Management Objects (RMO). Merge publications that use parameterized filters require a two-part snapshot. For more information, see Create a Snapshot for a Merge Publication with Parameterized Filters.

In This Topic

Using SQL Server Management Studio

By default, if SQL Server Agent is running, a snapshot is generated by the Snapshot Agent immediately after a publication is created with the New Publication Wizard. By default, it is then applied by the Distribution Agent (for snapshot and transactional replication) or Merge Agent (for merge subscriptions) for all subscriptions. A snapshot can also be generated using SQL Server Management Studio and Replication Monitor. For information about starting Replication Monitor, see Start the Replication Monitor.

To create a snapshot in Management Studio

  1. Connect to the Publisher in Management Studio, and then expand the server node.

  2. Expand the Replication folder, and then expand the Local Publications folder.

  3. Right-click the publication for which you want to create a snapshot, and then click View Snapshot Agent Status.

  4. In the View Snapshot Agent Status - <Publication> dialog box, click Start.

When the Snapshot Agent finishes generating the snapshot, a message will be displayed, such as "[100%] A snapshot of 17 article(s) was generated."

To create a snapshot in Replication Monitor

  1. In Replication Monitor, expand a Publisher group in the left pane, and then expand a Publisher.

  2. Right-click the publication for which you want to generate a snapshot, and then click Generate Snapshot.

  3. To view the status of the Snapshot Agent, click the Agents tab. For more detailed information, right-click the Snapshot Agent in the grid, and then click View Details.

To apply a snapshot

  1. After the snapshot is generated, it is applied by synchronizing the subscription with the Distribution Agent or the Merge Agent:

    • If the agent is set to run continuously (the default for transactional replication), the snapshot is automatically applied after it is generated.

    • If the agent is set to run on a schedule, the snapshot is applied the next time the agent is scheduled to run.

    • If the agent is set to run on demand, it is applied the next time you run the agent.

    For more information about synchronizing subscriptions, see Synchronize a Push Subscription and Synchronize a Pull Subscription.

Using Transact-SQL

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.

Important

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 Create a Publication.

  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 Specify Synchronization Schedules.

    Important

    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 Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager).

  3. Add articles to the publication. For more information, see Define an Article.

  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 Create a Publication.

  2. Add articles to the publication. For more information, see Define an Article.

  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

Examples (Transact-SQL)

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

Note

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

@ECHO OFF
SET InstanceName=%computername%
REM<snippetstartmergesnapshot_10>
REM -- Declare variables
SET Publisher=%InstanceName%
SET PublicationDB=AdventureWorks2012 
SET Publication=AdvWorksSalesOrdersMerge 

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

PAUSE

Using Replication Management Objects (RMO)

The Snapshot Agent generates snapshots after a publication is created. You can generate these snapshots programmatically by using Replication Management Objects (RMO) and direct managed code access to replication agent functionalities. The objects you use depend on the type of replication. The Snapshot Agent can be started synchronously using the SnapshotGenerationAgent object or asynchronously using the agent job. After the initial snapshot has been generated, it is transferred to and applied at the Subscriber when the subscription is first synchronized. You will need to rerun the agent whenever the existing snapshot no longer contains valid, up-to-date data. For more information, see Maintain Publications.

Important

When possible, prompt users to enter security credentials at runtime. If you must store credentials, use the cryptographic services provided by the Microsoft Windows .NET Framework.

To generate the initial snapshot for a snapshot or transactional publication by starting the Snapshot Agent job (asynchronous)

  1. Create a connection to the Publisher by using the ServerConnection class.

  2. Create an instance of the TransPublication class. Set the Name and DatabaseName properties for the publication, and set the ConnectionContext property to the connection created in step 1.

  3. Call the LoadProperties method to load the remaining properties of the object. If this method returns false, either the publication properties in step 2 were defined incorrectly or the publication does not exist.

  4. If the value of SnapshotAgentExists is false, call CreateSnapshotAgent to create the snapshot agent job for this publication.

  5. Call the StartSnapshotGenerationAgentJob method to start the agent job that generates the snapshot for this publication.

  6. (Optional) When the value of SnapshotAvailable is true, the snapshot is available to Subscribers.

To generate the initial snapshot for a snapshot or transactional publication by running the Snapshot Agent (synchronous)

  1. Create an instance of the SnapshotGenerationAgent class, and set the following required properties:

  2. Set a value of Transactional or Snapshot for ReplicationType.

  3. Call the GenerateSnapshot method.

To generate the initial snapshot for a merge publication by starting the Snapshot Agent job (asynchronous)

  1. Create a connection to the Publisher by using the ServerConnection class.

  2. Create an instance of the MergePublication class. Set the Name and DatabaseName properties for the publication, and set the ConnectionContext property to the connection created in step 1.

  3. Call the LoadProperties method to load the remaining properties of the object. If this method returns false, either the publication properties in step 2 were defined incorrectly or the publication does not exist.

  4. If the value of SnapshotAgentExists is false, call CreateSnapshotAgent to create the snapshot agent job for this publication.

  5. Call the StartSnapshotGenerationAgentJob method to start the agent job that generates the snapshot for this publication.

  6. (Optional) When the value of SnapshotAvailable is true, the snapshot is available to Subscribers.

To generate the initial snapshot for a merge publication by running the Snapshot Agent (synchronous)

  1. Create an instance of the SnapshotGenerationAgent class, and set the following required properties:

  2. Set a value of Merge for ReplicationType.

  3. Call the GenerateSnapshot method.

Examples (RMO)

This example synchronously runs the Snapshot Agent to generate the initial snapshot for a transactional publication.

// Set the Publisher, publication database, and publication names.
string publicationName = "AdvWorksProductTran";
string publicationDbName = "AdventureWorks2012";
string publisherName = publisherInstance;
string distributorName = publisherInstance;

SnapshotGenerationAgent agent;

try
{
    // Set the required properties for Snapshot Agent.
    agent = new SnapshotGenerationAgent();
    agent.Distributor = distributorName;
    agent.DistributorSecurityMode = SecurityMode.Integrated;
    agent.Publisher = publisherName;
    agent.PublisherSecurityMode = SecurityMode.Integrated;
    agent.Publication = publicationName;
    agent.PublisherDatabase = publicationDbName;
    agent.ReplicationType = ReplicationType.Transactional;

    // Start the agent synchronously.
    agent.GenerateSnapshot();

}
catch (Exception ex)
{
    // Implement custom application error handling here.
    throw new ApplicationException(String.Format(
        "A snapshot could not be generated for the {0} publication."
        , publicationName), ex);
}

' Set the Publisher, publication database, and publication names.
Dim publicationName As String = "AdvWorksProductTran"
Dim publicationDbName As String = "AdventureWorks2012"
Dim publisherName As String = publisherInstance
Dim distributorName As String = publisherInstance

Dim agent As SnapshotGenerationAgent

Try
    ' Set the required properties for Snapshot Agent.
    agent = New SnapshotGenerationAgent()
    agent.Distributor = distributorName
    agent.DistributorSecurityMode = SecurityMode.Integrated
    agent.Publisher = publisherName
    agent.PublisherSecurityMode = SecurityMode.Integrated
    agent.Publication = publicationName
    agent.PublisherDatabase = publicationDbName
    agent.ReplicationType = ReplicationType.Transactional

    ' Start the agent synchronously.
    agent.GenerateSnapshot()

Catch ex As Exception
    ' Implement custom application error handling here.
    Throw New ApplicationException(String.Format( _
     "A snapshot could not be generated for the {0} publication." _
     , publicationName), ex)
End Try

This example asynchronously starts the agent job to generate the initial snapshot for a transactional publication.

// Set the Publisher, publication database, and publication names.
string publicationName = "AdvWorksProductTran";
string publicationDbName = "AdventureWorks2012";
string publisherName = publisherInstance;

TransPublication publication;

// Create a connection to the Publisher using Windows Authentication.
ServerConnection conn;
conn = new ServerConnection(publisherName);

try
{
    // Connect to the Publisher.
    conn.Connect();

    // Set the required properties for an existing publication.
    publication = new TransPublication();
    publication.ConnectionContext = conn;
    publication.Name = publicationName;
    publication.DatabaseName = publicationDbName;

    if (publication.LoadProperties())
    {
        // Start the Snapshot Agent job for the publication.
        publication.StartSnapshotGenerationAgentJob();
    }
    else
    {
        throw new ApplicationException(String.Format(
            "The {0} publication does not exist.", publicationName));
    }
}
catch (Exception ex)
{
    // Implement custom application error handling here.
    throw new ApplicationException(String.Format(
        "A snapshot could not be generated for the {0} publication."
        , publicationName), ex);
}
finally
{
    conn.Disconnect();
}

' Set the Publisher, publication database, and publication names.
Dim publicationName As String = "AdvWorksProductTran"
Dim publicationDbName As String = "AdventureWorks2012"
Dim publisherName As String = publisherInstance

Dim publication As TransPublication

' Create a connection to the Publisher using Windows Authentication.
Dim conn As ServerConnection
conn = New ServerConnection(publisherName)

Try
    ' Connect to the Publisher.
    conn.Connect()

    ' Set the required properties for an existing publication.
    publication = New TransPublication()
    publication.ConnectionContext = conn
    publication.Name = publicationName
    publication.DatabaseName = publicationDbName

    If publication.LoadProperties() Then
        ' Start the Snapshot Agent job for the publication.
        publication.StartSnapshotGenerationAgentJob()
    Else
        Throw New ApplicationException(String.Format( _
         "The {0} publication does not exist.", publicationName))
    End If
Catch ex As Exception
    ' Implement custom application error handling here.
    Throw New ApplicationException(String.Format( _
     "A snapshot could not be generated for the {0} publication." _
     , publicationName), ex)
Finally
    conn.Disconnect()
End Try

See Also

Create a Publication
Create a Pull Subscription
Create a Push Subscription
Specify Synchronization Schedules
Create and Apply the Snapshot
Initialize a Subscription with a Snapshot
Replication Management Objects Concepts
Replication Security Best Practices
Replication System Stored Procedures Concepts
Use sqlcmd with Scripting Variables