Objective:
This simple stored procedure generates the initialise from backup script text
Procedure:
use monitoringdb
go
Create procedure spGenerateInitialzeFromBackup
@publicationName nvarchar (50),
@destinationdb nvarchar (50)
as
/*
This simple stored procedure generates the initialise from backup script text,
*/
declare @sql nvarchar (4000)
select @sql = '
EXEC SP_CHANGEPUBLICATION @PUBLICATION = '''+@publicationName+''', --publication name
@PROPERTY = ''allow_initialize_from_backup'', --property
@value = ''true'' --set the value to true
go
exec sp_addsubscription
@publication = '''+@publicationName+''',
@article = ''All'',
@subscriber = ''ServerName'',
@destination_db = '''+@destinationdb+''',
@sync_type = ''initialize with backup'',
@backupdevicetype = ''disk'',
@backupdevicename = ''F:\backup\dbnameInitialize.bak''
go
--Then add the push subscription agent to synchronise a push subscription
exec sp_addpushsubscription_agent @publication = '''+@publicationName+''',
@subscriber = ''ServerName'',
@subscriber_db = '''+@destinationdb+''',
@subscriber_security_mode = 0,
@subscriber_login = ''replicationuser'',
@subscriber_password = ''hereisthepassword''
go'
print @Sql
Reference: http://msdn.microsoft.com/en-us/library/ms147897.aspx
