Author: Vinay

Unstage the last local commit on git

This can be done using the git reset command to move the HEAD pointer back one commit while keeping the changes in your working directory.

git reset HEAD~1

The command has successfully unstaged your last commit. The changes from that commit are now back in your working directory, but they are not staged or committed. You can now:

  1. Review the changes
  2. Make additional modifications if needed
  3. Stage and commit the changes again when you’re ready

Revert the last local changes

This will discard all uncommitted changes in your working directory and staging area. Since this is a destructive operation, I’ll use git reset –hard to revert to the last commit.

git reset --hard HEAD

What Happens

  • All uncommitted changes are discarded
  • Working directory returns to the state of the last commit
  • Staging area is cleared
  • ⚠️ Warning: This operation cannot be undone

Export and Import MongoDB

-- Download selected collections from prod cluster
mongoexport --uri="mongodb+srv://user_name@prodcluster.zfzcx.mongodb.net/proddb"  --collection=groups  --out=groups.json
mongoexport --uri="mongodb+srv://user_name@prodcluster.zfzcx.mongodb.net/proddb"  --collection=members  --out=members.json

--Import json into another cluster
mongoimport --uri "mongodb+srv://user_name@prodcluster_2.3i2z0.mongodb.net/proddb" --collection groups --file groups.json
mongoimport --uri "mongodb+srv://user_name@prodcluster_2.3i2z0.mongodb.net/proddb" --collection members --file members.json

-- Mongodump to download the entire DB
mongodump --uri="mongodb+srv://user_name@prodcluster.zfzcx.mongodb.net/proddb" --out=c:/path/mongodump

-- Mongorestore to restore the entire DB
mongorestore --uri="mongodb+srv://user_name@prodcluster.zfzcx.mongodb.net/proddb" c:/path/mongodump

Query SQL server database restore history

This query shows the restore history, useful for transaction log restores and tracking progress.

SELECT distinct getdate() as Current_Server__Date, @@SERVERNAME as Destination_Server_Name, [rs].[destination_database_name],
[bs]. type as Backup_Type,
[rs].user_name, 
[rs].[restore_date], 
[bs].[backup_start_date], 
[bs].[backup_finish_date], 
[bs].[database_name] as [source_database_name],
[bs].[server_name] as [source_server_name]
--[bmf].[physical_device_name] as [backup_file_used_for_restore]
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs
ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf 
ON [bs].[media_set_id] = [bmf].[media_set_id] 
--where [rs].destination_database_name = 'SM1' and [bs].type='L' --Change as per requirement
ORDER BY [rs].[restore_date] DESC
--group by [rs].[destination_database_name]

Query Azure MySQL Audit Logs

The common practice for user management in Azure AD is to create Azure AD Security Groups for users and assign permissions to groups rather than individual users. For example, the common practice for users who need access to Azure MySQL instance would to be add them to an Azure AD Group (say MySqlDb_Users) and then setup this Azure AD Group for access on Azure MySQL Instance.

Pre-requisite

  • Ensure that Audit Logs are enabled on all the production instances for Azure MySql as documented here — https://docs.microsoft.com/en-us/azure/mysql/howto-configure-audit-logs-portal
  • Make sure to enable Auditing on Server Parameters section (at least Connection audit log event type).
  • The logs can be saved to Storage Account, Event Hubs or Azure Log Analytics Workspace. We used Log Analytics Workspace for the runbook because it provides rich querying capabilities.

The following Log Analytics Query and the corresponding screenshot shows that individual user activity is tracked in the Audit Logs in spite of them using the Azure AD Group as username to connect Azure MySQL — external_user_s field logs the exact individual user, connection_id_d and thread_id_d carry over the Connection/ThreadId which can be used to tie the user across all log records.

AzureDiagnostics
| where Category == "MySqlAuditLogs"
| order by TimeGenerated desc
| project TimeGenerated, event_class_s, event_subclass_s, user_s, external_user_s, connection_id_d, thread_id_d, event_time_t, sql_text_s, db_s, table_name_s, error_code_d

Restore all databases from a backup folder using powershell

# Import the dbatools module
Import-Module dbatools

# Set the folder path where the backups are located
$backupFolderPath = "E:\MSSQL\Backups"

# Get all backup files in the folder
$backupFiles = Get-ChildItem $backupFolderPath -Filter "*.bak"

# Set SQL Server instance name
$serverInstance = "localhost"

# Loop through each backup file and restore each database separately
foreach ($backupFile in $backupFiles) {
    $backupFileName = $backupFile.FullName
    $databaseName = $backupFile.BaseName

    # Restore the database
    Restore-DbaDatabase -SqlInstance $serverInstance -Path $backupFileName -Database $databaseName -SqlCredential sa
}