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.

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.

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.

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

SELECT 
        SUM(numRows)
FROM 
        #Tbl 
WHERE 1=1
        AND tableName = '[dbo].[__M_DocumentFiles]'