SELECT [pos].[POS_hostname], [pos].[POS_MAC_address], [pos].[POS_number], [pos].[POS_type]
FROM [Arizona].[dbo].[Point_of_sale] [pos]
WHERE [pos].[POS_active]=1
AND [pos].[POS_type] IN (1,2)
AND [pos].[POS_number] < 99
ORDER BY [pos].[POS_number]
;

SELECT ActivePos_write.upd.DatabaseVersion() AS currentVersion;

SELECT SettingValue AS backupSrc, SettingId
  FROM ActiveSystemServer.cfg.Settings
 WHERE SettingId LIKE 'Values.Modules.Replication.DbInitializationBackupPath%'
   AND LEN(SettingValue) > 1;

--last backup
SELECT
    JobName = J.name,
    H.*
FROM
    msdb.dbo.sysjobs AS J
    CROSS APPLY (
        SELECT TOP 20
            JobName = J.name,
            StepNumber = T.step_id,
            StepName = T.step_name,
            StepStatus = CASE T.run_status
                WHEN 0 THEN 'Failed'
                WHEN 1 THEN 'Succeeded'
                WHEN 2 THEN 'Retry'
                WHEN 3 THEN 'Canceled'
                ELSE 'Running' END,
            ExecutedAt = msdb.dbo.agent_datetime(T.run_date, T.run_time),
            ExecutingHours = ((T.run_duration/10000 * 3600 + (T.run_duration/100) % 100 * 60 + T.run_duration % 100 + 31 ) / 60) / 60,
            ExecutingMinutes = ((T.run_duration/10000 * 3600 + (T.run_duration/100) % 100 * 60 + T.run_duration % 100 + 31 ) / 60) % 60,
            Message = T.message
            ,t.[instance_id]
        FROM msdb.dbo.sysjobhistory AS T
        WHERE T.job_id = J.job_id
        ORDER BY t.[instance_id] DESC
    ) AS H
WHERE [J].[name]='D91030 - Backup ActivePos_Read'
AND [H].[StepNumber] = 0
ORDER BY J.name

RETURN

--start backup
EXEC msdb.dbo.sp_start_job @job_name = N'D91030 - Backup ActivePos_Read' , @step_name = 'Purge old ActivePos_Read backups'

WAITFOR DELAY '00:00:05.000'

WHILE EXISTS(
    SELECT sj.name
       , sja.*
    FROM msdb.dbo.sysjobactivity AS sja
    INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id
    WHERE sj.[name]='D91030 - Backup ActivePos_Read'
       AND sja.start_execution_date IS NOT NULL
       AND sja.stop_execution_date IS NULL
) BEGIN
    --PRINT 'job is still running '+CONVERT(VARCHAR(20), CURRENT_TIMESTAMP, 120);
    DECLARE @t VARCHAR(20) = CONVERT(VARCHAR(20), CURRENT_TIMESTAMP, 120);
    RAISERROR('%s, job is still running', 0, 1, @t) WITH NOWAIT;
    WAITFOR DELAY '00:00:05.000'
END 

--check POS and PHCY versions
SELECT ActivePos_write.upd.DatabaseVersion() AS currentVersion
EXEC ('SELECT ActivePos_write.upd.DatabaseVersion()') AT xxx

--force restore after manual copy
EXEC ('exec ActivePos_write.dbo.InitializeActivePosReadFromBackup @BackUpFileWithPath = ''c:\temp\ActivePos_read.22.1.223.2401.bak''') AT xxx

--to start on the pos to force a restore of the backup. adapt version, mac and UNC before running
exec ActiveSystemClient.dbo.DBAReplPosSubscription 
	              @mac = '9C-7B-EF-43-5A-98', 
	              @serverDbVersion = '23.2.23.19501', 
	              @preferedReplinibackup= '\\cvi247aps-replinibackup.coop-vitality.ch\replinibackup\ActivePos_read.23.2.23.19501.bak'


--restart service on pos
EXEC ('EXEC xp_cmdshell ''net stop ActiveposClientService'';EXEC xp_cmdshell ''net start ActiveposClientService''') AT xxx

--subscription
EXEC ActivePos_read..sp_dropsubscription @publication = N'ActivePosTran', @article = N'all', @subscriber ='xxx'
EXEC ActiveSystemServer.dbo.RepairReplication