This article shows you a script that can be used to monitor the progress of both a Backup and a Restore. It can also be used to just review row counts on all the Universal Database tables.
The first code block is used to populate a SQL Server Temp table with summary data from every table in the database. Below is a description of each field.
- tableName - This is the name of the table.
- numRows - This is the number of rows for each group of records. The following three fields group the row count of a table.
- batchNumber - This indicates the batch number. For example if a table has 10,000 total rows and you're working on an injection. 5,000 could be batch number 0 indicating they haven't started restoring yet, 4,500 could be batch number 10 indicating that they were successfully restored, and 500 might be batch number 1 indicating that there could be an error restoring those records.
- Final_Status - This is the value in the Final_Status column from the table.
- loadStatus - This will indicate 'Loaded' for rows that have a Result_Id and 'Not Loaded' for rows that do not have a Result_Id
- minDate - This is the oldest date/time in the Status_Date_From column of the table for that group of records.
- maxDate - This is the most recent date/time in the STatus_Date_Till column of the table for that group of records.
- USE [{database Name Here }];
- GO
- IF OBJECT_ID('tempdb..#Tbl', 'U') IS NOT NULL
- BEGIN
- -- Temp Table exists, so DROP it
- DROP TABLE #Tbl;
- END
- CREATE TABLE
- #Tbl
- (
- tableName NVARCHAR(100) NOT NULL,
- numRows BIGINT NULL,
- batchNumber BIGINT NULL,
- Final_Status nvarchar(50) NULL,
- loadStatus nvarchar(20) NULL,
- minDate datetime NULL,
- maxDate datetime NULL
- )
- INSERT INTO #Tbl (tableName, batchNumber, Final_Status, loadStatus, minDate, maxDate, numRows)
- EXEC sp_MSforeachtable
- '
- SELECT
- ''?'',
- Batch_Number,
- Final_Status,
- IIF(Result_Id = '''', ''Not Loaded'', ''Loaded''),
- MIN([Status_Date_From]),
- MAX([Status_Date_Till]),
- COALESCE(COUNT([Row]), 0)
- FROM
- ?
- GROUP BY
- Batch_Number,
- Final_Status,
- IIF(Result_Id = '''', ''Not Loaded'', ''Loaded'')
- '
Reviewing the results loaded in the Temp Table
Below are some examples of how you can use the summary data that the previous script loads to the temp table. The queries shown below must be executed in the same Query Window in SQL Server Management Studio (SSMS) because SSMS temp tables have local scope to the query window in which they were created.
- SELECT
- *
- FROM
- #Tbl
- ORDER BY
- tableName,
- loadStatus,
- batchNumber,
- Final_Status
Below are sample results of the query above.
This sample query was run on a database after injecting data. You can see that the data is already loaded. Looking at __M_Contacts you can see that there are 5,368 records that are 'Active', and 1,387 that are 'Archived' and all 6,755 have been loaded to the destination system as indicated by the loadStatus column.
Data Summary
This one is very useful for summarizing the data stored in the project database. This can be saved in your playbook and your notes for later reference.
- SELECT
- CONCAT('Total Rows', ' - ', FORMAT(SUM(numRows),'#,###'))
- FROM
- #Tbl
- WHERE 1=1
- AND tableName !='[dbo].[__M__READ_ME_FIRST]'
- UNION ALL
- SELECT
- CONCAT('Active Users - ', SUM(numRows))
- FROM
- #Tbl
- WHERE 1=1
- AND tableName = '[dbo].[__M_Users]'
- AND Final_Status = 'Active'
- UNION ALL
- SELECT
- CONCAT(tableName, ' - ', FORMAT(SUM(numRows),'#,###'))
- FROM
- #Tbl
- WHERE 1=1
- AND tableName !='[dbo].[__M__READ_ME_FIRST]'
- GROUP BY
- tableName
Progress monitoring
The following query can be used to monitor the progress of injecting data to your destination system.
- SELECT
- tableName,
- SUM(IIF( loadStatus = 'Loaded', numRows,0)) loadedCount,
- SUM(IIF( loadStatus = 'Loaded', 0,numRows)) remainingCount,
- SUM(numRows) totalRows,
- ROUND(CAST(SUM(IIF( loadStatus = 'Loaded', numRows,0)) AS FLOAT) * 100 / SUM(numRows),2) percComplete,
- ROUND(CAST(SUM(IIF( loadStatus = 'Loaded', 0, numRows)) AS FLOAT) * 100 / SUM(numRows),2) percRemaining
- FROM
- #Tbl
- WHERE 1=1
- AND Final_Status !='Deleted'
- GROUP BY
- tableName
Below is a description of each column of output for the above query and a screenshot of example output.
- tableName - The name of the table.
- loadedCount - The number of rows that have a Result_Id, which means they've been injected to the destination system.
- remainingCount - The number of rows that do not have a Result_Id.
- totalRows - The total number of rows on that table that are not in 'Deleted' status.
- percComplete - The percentage of rows that have a Result_Id
- percRemaining - The percentage of rows that do NOT have a Result_Id
More Examples
Active Users
- SELECT
- SUM(numRows)
- FROM
- #Tbl
- WHERE 1=1
- AND Final_Status = 'Active'
- AND tableName = '[dbo].[__M_Users]'
Total Record Count
- SELECT
- SUM(numRows)
- FROM
- #Tbl
Document Count
- SELECT
- SUM(numRows)
- FROM
- #Tbl
- WHERE 1=1
- AND tableName = '[dbo].[__M_DocumentFiles]'
Database Summary
- SELECT
- *
- FROM (
- SELECT
- tableName,
- SUM(numRows) numRows
- FROM
- #Tbl
- GROUP BY
- tableName
- UNION
- SELECT
- '[__Total_Rows_All_Tables]',
- SUM(numRows)
- FROM
- #Tbl
- UNION
- SELECT
- '[__Active Users]',
- SUM(numRows)
- FROM
- #Tbl
- WHERE 1=1
- AND Final_Status = 'Active'
- AND tableName = '[dbo].[__M_Users]'
- ) t
- ORDER BY
- tableName