1. Clumio
  2. Knowledge Base
  3. Administration and Security

Articles in this section

See more

Commands / executables used for MSSQL on EC2 backups and restores

Shree Kumar Bakthavatsalam
  • Updated

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]::SetEnvironmentVariable

  • Copy-S3Object

  • Remove-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().Name

  • Get-Service

  • vssadmin 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

Related articles

Comments

0 comments

Please sign in to leave a comment.