Grant sysadmin permissions to windows login when SQL Server SA login password is not known

Here are the detailed steps:

1: Start SQL Server in Single User Mode Open SQL Server Configuration Manager.

 
Stop the SQL Server Instance you need to recover

                                      |  
Right-click on the SQL Server Instance and select Properties.

                                      |  
Click on the Advanced tab, and add -m or -f; to the beginning of Startup parameters.

                                      |  
Click OK and start the instance.

2: Add an existing login or a newly created one to the sysadmin server role

After the SQL Server Instance starts in single-user mode, the Windows Administrator account is able to connect to SQL Server using the sqlcmd utility using Windows authentication. You can use Transact-SQL commands such as "sp_addsrvrolemember" to add an existing login or a newly created one to the sysadmin server role.


Open an elevated command prompt and enter the command:

SQLCMD -S myServer\instanceName

Replace myServer\instanceName with the name of the computer and the instance of SQL Server that you want to connect to.
At the next prompts, enter the following commands to create windows login for sysadmin role:

CREATE LOGIN [US\TEST] FROM WINDOWS
go
ALTER SERVER ROLE sysadmin ADD MEMBER [US\TEST] 

(Grant sysadmin role for newly created login or existing login)

go
quit

 
Stop the SQL Server instance.

                          |  
Remove the -m option from the Start parameters field, 

                          |

and then start the SQL Server service.


At this point you should be able to login to SQL Server with sysadmin privileges and reset the SA login password.

SQL Server Services stopped after applying SQL Server Cumulative/Security update

 SQL Server Services stopped after applying SQL Server Cumulative/Security update due to following error:

1. Cannot find the user 'ModuleSigner', because it does not exist or you do not have permission.
2. Cannot find the login '##MS_SSISServerCleanupJobLogin##', because it does not exist or you do not have permission.

Note: Above error is one of the example.  Analyst may receive similar error for other missing security objects.

 

Whenever we have such upgrade script (SQL Server Cumulative\Security updates) failure issue and SQL Server instance is not getting started,

To find the root cause of the issue, go to the SQL Server installation Error location

Example: C:\Program Files\Microsoft SQL Server\150\Setup Bootstrap\Log\

If you found below error

1. Cannot find the user 'ModuleSigner', because it does not exist or you do not have permission.
2. Cannot find the login '##MS_SSISServerCleanupJobLogin##', because it does not exist or you do not have permission.

We need to use trace flag 902 to start SQL which would bypass script upgrade mode. This would allow us the find the cause and fix it. So, here are the steps to fix missing user 'ModuleSigner' and login '##MS_SSISServerCleanupJobLogin##'

SQL instance needs to be rebooted and after that SQL Server Service does not start. 

As I mentioned earlier, first we started SQL with trace flag 902. I started SQL using trace flag 902 as below via command prompt.

Step 1: NET START MSSQLSERVER /T902

For named instance, we need to use below (replace instance name based on your environment)

NET START MSSQL$INSTANCENAME /T902

Step 2: As soon as SQL Server was started, you will able to connect because the upgrade installation didn’t run. Here is the T-SQL script which creates missing logins '##MS_SSISServerCleanupJobLogin##' and users 'ModuleSigner'.

USE [SSISDB]

CREATE USER [ModuleSigner] FOR CERTIFICATE [MS_SQLISSigningCertificate]

GO

