Commands / executables used for MSSQL on EC2 backups and restores

Commands used in heartbeats

Powershell file location	Description	Powershell Commands used	TSQL commands used
hcm/hcmcommon/powershell/install_mssql_ec2_binaries.ps1	Script to install binaries/excutables on host	Get-Service Get-CimInstance Remove-CimInstance (used only if CJtracker is present) [System.Environment]: :SetEnvironmentVariableCopy-S3ObjectRemove-Item (only in binaries folder) new-service (to register CJTracker service) Start-Service	NA
hcm/hcmcommon/powershell/upgrade_mssql_ec2_binaries.ps1	Script to upgrade binaries on host	same commands as above	NA
hcm/hcmcommon/powershell/copy_host_key.ps1	Used to copy host encryption to host	Copy-S3Object	NA
lib/mssql/common_powershell_script/check_sys_admin_permission.ps1	Used to verify pre-req on windows host	[System.Security.Principal.WindowsIdentity]: :GetCurrent().NameGet-Servicevssadmin list writers	select IS_SRVROLEMEMBER
lib/mssql/common_powershell_script/mssql_ec2_normal_heartbeat.ps1	Normal heartbeat script	Get-Service	NA
lib/mssql/common_powershell_script/mssql_ec2_system_hb.ps1	System heartbeat script	Get-WmiObject Get-NetAdapter Get-CimInstance	NA

Commands used for inventory

PowerShell file location	Description	Powershell Commands used	TSQL commands used
inventory/invmssql/invagentmssql/powershell/get_all_services_v2.ps1	Script to get all SQL server service on ec2-instance	Get-Service	NA
inventory/invmssql/invagentmssql/powershell/instance_details_using_sql.ps1	Script to get SQL server instance data for inventory	Invoke-SQLCMD	All are select queries, all we need is select access SELECT * from sys.databases SELECT * FROM sys.availability_databases_cluster SELECT * FROM sys.master_files SELECT * FROM sys.dm_hadr_availability_group_states SELECT * FROM sys.availability_groups SELECT * FROM sys.availability_replicas SELECT * FROM sys.dm_hadr_availability_group_states
inventory/invmssql/invagentmssql/powershell/ag_db_details.ps1	Script to get ag database details		All are select queries, need select access in master DB same as above

Commands used for backup and restore

PowerShell file location	Description	Powershell Commands used	TSQL commands used
dpmssql/dpmssqlagent/Powershell/backuplog.ps1	Script for log backup	Invoke-SQLCMD	backup log
dpmssql/dpmssqlagent/Powershell/get_backup_history.ps1	Script to get db backup history	Invoke-SQLCMD	SELECT Top 1 backup_start_date, backup_finish_date, first_lsn, last_lsn, database_backup_lsn, time_zone, recovery_model,checkpoint_lsn, last_recovery_fork_guid FROM msdb.dbo.backupset
dpmssql/dpmssqlagent/Powershell/get_db_file_types.ps1	Script to get file type of database	Invoke-SQLCMD	SELECT type, type_desc, sys.master_files.name, physical_name FROM sys.master_files INNER JOIN sys.databases ON sys.databases.database_id = sys.master_files.database_id
dpmssql/dpmssqlagent/Powershell/recover_database.ps1	Script to recover the database	Invoke-SQLCMD	restore database
dpmssql/dpmssqlagent/Powershell/restorelog.ps1	script to recover log backup file	Invoke-SQLCMD	restore log
dpmssql/cjtracker/Powershell/get_cjtracker_stats.ps1	Script used by cj tracker for stats	Read process stats

Run Custom command allowed commands

PowerShell Commands(CommandType = 1):

