powershell Archives - sherbaz.com

Most important task when you join a new organization or when you start working for a new client is to gain access to all required AD groups. I had to switch several times and this was a pain point. My manager would share a list of AD groups and ask me to check if my account is added to all those groups across all domains. Hence being lazy, I had to make use of below simple script to fetch and compare lists quickly at each domain.

##*------------------------------------------------------------------------------------------------------------------------------------------
#  Filename       : groups.ps1
#  Purpose        : Verify if all AD groups are added for a domain user account
#  Schedule       : NONE
#  Date           : 05-March-2018
#  Author         : www.sherbaz.com/Sherbaz Mohamed
#  Version        : 1
#  OS             : Windows Server 2012
#
#  Important --arks:	
#  INPUT          : Username, File path (for AD groups)
#  VARIABLE       : NONE
#  PARENT         : NONE
#  CHILD          : NONE
#  NOTE           : Arguments are mandatory. The input text file for argument 2 should contain the list of AD groups.
#---------------------------------------------------------------------------------------------------------------------------------------------
# Usage:
# .\groups.ps1 -username sherbaz -groupsfile .\groups.txt
#
##*-------------------------------------------------------------------------------------------------------------------------------------------*/

Param(
    [Parameter(Mandatory=$True,Position=1)]
    [string]$username,
    [Parameter(Mandatory=$True)]
    [string]$groupsfile
)


$memberships = dsquery user -name $username | dsget user -memberof

"Below groups were missing"

foreach($group in Get-Content $groupsfile)
{
    $found = $memberships | where {$_ -Match "$group"}
    if($found -eq $null) {"Not Found: $group"}
}

##*------------------------------------------------------------------------------------------------------------------------------------------

# Filename : groups.ps1

# Purpose : Verify if all AD groups are added for a domain user account

# Schedule : NONE

# Date : 05-March-2018

# Author : www.sherbaz.com/Sherbaz Mohamed

# Version : 1

# OS : Windows Server 2012

# Important --arks:

# INPUT : Username, File path (for AD groups)

# VARIABLE : NONE

# PARENT : NONE

# CHILD : NONE

# NOTE : Arguments are mandatory. The input text file for argument 2 should contain the list of AD groups.

#---------------------------------------------------------------------------------------------------------------------------------------------

# Usage:

# .\groups.ps1 -username sherbaz -groupsfile .\groups.txt

##*-------------------------------------------------------------------------------------------------------------------------------------------*/

Param(

[Parameter(Mandatory=$True,Position=1)]

[string]$username,

[Parameter(Mandatory=$True)]

[string]$groupsfile

)

$memberships = dsquery user -name $username | dsget user -memberof

"Below groups were missing"

foreach($group in Get-Content $groupsfile)

{

$found = $memberships | where {$_ -Match "$group"}

if($found -eq $null) {"Not Found: $group"}

}

Store the script in a file and name it for example “groups.ps1”. Copy-paste all AD group names required for your job role into a separate text file named groups.txt in the same folder. Open a powershell prompt, Navigate into the directory and execute below command.

.\groups.ps1 -username sherbaz -groupsfile .\groups.txt

1	.\groups.ps1 -username sherbaz -groupsfile .\groups.txt

You could further customize the script based on your requirement.

Below script helps restore several databases on to a SQL Server instance in one go. We just have to place all the .bak files in a folder and execute the script. before running the script, its required to import SQLPS in the powershell prompt as below.

Import-Module SQLPS -DisableNameChecking

1	Import-Module SQLPS -DisableNameChecking

Save below script as restore_all.ps1

#*----------------------------------------------------------------------------------------------------------------------------
#  Filename       : mssql_AutoRestoreMultipleDatabasesInOneGo.ps1
#  Purpose        : Script to restore all databases from a backup folder on to a SQL Server.
#  Schedule       : Ad-Hoc
#  Date           : 25-September-2014
#  Author         : www.sherbaz.com/Sherbaz Mohamed
#  Version        : 1
#
#  Important --arks:	
#  INPUT          : $path = Backup folder, $sqlserver = Destination SQL Server instance name, $datafolder = datafilelocation, $logfolder = logfilelocation
#  VARIABLE       : NONE
#  PARENT         : NONE
#  CHILD          : NONE
#  NOTE           : The database path will be retrieved from SQL Server database settings
#---------------------------------------------------------------------------------------------------------------------------*/
# Usage:
# ./mssql_AutoRestoreMultipleDatabasesInOneGo.ps1 "E:\database_Backup_Migration\" "sqlserver" "destinationdatafolderpath" "destinationlogfolderpath"
# 
param($path, $sqlserver, $datafolder, $logfolder)

