Hopefully Simple for Experts (Difficult for Me) - Compare to Prior Day

I want to write a query/view against this table with calculated columns:

  • [Size Difference from Prior Snapshot - Bytes]
  • [Size Difference from Prior Snapshot - Percent]

The Prior Snapshot is the prior record when the list is ordered by LogDateTime (you could order by LogId, LogDateTime or FileName, and get the same result)

This is the table (in SQL Server 2017 / 14.0.2037.2):

LogId LogDateTime FileName Size
1 1 2023-06-23 16:41:39.310 2023_06_23_05_40_01_Snapshot_Main_Production.zip 128302768
2 2 2023-06-24 05:40:17.903 2023_06_24_05_40_01_Snapshot_Main_Production.zip 90519637
3 3 2023-06-25 05:40:21.690 2023_06_25_05_40_01_Snapshot_Main_Production.zip 110933251
4 4 2023-06-26 05:40:20.273 2023_06_26_05_40_01_Snapshot_Main_Production.zip 91654658
5 5 2023-06-27 05:40:25.453 2023_06_27_05_40_01_Snapshot_Main_Production.zip 110334977
6 6 2023-06-28 05:40:22.883 2023_06_28_05_40_01_Snapshot_Main_Production.zip 109150472
7 7 2023-06-29 05:40:22.717 2023_06_29_05_40_01_Snapshot_Main_Production.zip 109364919
8 8 2023-06-30 05:40:22.153 2023_06_30_05_40_01_Snapshot_Main_Production.zip 109968814

And the DDL to create that table and insert records:

CREATE TABLE [tbl_SnapshotSize](
	[LogId] [int] IDENTITY(1,1) NOT NULL,
	[LogDateTime] [datetime] NOT NULL,
	[FileName] [nvarchar](1000) NULL,
	[Size] [bigint] NULL
SET IDENTITY_INSERT [tbl_SnapshotSize] ON 
INSERT [tbl_SnapshotSize] ([LogId], [LogDateTime], [FileName], [Size]) VALUES (1, CAST(N'2023-06-23T16:41:39.310' AS DateTime), N'2023_06_23_05_40_01_Snapshot_Main_Production.zip', 128302768)
INSERT [tbl_SnapshotSize] ([LogId], [LogDateTime], [FileName], [Size]) VALUES (2, CAST(N'2023-06-24T05:40:17.903' AS DateTime), N'2023_06_24_05_40_01_Snapshot_Main_Production.zip', 90519637)
INSERT [tbl_SnapshotSize] ([LogId], [LogDateTime], [FileName], [Size]) VALUES (3, CAST(N'2023-06-25T05:40:21.690' AS DateTime), N'2023_06_25_05_40_01_Snapshot_Main_Production.zip', 110933251)
INSERT [tbl_SnapshotSize] ([LogId], [LogDateTime], [FileName], [Size]) VALUES (4, CAST(N'2023-06-26T05:40:20.273' AS DateTime), N'2023_06_26_05_40_01_Snapshot_Main_Production.zip', 91654658)
INSERT [tbl_SnapshotSize] ([LogId], [LogDateTime], [FileName], [Size]) VALUES (5, CAST(N'2023-06-27T05:40:25.453' AS DateTime), N'2023_06_27_05_40_01_Snapshot_Main_Production.zip', 110334977)
INSERT [tbl_SnapshotSize] ([LogId], [LogDateTime], [FileName], [Size]) VALUES (6, CAST(N'2023-06-28T05:40:22.883' AS DateTime), N'2023_06_28_05_40_01_Snapshot_Main_Production.zip', 109150472)
INSERT [tbl_SnapshotSize] ([LogId], [LogDateTime], [FileName], [Size]) VALUES (7, CAST(N'2023-06-29T05:40:22.717' AS DateTime), N'2023_06_29_05_40_01_Snapshot_Main_Production.zip', 109364919)
INSERT [tbl_SnapshotSize] ([LogId], [LogDateTime], [FileName], [Size]) VALUES (8, CAST(N'2023-06-30T05:40:22.153' AS DateTime), N'2023_06_30_05_40_01_Snapshot_Main_Production.zip', 109968814)
ALTER TABLE [tbl_SnapshotSize] ADD  CONSTRAINT [DF_tbl_SnapShot_LogDateTime]  DEFAULT (getdate()) FOR [LogDateTime]

Full background: These records are inserted by a PowerShell script that downloads cloud app backups to our LAN. We need to make sure those backup sizes change daily (the backup process fails sometimes, and doesn't notify or alert anyone, and the PowerShell keeps downloading the same stale backup file again and again until someone notices, usually at the worst time).

I plan to query this view, and if the "size difference - percent" value is zero, that means the backup failed, so an email alert will be sent to the service desk and app administrators.

I also plan to send alerts if the "size difference - percent" is greater than ABS(.2), which could also be a red flag for different reasons.

,[Size Difference from Prior Snapshot - Bytes]=size-LAG(Size,1) OVER (ORDER BY LogDateTime)
,[Size Difference from Prior Snapshot - Percent]=size*$100/LAG(Size,1) OVER (ORDER BY LogDateTime)
FROM [tbl_SnapshotSize]
ORDER BY LogDateTime

If you can modify the PowerShell download process, you can probably avoid downloading the same file multiple times by using the cloud copy utilities.

Azcopy for Azure:

AWS CLI (S3 command):


Both have a sync option, that will avoid downloading files that already exist in the destination folder. Azcopy can also avoid downloading files that are too old, or too recent, you can pass the effective dates.