USE [master]  
GO  
CREATE LOGIN [##MS_SSISServerCleanupJobLogin##] WITH PASSWORD='Pa$$w0rd', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF  
GO  
GRANT VIEW SERVER STATE TO ##MS_SSISServerCleanupJobLogin##
GO
USE [SSISDB]  
GO
CREATE USER [##MS_SSISServerCleanupJobUser##] FOR LOGIN [##MS_SSISServerCleanupJobLogin##] WITH DEFAULT_SCHEMA=[dbo]
GO

Step 3: After creating the login\user, stopped SQL Service using SQL Server Configuration Manager. We can also do it via command prompt using below command. Below is for the default instance.

NET STOP MSSQLSERVER

If you are dealing with named instance, then below is the command ((replace InstanceName based on your environment)

NET START MSSQL$INSTANCENAME

Step 4: Then start SQL normally (without trace flag) using SQL Server Configuration Manager.

NET START MSSQLSERVER

Step 5: Verify the SQL instance and install the latest Cumulative\Security update for the instance

Refresh database from PROD to Dev server in existing Alway-on configuration automatically

 

PowerShell Script to copy from PROD server to Dev03 and from Dev03(AG Primary) to Dev04(AG secondary) server. 

You can schedule a job in task scheduler to automatic copy file. 

# Delete all existing backup from below location

Remove-Item D:\ProdBackup\*.bak

Write-Output "Deleted all old existing backup from Dev03 server : TASK COMPLETED"

# Source and Destination backup file location

$_sourcePath ="\\ABC-PROD01\e$\CopyOnlyBackup"

$_destinationPath = "D:\ProdBackup";

# Pick latest .bak file and Copying file Des to Source and rename

$Datetime =Get-Date

Write-Output ("PROD Latest backup file copying started at " + $Datetime + " ...COPY TASK RUNNING....." )

@(Get-ChildItem $_sourcePath -Filter *.bak | Sort LastWriteTime -Descending)[0] | % { Copy-Item -path $_.FullName -destination $("$_destinationPath\testDB.bak") -force} 

$Datetime1 =Get-Date

Write-Output ("Latest backup file copy Completed on Dev03 server and renamed database file name at " + $Datetime1 + "...COPY TASK COMPLETED..." )

################# Copy backup From Dev03 to Dev04 ######################

# Delete all existing backup from below location

Remove-Item \\ABC-DEV04\d$\ProdBackup\*.bak

Write-Output "Deleted all old existing backup from Dev04 server : TASK COMPLETED"

# Source and Destination backup file location

$_sourcePath ="D:\ProdBackup"

$_destinationPath = "\\ABC-DEV04\d$\ProdBackup";

# Pick latest .bak file and Copying file Des to Source and rename

$Datetime =Get-Date

Write-Output ("PROD Latest backup file copying started at " + $Datetime + " ...COPY TASK RUNNING....." )

@(Get-ChildItem $_sourcePath -Filter *.bak | Sort LastWriteTime -Descending)[0] | % { Copy-Item -path $_.FullName -destination $("$_destinationPath\testDB.bak") -force} 

$Datetime1 =Get-Date

Write-Output ("Latest backup file copy Completed on Dev server and renamed database file name at " + $Datetime1 + "...COPY TASK COMPLETED..." )

Once Copy done by above Script. 

Create below job on AG primary server Dev03. 

Note: This will contain total 6 step to Restore DB on Primary and Secondary server and will configure AG between Dev03 and and Dev04 automatically.

USE [msdb]

GO

/****** Object:  Job [Monthly_Test_DB_Refresh_From_PROD]    Script Date: 7/6/2021 7:45:32 AM ******/

EXEC msdb.dbo.sp_delete_job @job_id=N'8c70a241-323d-48e5-80ce-f97190c07661', @delete_unused_schedule=1

GO

/****** Object:  Job [Monthly_Test_DB_Refresh_From_PROD]    Script Date: 7/6/2021 7:45:32 AM ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 7/6/2021 7:45:32 AM ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Monthly_Test_DB_Refresh_From_PROD', 

@enabled=1, 

@notify_level_eventlog=0, 

@notify_level_email=0, 

@notify_level_netsend=0, 

@notify_level_page=0, 

@delete_level=0, 

@description=N'No description available.', 

@category_name=N'[Uncategorized (Local)]', 

@owner_login_name=N'sa', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [1-Remove AG from Dev Primary]    Script Date: 7/6/2021 7:45:32 AM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'1-Remove AG from Dev Primary', 

@step_id=1, 

@cmdexec_success_code=0, 

@on_success_action=4, 

@on_success_step_id=2, 

@on_fail_action=2, 

@on_fail_step_id=0, 

@retry_attempts=0, 

@retry_interval=0, 

@os_run_priority=0, @subsystem=N'CmdExec', 

@command=N'sqlcmd -S ABC-DEV03 -i D:\ProdBackup\AG_Refresh_SQL_Scripts\RemoveDBFromAGgroup.sql

', 

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [2-DeleteDBOnSecondary]    Script Date: 7/6/2021 7:45:32 AM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'2-DeleteDBOnSecondary', 

@step_id=2, 

@cmdexec_success_code=0, 

@on_success_action=4, 

@on_success_step_id=3, 

@on_fail_action=2, 

@on_fail_step_id=0, 

@retry_attempts=0, 

@retry_interval=0, 

@os_run_priority=0, @subsystem=N'CmdExec', 

@command=N'sqlcmd -S ABC-DEV04 -i D:\ProdBackup\AG_Refresh_SQL_Scripts\DeleteDBOnSecondary.sql', 

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [3-DeleteDBOnPrimary]    Script Date: 7/6/2021 7:45:32 AM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'3-DeleteDBOnPrimary', 

@step_id=3, 

@cmdexec_success_code=0, 

@on_success_action=4, 

@on_success_step_id=4, 

@on_fail_action=2, 

@on_fail_step_id=0, 

@retry_attempts=0, 

@retry_interval=0, 

@os_run_priority=0, @subsystem=N'CmdExec', 

@command=N'sqlcmd -S ABC-DEV03 -i D:\ProdBackup\AG_Refresh_SQL_Scripts\DeleteDBOnPrimary.sql', 

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [4-RestoreDBOnPrimary]    Script Date: 7/6/2021 7:45:32 AM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'4-RestoreDBOnPrimary', 

@step_id=4, 

@cmdexec_success_code=0, 

@on_success_action=4, 

@on_success_step_id=5, 

@on_fail_action=2, 

@on_fail_step_id=0, 

@retry_attempts=0, 

@retry_interval=0, 

@os_run_priority=0, @subsystem=N'CmdExec', 

@command=N'sqlcmd -S ABC-DEV03 -i D:\ProdBackup\AG_Refresh_SQL_Scripts\RestoreDBOnPrimary.sql', 

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [5-RestoreDBOnSecondary]    Script Date: 7/6/2021 7:45:32 AM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'5-RestoreDBOnSecondary', 

@step_id=5, 

@cmdexec_success_code=0, 

@on_success_action=4, 

@on_success_step_id=6, 

@on_fail_action=2, 

@on_fail_step_id=0, 

@retry_attempts=0, 

@retry_interval=0, 

@os_run_priority=0, @subsystem=N'CmdExec', 

@command=N'sqlcmd -S ABC-DEV03 -i D:\ProdBackup\AG_Refresh_SQL_Scripts\RestoreDBOnSecondary.sql', 

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [6- Add DB in AG]    Script Date: 7/6/2021 7:45:32 AM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'6- Add DB in AG', 

@step_id=6, 

@cmdexec_success_code=0, 

@on_success_action=1, 

@on_success_step_id=0, 

@on_fail_action=2, 

@on_fail_step_id=0, 

@retry_attempts=0, 

@retry_interval=0, 

@os_run_priority=0, @subsystem=N'CmdExec', 

@command=N'sqlcmd -S ABC-DEV03 -i D:\ProdBackup\AG_Refresh_SQL_Scripts\AddDBInAG.sql', 

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

GO

Now the below SQL script of code divided in 6 step for your above created SQL Job.

Save each steps of code on Dev03(AG primary server) at D:\ProdBackup\AG_Refresh_SQL_Scripts\*.sql format

Note: File name refer from created job steps.

All 6 *.sql file will access by above job.

Note: Before/while saving the .sql file go to Query --> and SQLCMD option.

-----------File name 1 -Remove DB from AG group-------------------------

:connect ABC-DEV03

USE [master]

GO

/****** Object:  AvailabilityDatabase testdb    Script Date: 7/1/2021 2:45:39 AM ******/

ALTER AVAILABILITY GROUP [ABC-DV-AWO]

REMOVE DATABASE testdb;

GO

-----------File name 2 Delete DB on Secondary ------------------------------

:connect ABC-DEV04

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'testdb '

GO

USE [master]

GO

/****** Object:  Database testdb    Script Date: 7/1/2021 2:48:58 AM ******/

DROP DATABASE testdb

GO

----------File name 3 Delete DB on Primary------------------------------

:connect ABC-DEV03

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'testdb '

GO

use testdb

GO

use [master]

GO

USE [master]

GO

ALTER DATABASE testdb SET  SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

GO

USE [master]

GO

/****** Object:  Database testdb    Script Date: 7/1/2021 2:51:08 AM ******/

DROP DATABASE testdb

GO

-------File name 4 Restore DB on Secondary -------------------------------

:connect ABC-DEV04

RESTORE DATABASE TestDB 

FROM DISK = N'D:\ProdBackup\testdb.bak' 

WITH FILE = 1,  

     MOVE N'testdb' TO N'E:\Data\testdb.mdf',  

     MOVE N'testdb_log' TO N'L:\DBLogs\testdb.ldf',

NORECOVERY, 

     NOUNLOAD, REPLACE, STATS = 5

-------File name 5 Restore DB on Primary-------------------------------

:connect ABC-DEV03

RESTORE DATABASE TestDB 

FROM DISK = N'D:\ProdBackup\testdb.bak' 

WITH FILE = 1,  

     MOVE N'testdb' TO N'E:\Data\testdb.mdf',  

     MOVE N'testdb_log' TO N'L:\DBLogs\testdb.ldf',

     NOUNLOAD, REPLACE, STATS = 50

---------------File name 6 - add DB in AG -----------------------

--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.

:Connect ABC-DEV03

USE [master]

GO

ALTER AVAILABILITY GROUP [ABC-DV-AWO]

MODIFY REPLICA ON N'ABC-DEV04' WITH (SEEDING_MODE = MANUAL)

GO

USE [master]

GO

ALTER AVAILABILITY GROUP [ABC-DV-AWO]

ADD DATABASE testdb;

GO

:Connect ABC-DEV04

-- Wait for the replica to start communicating

begin try

declare @conn bit

declare @count int

declare @replica_id uniqueidentifier 

declare @group_id uniqueidentifier

set @conn = 0

set @count = 30 -- wait for 5 minutes 

if (serverproperty('IsHadrEnabled') = 1)

and (isnull((select member_state from master.sys.dm_hadr_cluster_members where upper(member_name COLLATE Latin1_General_CI_AS) = upper(cast(serverproperty('ComputerNamePhysicalNetBIOS') as nvarchar(256)) COLLATE Latin1_General_CI_AS)), 0) <> 0)

and (isnull((select state from master.sys.database_mirroring_endpoints), 1) = 0)

begin

    select @group_id = ags.group_id from master.sys.availability_groups as ags where name = N'ABC-DV-AWO'

select @replica_id = replicas.replica_id from 

master.sys.availability_replicas as replicas 

where upper(replicas.replica_server_name COLLATE Latin1_General_CI_AS) = upper(@@SERVERNAME COLLATE Latin1_General_CI_AS) and group_id = @group_id

while @conn <> 1 and @count > 0

begin

set @conn = isnull((select connected_state from

master.sys.dm_hadr_availability_replica_states as states where states.replica_id = @replica_id), 1)

if @conn = 1

begin

-- exit loop when the replica is connected, or if the query cannot find the replica status

break

end

waitfor delay '00:00:10'

set @count = @count - 1

end

end

end try

begin catch

-- If the wait loop fails, do not stop execution of the alter database statement

end catch

ALTER DATABASE testdb SET HADR AVAILABILITY GROUP = [ABC-DV-AWO];

GO

GO

How to open SSIS package from SQL Server - Integration Services catalog

Open "Visual Studio" on your <ENTAD00110155> server (SSIS Server). 

Go to File --> New Project -->  select "Integration services" --> 

Select "Integration Services Import Project wizard" -->  Next --> 

Select "Integration Services catalog" --> Browse (Server name) --> 

Select <ENTAD00110155> server to connect --> OK --> 

Click browse (To select Package)  --> SSISDB--> Select Folder-->

Select DTSX  --> Next --> Import --> Close --> Go to View --> 

Solution explorer -->  Open "SQL_Restore.dtsx". 

Now you can see the SSIS package workflow and tasks!!

How to use Robocopy for transferring data

robocopy H:\Resource \\WN0001101\h$\Backupfrom2008 /e /sec /LOG:H:\Copy_Log.txt


robocopy H:\Resource J:\Destination /e /sec /LOG:H:\Copy_Log.txt



robocopy H:\Resource  \\WN0001101\h$\Backupfrom2008 /mir /sec /LOG:H:\Copy_Log.txt

How to Add new Disk on Azure Windows server and make visible.

I created a new VM1 using the new Azure portal (Resource manager?) and attached a new drive (4 GB) to the vm1. and when I RDP to VM1, I can't see the new drive. I deleted the drive and added one of 20gb as I think that there may be a 20gb limit on drives for A0. Still nothing.
Using this Microsoft link I fixed the issue: 

https://docs.microsoft.com/en-us/azure/virtual-machines/windows/attach-managed-disk-portal?toc=%2Fazure%2Fvirtual-machines%2Fwindows%2Fclassic%2Ftoc.json

How to add a data disk

1. Go to the Azure portal to add a data disk. Search for and select Virtual machines.
2. Select a virtual machine from the list.
3. On the Virtual machine page, select Disks.
4. On the Disks page, select Add data disk.
5. In the drop-down for the new disk, select Create disk.
6. In the Create managed disk page, type in a name for the disk and adjust the other settings as necessary. When you're done, select Create.
7. In the Disks page, select Save to save the new disk configuration for the VM.
8. After Azure creates the disk and attaches it to the virtual machine, the new disk is listed in the virtual machine's disk settings under Data disks.

How to Initialize a new data disk

1. Connect to the VM.
2. Select the Windows Start menu inside the running VM and enter diskmgmt.msc in the search box. The Disk Management console opens.
3. Disk Management recognizes that you have a new, uninitialized disk and the Initialize Disk window appears.
4. Verify the new disk is selected and then select OK to initialize it.
5. The new disk appears as unallocated. Right-click anywhere on the disk and select New simple volume. The New Simple Volume Wizard window opens.
6. Proceed through the wizard, keeping all of the defaults, and when you're done select Finish.
7. Close Disk Management.
8. A pop-up window appears notifying you that you need to format the new disk before you can use it. Select Format disk.
9. In the Format new disk window, check the settings, and then select Start.
10. A warning appears notifying you that formatting the disks erases all of the data. Select OK.
11. When the formatting is complete, select OK.

Next steps

• You can also attach a data disk by using PowerShell.

Add new Article to existing Transnational Replication using Script

Add Article to existing Publication

To add the new (articles/SP/Views/Functions) to Transactional replication,  Don't not use GUI to add articles (including Tables, views, SP’s and Functions), always use sp_addarticle and sp_addsubscription like following scripts.

                   

Make sure that your publication has IMMEDIATE_SYNC and ALLOW_ANONYMOUS properties set to FALSE or 0.

use JAIVERMA_DB

select immediate_sync , allow_anonymous from syspublications

If either of them is TRUE then modify that to FALSE by using the following command

EXEC sp_changepublication

@publication = My-Replication',

@property = 'allow_anonymous' ,

@value = 'false'

GO

EXEC sp_changepublication

@publication = My-Replication',

s@property = 'immediate_sync' ,

@value = 'false'

Adding the Tables to Replication -

1)    Add the article to the publication

use JAIVERMA_DB

use [JAIVERMA_DB]

exec sp_addarticle @publication = N’MY-Replication’, @article = N'New_Table_name', @source_object = N'New_Table_name', @force_invalidate_snapshot=1

GO

2)     Add the subscription for this new article

use [JAIVERMA_DB]

exec sp_addsubscription @publication = N’MY-Replication’, @subscriber = N’SECONDARYSERVERNAME’, @destination_db = N'JAIVERMA_DB', @article = N'New_Table_name',  @reserved='Internal', @subscription_type = N'Pull'

GO

use [JAIVERMA_DB]

exec sp_addsubscription @publication = N’MY-Replication’, @subscriber = N'GPC-INISQDB06', @destination_db = N'JAIVERMA_DB', @article = N' New_Table_name',  @reserved='Internal', @subscription_type = N'Pull'

GO

Adding the SP to Replication -

1)      Adding the SP to publication