foreach($bkpfile in Get-ChildItem $path "*.bak" | Select-Object basename)
{
	$bkpfile = $bkpfile.BaseName

	$server = New-Object 	Microsoft.SqlServer.Management.Smo.Server($sqlserver)

	$restore = New-Object 	Microsoft.SqlServer.Management.Smo.Restore

	$restore.Devices.AddDevice($path+'\'+$bkpfile+'.bak', 
 	 [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
  
	$header = $restore.ReadBackupHeader($server)

	if($header.Rows.Count -eq 1)
	{
 	 $dbname = $header.Rows[0]["DatabaseName"]
	}



	# .\001_restore.ps1 . $path"\"$bkpfile".bak" $dbname

	# param($sqlserver, $bkfilepath, $dbname)
		$bkfilepath = $path + "\"+ $bkpfile + ".bak"

# Connect to the specified instance

$srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $sqlserver

 

# Get the default file and log locations

# (If DefaultFile and DefaultLog are empty, use the MasterDBPath and MasterDBLogPath values)

if(!$datafolder)
{
	$fileloc = $srv.Settings.DefaultFile
}
else { $fileloc = $datafolder}

if(!$logfolder)
{
	$logloc = $logloc = $srv.Settings.DefaultLog
}
else { $logloc = $logfolder}


if ($fileloc.Length -eq 0) {

    $fileloc = $srv.Information.MasterDBPath

    }

if ($logloc.Length -eq 0) {

    $logloc = $srv.Information.MasterDBLogPath

    }

 

# Identify the backup file to use, and the name of the database copy to create

$bckfile = $bkfilepath

$dbname = $dbname

 

# Build the physical file names for the database copy
if($fileloc -eq $logloc)
{

	$dbfile = $fileloc + '\Data\'+ $dbname + '_Data.mdf'

	$logfile = $logloc + '\Log\'+ $dbname + '_Log.ldf'
}
else
{
	$dbfile = $fileloc + '\'+ $dbname + '_Data.mdf'

	$logfile = $logloc + '\'+ $dbname + '_Log.ldf'
}

 

# Use the backup file name to create the backup device

$bdi = new-object ('Microsoft.SqlServer.Management.Smo.BackupDeviceItem') ($bckfile, 'File')

 

# Create the new restore object, set the database name and add the backup device

$rs = new-object('Microsoft.SqlServer.Management.Smo.Restore')

$rs.Database = $dbname

$rs.Devices.Add($bdi)

 

# Get the file list info from the backup file

$fl = $rs.ReadFileList($srv)

$rfl = @()

foreach ($fil in $fl) {

    $rsfile = new-object('Microsoft.SqlServer.Management.Smo.RelocateFile')

    $rsfile.LogicalFileName = $fil.LogicalName

    if ($fil.Type -eq 'D') {

        $rsfile.PhysicalFileName = $dbfile

        }

    else {

        $rsfile.PhysicalFileName = $logfile

        }

    $rfl += $rsfile

    }

 

# Restore the database

Restore-SqlDatabase -ServerInstance $sqlserver -Database $dbname -BackupFile $bkfilepath -RelocateFile $rfl -NoRecovery
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

#*----------------------------------------------------------------------------------------------------------------------------

# Filename : mssql_AutoRestoreMultipleDatabasesInOneGo.ps1

# Purpose : Script to restore all databases from a backup folder on to a SQL Server.

# Schedule : Ad-Hoc

# Date : 25-September-2014

# Author : www.sherbaz.com/Sherbaz Mohamed

# Version : 1

# Important --arks:

# INPUT : $path = Backup folder, $sqlserver = Destination SQL Server instance name, $datafolder = datafilelocation, $logfolder = logfilelocation

# VARIABLE : NONE

# PARENT : NONE

# CHILD : NONE

# NOTE : The database path will be retrieved from SQL Server database settings

#---------------------------------------------------------------------------------------------------------------------------*/

# Usage:

# ./mssql_AutoRestoreMultipleDatabasesInOneGo.ps1 "E:\database_Backup_Migration\" "sqlserver" "destinationdatafolderpath" "destinationlogfolderpath"

param($path, $sqlserver, $datafolder, $logfolder)

foreach($bkpfile in Get-ChildItem $path "*.bak" | Select-Object basename)

{

$bkpfile = $bkpfile.BaseName

$server = New-Object Microsoft.SqlServer.Management.Smo.Server($sqlserver)

$restore = New-Object Microsoft.SqlServer.Management.Smo.Restore

$restore.Devices.AddDevice($path+'\'+$bkpfile+'.bak',

[Microsoft.SqlServer.Management.Smo.DeviceType]::File)

$header = $restore.ReadBackupHeader($server)

if($header.Rows.Count -eq 1)

{

$dbname = $header.Rows[0]["DatabaseName"]

}

# .\001_restore.ps1 . $path"\"$bkpfile".bak" $dbname

# param($sqlserver, $bkfilepath, $dbname)

$bkfilepath = $path + "\"+ $bkpfile + ".bak"

# Connect to the specified instance

$srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $sqlserver

# Get the default file and log locations

# (If DefaultFile and DefaultLog are empty, use the MasterDBPath and MasterDBLogPath values)

if(!$datafolder)

{

$fileloc = $srv.Settings.DefaultFile

}

else { $fileloc = $datafolder}

if(!$logfolder)

{

$logloc = $logloc = $srv.Settings.DefaultLog

}

else { $logloc = $logfolder}

if ($fileloc.Length -eq 0) {

$fileloc = $srv.Information.MasterDBPath

}

if ($logloc.Length -eq 0) {

$logloc = $srv.Information.MasterDBLogPath

}

# Identify the backup file to use, and the name of the database copy to create

$bckfile = $bkfilepath

$dbname = $dbname

# Build the physical file names for the database copy

if($fileloc -eq $logloc)

{

$dbfile = $fileloc + '\Data\'+ $dbname + '_Data.mdf'

$logfile = $logloc + '\Log\'+ $dbname + '_Log.ldf'

}

else

{

$dbfile = $fileloc + '\'+ $dbname + '_Data.mdf'

$logfile = $logloc + '\'+ $dbname + '_Log.ldf'

}

# Use the backup file name to create the backup device

$bdi = new-object ('Microsoft.SqlServer.Management.Smo.BackupDeviceItem') ($bckfile, 'File')

# Create the new restore object, set the database name and add the backup device

$rs = new-object('Microsoft.SqlServer.Management.Smo.Restore')

$rs.Database = $dbname

$rs.Devices.Add($bdi)

# Get the file list info from the backup file

$fl = $rs.ReadFileList($srv)

$rfl = @()

foreach ($fil in $fl) {

$rsfile = new-object('Microsoft.SqlServer.Management.Smo.RelocateFile')

$rsfile.LogicalFileName = $fil.LogicalName

if ($fil.Type -eq 'D') {

$rsfile.PhysicalFileName = $dbfile

}

else {

$rsfile.PhysicalFileName = $logfile

}

$rfl += $rsfile

}

# Restore the database

Restore-SqlDatabase -ServerInstance $sqlserver -Database $dbname -BackupFile $bkfilepath -RelocateFile $rfl -NoRecovery

}

Execute the script like in below example.

PS C:\Users\sherbaz\Desktop\restore\New&gt; .\restore_all.ps1 E:\database_Backup_files\ server101.sherbaz.com "M:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data" "L:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log"

1	PS C:\Users\sherbaz\Desktop\restore\New> .\restore_all.ps1 E:\database_Backup_files\ server101.sherbaz.com "M:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data" "L:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log"

Tag: powershell

Verify all AD groups added to a domain user

Auto restore multiple databases in One shot using powershell