Category: Power Shell Script
-
Powershell Script to Drop Logins across multile servers.
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null $Error.Clear() cls $servers = Get-Content E\LoginNames\Servers.txt $logins = @(login name to be dropped) foreach($server in $servers) { $srv = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) $server #drop database users foreach($database in $srv.Databases) { foreach($login in $logins) { if($database.Users.Contains($login)) { $database.Users[$login].Drop(); } } } #drop server logins foreach($login in $logins) { if ($srv.Logins.Contains($login)) { $srv.Logins[$login].Drop(); } } } $FilePath = “E:\LoginNames\” $OutFile = Join-Path -path $FilePath -childPath (“logins_” + (get-date).toString(‘yyyyMMdd_hhmmtt’) + “.log”)
//
-
Get SQL Server version information installed on multiple servers
$start = get-date write-host “Start: ” $start [reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”) | out-null ## Specify path of output file $FilePath = “D:\Version\” $OutFile = Join-Path -path $FilePath -childPath (“SQLVersions_” + (get-date).toString(‘yyyyMMdd_hhmmtt’) + “.log”) ## Output file name will be like “X:\Monitoring_Automation\ SQLVersions_20130906_1248AM.log” # Below is loop to generate version of each server @(foreach ($svr in get-content “D:\Version\Monitoring_servers.txt”) { $s = New-Object “Microsoft.SqlServer.Management.Smo.Server” $svr $s | select Name, Version }) | export-csv -noType $OutFile $end = get-date write-host “End: ” $end
//
-
DBCC IND
The DBCC IND statement takes three parameters like this: DBCC IND(0,’dbo.SalesOrderDetailList’,1); First parameter is the database id. If you pass in 0 here, the current database is used. Second parameter is the table name in quotes. You can also pass in the object_id of the table instead of the quoted name like this: DBCC IND(0,1797581442,1); Third parameter is the index_id. There is an optional fourth parameter that…
//
-
Read a big file using PowerShell
Get-Content C:\MySQL_Dump_8April2022\agii-tdsqlpw01_08April_11PM.sql | Select-Object -skip 0 -first 100
//
-
AOG on Azure Blob with a different region SQL Server instance
Hello there, everyone! I hope everything is going well for you. Today, I’ll show you AOG on Azure Blob with different region SQL Server Instance. Ø Created SQL disk layout and formatted with 64K- · Disk E: SQL Binaries · Disk T: TempDB · Disk R: User Databases · Disk L: User DB transaction logs Ø SQL Server Memory Configuration as…
//
-
Read a big file using PowerShell
# Command-Format: Get-Content ‘your-file-path’ -Tail <number-of-line>Get-Content ‘.\mylargefile.txt’ -Tail 5000 # Get-Content D:\MyBigFeedFile.txt | Select-Object -skip 123798766555672 -first 6
//