use [JAIVERMA_DB]

exec sp_addarticle @publication = N’MY-Replication’, @article = N'New_SP_Name', @source_object = N'New_SP_name', @type = N'proc schema only', @destination_table = N'New_SP_name',  @force_invalidate_snapshot=1

GO

2)      Add the subscription for this new SP

use [JAIVERMA_DB]

exec sp_addsubscription @publication = N’MY-Replication’, @subscriber = N’SECONDARYSERVERNAME’, @destination_db = N'JAIVERMA_DB', @article = N' New_SP_Name'',  @reserved='Internal', @subscription_type = N'Pull'

GO

Adding the View to existing replication

1)      Adding the View to publication

use [JAIVERMA_DB]

exec sp_addarticle @publication = N’MY-Replication’,

@article = N'New_View_name',

@source_object = N'New_View_name',

@type = N'view schema only',

@destination_table = N'New_View_name',

@force_invalidate_snapshot=1

GO

2)      Add the subscription for this new view

use [JAIVERMA_DB]

exec sp_addsubscription @publication = N’MY-Replication’, @subscriber = N’SECONDARYSERVERNAME’, @destination_db = N'JAIVERMA_DB', @article = N'New_View_name', @reserved='Internal', @subscription_type = N'Pull'

GO

Adding the User Defined Functions to existing replication

1)      Adding the User Defined Functions to publication

use [JAIVERMA_DB]

exec sp_addarticle @publication = N’MY-Replication’,

@article = N'New_Function_name',

@source_object = N'New_Function_name',

@type = N'func schema only',

@destination_table = N'New_Function_name',

@force_invalidate_snapshot=1

GO

2)      Add the subscription for this new Function

use [JAIVERMA_DB]

exec sp_addsubscription @publication = N’MY-Replication’, @subscriber = N’SECONDARYSERVERNAME’, @destination_db = N'JAIVERMA_DB', @article = N'New_Function_name',  @reserved='Internal', @subscription_type = N'Pull'

GO

3)    Lastly start the SNAPSHOT AGENT job from the job activity monitor. Verify that the snapshot was generated for only newly added articles .