88 lines
3.3 KiB
Transact-SQL
88 lines
3.3 KiB
Transact-SQL
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
|