sp_addpublication_snapshot (Transact-SQL)

Applies to: SQL Server Azure SQL Managed Instance

Creates the Snapshot Agent for the specified publication. This stored procedure is executed at the Publisher on the publication database.

Transact-SQL syntax conventions

Syntax

sp_addpublication_snapshot
    [ @publication = ] N'publication'
    [ , [ @frequency_type = ] frequency_type ]
    [ , [ @frequency_interval = ] frequency_interval ]
    [ , [ @frequency_subday = ] frequency_subday ]
    [ , [ @frequency_subday_interval = ] frequency_subday_interval ]
    [ , [ @frequency_relative_interval = ] frequency_relative_interval ]
    [ , [ @frequency_recurrence_factor = ] frequency_recurrence_factor ]
    [ , [ @active_start_date = ] active_start_date ]
    [ , [ @active_end_date = ] active_end_date ]
    [ , [ @active_start_time_of_day = ] active_start_time_of_day ]
    [ , [ @active_end_time_of_day = ] active_end_time_of_day ]
    [ , [ @snapshot_job_name = ] N'snapshot_job_name' ]
    [ , [ @publisher_security_mode = ] publisher_security_mode ]
    [ , [ @publisher_login = ] N'publisher_login' ]
    [ , [ @publisher_password = ] N'publisher_password' ]
    [ , [ @job_login = ] N'job_login' ]
    [ , [ @job_password = ] N'job_password' ]
    [ , [ @publisher = ] N'publisher' ]
    [ , [ @distributor_security_mode = ] distributor_security_mode ]
    [ , [ @distributor_login = ] N'distributor_login' ]
    [ , [ @distributor_password = ] N'distributor_password' ]
[ ; ]

Arguments

[ @publication = ] N'publication'

The name of the publication. @publication is sysname, with no default.

[ @frequency_type = ] frequency_type

The frequency with which the Snapshot Agent is executed. @frequency_type is int, and can be one of the following values.

[ @frequency_interval = ] frequency_interval

The value to apply to the frequency set by @frequency_type. @frequency_interval is int, and can be one of the following values.

[ @frequency_subday = ] frequency_subday

The unit for freq_subday_interval. @frequency_subday is int, and can be one of these values.

[ @frequency_subday_interval = ] frequency_subday_interval

The interval for frequency_subday, in minutes. @frequency_subday_interval is int, with a default of 5.

[ @frequency_relative_interval = ] frequency_relative_interval

The date the Snapshot Agent runs. @frequency_relative_interval is int, with a default of 1.

[ @frequency_recurrence_factor = ] frequency_recurrence_factor

The recurrence factor used by frequency_type. @frequency_recurrence_factor is int, with a default of 0.

[ @active_start_date = ] active_start_date

The date when the Snapshot Agent is first scheduled, formatted as yyyyMMdd. @active_start_date is int, with a default of 0.

[ @active_end_date = ] active_end_date

The date when the Snapshot Agent stops being scheduled, formatted as yyyyMMdd. @active_end_date is int, with a default of 99991231,which means December 31, 9999.

[ @active_start_time_of_day = ] active_start_time_of_day

The time of day when the Snapshot Agent is first scheduled, formatted as HHmmss. @active_start_time_of_day is int, with a default of 0.

[ @active_end_time_of_day = ] active_end_time_of_day

The time of day when the Snapshot Agent stops being scheduled, formatted as HHmmss. @active_end_time_of_day is int, with a default of 235959, which means 11:59:59 P.M. as measured on a 24-hour clock.

[ @snapshot_job_name = ] N'snapshot_job_name'

The name of an existing Snapshot Agent job name if an existing job is being used. @snapshot_job_name is nvarchar(100), with a default of NULL. This parameter is for internal use and shouldn't be specified when creating a new publication. If @snapshot_agent_name is specified, then @job_login and @job_password must be NULL.

[ @publisher_security_mode = ] publisher_security_mode

The the security mode used by the agent when connecting to the Publisher. @publisher_security_mode is int, with a default of 1. A value of 0 must be specified for non-SQL Server Publishers. When possible, use Windows authentication.. The following values define the security mode:

• 0 specifies SQL Server authentication.
• 1 specifies Windows authentication.
• 2 specifies Microsoft Entra password authentication, starting with SQL Server 2022 (16.x) CU 6.
• 3 specifies Microsoft Entra integrated authentication, starting with SQL Server 2022 (16.x) CU 6.
• 4 specifies Microsoft Entra token authentication, starting with SQL Server 2022 (16.x) CU 6.

[ @publisher_login = ] N'publisher_login'

The login used when connecting to the Publisher. @publisher_login is sysname, with a default of NULL. @publisher_login must be specified when @publisher_security_mode is 0. If @publisher_login is NULL and @publisher_security_mode is 1, then the account specified in @job_login is used when connecting to the Publisher.

[ @publisher_password = ] N'publisher_password'

The password used when connecting to the Publisher. @publisher_password is sysname, with a default of NULL.

[ @job_login = ] N'job_login'

The login for the account under which the agent runs. On Azure SQL Managed Instance, use a SQL Server account. @job_login is nvarchar(257), with a default of NULL. This account is always used for agent connections to the Distributor. You must supply this parameter when creating a new Snapshot Agent job.

For non-SQL Server Publishers, this must be the same login specified in sp_adddistpublisher.

[ @job_password = ] N'job_password'

The password for the Windows account under which the agent runs. @job_password is sysname, with no default. You must supply this parameter when creating a new Snapshot Agent job.

[ @publisher = ] N'publisher'

Specifies a non-SQL Server Publisher. @publisher is sysname, with a default of NULL.

@publisher shouldn't be used when creating a Snapshot Agent at a SQL Server Publisher.

[ @distributor_security_mode = ] distributor_security_mode

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

[ @distributor_login = ] N'distributor_login'

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

[ @distributor_password = ] N'distributor_password'

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

Return code values

0 (success) or 1 (failure).

Remarks

sp_addpublication_snapshot is used in snapshot replication, transactional replication, and merge replication.

Examples

-- 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'; 
SET @publication = N'AdvWorksProductTran'; 
-- Windows account used to run the Log Reader and Snapshot Agents.
SET @login = $(Login); 
-- This should be passed at runtime.
SET @password = $(Password); 

-- Enable transactional or 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 use of Windows Integrated Authentication (default) 
    -- when connecting to the Publisher.
    @publisher_security_mode = 1;

-- Create a new transactional publication with the required properties. 
EXEC sp_addpublication 
    @publication = @publication, 
    @status = N'active',
    @allow_push = N'true',
    @allow_pull = N'true',
    @independent_agent = N'true';

-- Create a new snapshot job for the publication, using a default schedule.
EXEC sp_addpublication_snapshot 
    @publication = @publication, 
    @job_login = @login, 
    @job_password = @password,
    -- Explicitly specify the use of Windows Integrated Authentication (default) 
    -- when connecting to the Publisher.
    @publisher_security_mode = 1;
GO

Permissions

Only members of the sysadmin fixed server role or db_owner fixed database role can execute sp_addpublication_snapshot.

Related content

• Create a publication
• Create and Apply the Initial Snapshot
• sp_addpublication (Transact-SQL)
• sp_changepublication_snapshot (Transact-SQL)
• sp_startpublication_snapshot (Transact-SQL)
• Replication stored procedures (Transact-SQL)