sp_adddistributiondb (T-SQL)

Creates a new distribution database and installs the Distributor schema. The distribution database stores procedures for distribution and distribution history.

Syntax

sp_adddistributiondb [@database =] 'database'
    [,[@data_folder =] 'data_folder'] [,[@data_file =] 'data_file']
    [,[@data_file_size =] data_file_size] [,[@log_folder =] 'log_folder']
    [,[@log_file =] 'log_file'] [,[@log_file_size =] log_file_size]
    [,[@min_distretention =] min_distretention]
    [,[@max_distretention =] max_distretention]
    [,[@history_retention =] history_retention]
    [,[@security_mode =] security_mode] [,[@login =] 'login']
    [,[@password =] 'password']
    [,[@createmode =] createmode]

Arguments

[@database =] 'database'

Is the database name. database is sysname, with no default.

[@data_folder =] 'data_folder'

Is the name of the directory used to store the database files. data_folder is nvarchar(255), with a default of NULL. If NULL, the data directory for that instance of Microsoft® SQL Server™ is used, for example, '\Mssql7\Data'.

[@data_file =] 'data_file'

Is the name of the database file. data_file is nvarchar(255), with a default of database. If NULL, the stored procedure constructs the new file name using the database name.

[@data_file_size =] data_file_size

Is the initial data file size in megabytes (MB). data_file_size is int, with a default of 2 MB.

[@log_folder =] 'log_folder'

Is the name of the directory for the database log file. log_folder is nvarchar(255), with a default of NULL. If NULL, the data directory for that instance of Microsoft® SQL Server™ is used, for example, '\Mssql7\Data'.

[@log_file =] 'log_file'

Is the name of the log file. log_file is nvarchar(255), with a default of NULL. If NULL, the stored procedure constructs the new file name using the database name.

[@log_file_size =] log_file_size

Is the initial log file size in MB. log_file_size is int, with a default of 0 MB, which means the file size is 512 KB, the smallest allowed.

[@min_distretention =] min_distretention

Is the minimum retention period, in hours, before transactions are deleted. min_distretention is int, with a default of 0 hours.

[@max_distretention =] max_distretention

Is the maximum retention period, in hours, before transactions are deleted. max_distretention is int, with a default of 72 hours. Subscriptions that are inactive beyond this period are automatically deactivated. RAISERROR 21011 is issued for each deactivated subscription.

[@history_retention =] history_retention

Is the number of hours to retain history. history_retention is int, with a default of 48 hours.

[@security_mode =] security_mode

Is the security mode implemented by the Distributor. security_mode is int, with a default of 0. 0 specifies SQL Server Authentication. 1 specifies Windows NT Authentication.

[@login =] 'login'

Is the login name used when connecting to the Distributor. This is required if security_mode is set to 0. login is sysname, with a default of sa.

[@password =] 'password'

Is the password used when connecting to the Distributor. This is required if security_mode is set to 0. password is sysname, with a default of NULL.

[@createmode =] createmode

createmode is int, with a default of 0, and can be one of these values.

 

Value	Description
0 (default)	Use CREATE DATABASE for attach.
1	CREATE DATABASE or use existing but no attach.
2	For internal use only.

Return Code Values

0 (success) or 1 (failure)

Remarks

sp_adddistributiondb is used in all types of replication.

Permissions

Only members of the sysadmin fixed server role can execute sp_adddistributiondb.

See Also