1: "ls"  
2: "vssadmin list writers"  
3: "Get-Service -DisplayName \"SQL Server (*\""  
4: "vssadmin list providers"  
5: "Get-Service"  
6: "vssadmin list shadows"  
7: "(Get-ChildItem $Env:CLUMIO_INSTALL_DIR\\logs).Count"  
8: "Get-EventLog -LogName 'Application' -Source 'VSS' -Before (Get-Date) -After ((Get-Date).AddDays(-1)) | Format-List"  
9: "Get-EventLog -LogName 'Application' -Source 'VSS' -Before (Get-Date) -After ((Get-Date).AddHours(-1)) | Format-List"  
10: "Get-EventLog -LogName 'Application' -Source 'VSS' -Before (Get-Date) -After ((Get-Date).AddHours(-3)) | Format-List"  
11: "Get-EventLog -LogName 'Application' -Source 'MSSQLSERVER' -Before (Get-Date) -After ((Get-Date).AddHours(-3)) | Format-List"  
12: "Get-EventLog -LogName 'System' -Before (Get-Date) -After ((Get-Date).AddDays(-1)) | Format-List"  
13: "Get-EventLog -LogName 'System' -Before (Get-Date) -After ((Get-Date).AddHours(-3)) | Format-List"  
14: "Get-EventLog -LogName 'Application' -Before (Get-Date) -After ((Get-Date).AddDays(-1)) | Format-List"  
15: "Get-EventLog -LogName 'Application' -Before (Get-Date) -After ((Get-Date).AddHours(-3)) | Format-List"  
16: "Get-Counter \"\\Process(sqlservr)\\% Processor Time\" -SampleInterval 30"  
17: "Get-Counter \"\\Process(sqlservr)\\IO Read Bytes/sec\" -SampleInterval 30"  
18: "Get-Counter \"\\Process(sqlservr)\\IO Write Bytes/sec\" -SampleInterval 30"  
19: "Get-Counter \"\\Process(sqlservr)\\IO Read Operations/sec\" -SampleInterval 30"  
20: "Get-Counter \"\\Process(sqlservr)\\IO Write Operations/sec\" -SampleInterval 30"  
21: "Get-Counter \"\\Process(_total)\\% Processor Time\" -SampleInterval 30"  
22: "Get-Counter \"\\Process(idle)\\% Processor Time\" -SampleInterval 30"  
23: "Get-WmiObject -Class Win32_Processor | Select-Object -Property * "  
24: "fsutil usn readjournal", // Pass arguments = ['C:', 'startusn=0', 'csv']  
25: "winsat disk"  
26: "Get-WmiObject Win32_Product" // used to get all installed application on windows host  
27: "type $ENV:ALLUSERSPROFILE\\Amazon\\SSM\\Logs\\amazon-ssm-agent.log" // used to get info logs of ssm  
28: "type $ENV:ALLUSERSPROFILE\\Amazon\\SSM\\Logs\\errors.log" // used to get error logs of ssm  
29: "Restart-Service AmazonSSMAgent"  
30: "fsutil usn"  
31: "Get-Process"  
32: "type $ENV:ALLUSERSPROFILE\\Amazon\\SSM\\Logs\\ssm-agent-worker.log"  
33: "type $ENV:ALLUSERSPROFILE\\Amazon\\SSM\\Logs\\ssm-document-worker.log"  
34: "type $ENV:ALLUSERSPROFILE\\Amazon\\SSM\\Logs\\ssm-session-worker.log"  
35: "type $ENV:ALLUSERSPROFILE\\Amazon\\SSM\\Logs\\hibernate.log"  
36: "chkdsk" // only used to get state of the disk

TSQL commands(CommandType = 2)

1: "select * from sys.databases"  
2: "select * from sys.availability_groups"  
3: "select * from sys.availability_replicas"  
4: "select * from sys.availability_databases_cluster"  
5: "select * from sys.dm_hadr_availability_group_states"  
6: "select @@version"  
7: "select * from sys.master_files"  
8: "select * from msdb.dbo.backupset"

Executables used for mssql on ec2

Clumio Executables	Short description	Executable location relative to binary path
cjtracker.exe	Used for tracking changed blocked in mdf and ldf files	%binary_path%/mssql/dp/cjtracker.exe
dpmssqlcloudagent.exe	Responsible for taking mssql server instance snapshot	%binary_path%/mssql/dp/dpmssqlcloudagent.exe
uploader.exe	Responsible to upload chunks from host to S3	%binary_path%/mssql/dp/uploader.exe
restoreagent.exe	Responsible for restoring database	%binary_path%/mssql/dp/restoreagent.exe
winhostutil.exe	Responsible for inventory sync files upload and log collection	%binary_path%/hcm/winhostutil.exe

Contact [email protected] with any questions or clarifications.