MsSQL Backup a Database

Use the following query to backup a MsSQL database. Fill in the correct database name (@name) and backup location (@path).

This script uses the date for the filename, so if multiple backups are needed on the same day make sure to add some remarks to it (@filename).

Also a COPY_ONLY object is used, so this will not interfere with the normal backup job.



— Declare variables
DECLARE @fileName VARCHAR(256) — filename for backup  
DECLARE @path VARCHAR(256) — path for backup files  
DECLARE @name VARCHAR(50) — database name  
DECLARE @fileDate VARCHAR(20) — used for file name
— specify database name
SET @name = ‘databasename’
— specify database backup directory
SET @path = ‘backup location’
— specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
SET @fileName = @path + @fileDate + ‘-‘ + @name + ‘.BAK’
— start backup of the database
BACKUP DATABASE @name TO DISK = @fileName WITH STATS, COMPRESSION, COPY_ONLY

Last Logon Date AD Users

source: https://gallery.technet.microsoft.com/scriptcenter/Get-Last-Logon-for-Users-c8e3eab2

The “lastLogon” Active Directory attribute is a property that is not replicated throughout the Domain Controllers.  This attribute is stored on the domain controller that received the authentication request and updated its property accordingly.  Because of this behavior, you have have experienced issues where Domain Controllers in other sites have old or empty lastLogon information.  This makes it difficult to be sure an account is truly inactive without verifying on each Domain Controller.  This can be a problem for environments that have tens or hundreds of Domain Controllers across its enterprise.

I was faced with a problem of writing a script, and like many, developed a seemingly simple solution.  The problem was the runtime was very long for the amount of Domain Controllers / User accounts for the environment.  I sought out to find the solution to this exceedingly long runtime, and I finally did.

This PowerShell will query each of your Domain Controllers only once and produce the most recent logon date/time.

To protect the informaiton about my directory, lets say my environment is between 5 and 10 Domain Controllers, with between 15 – 25k user accounts.  My runtime is between 4-5 minutes for the entire directory!  Which is a far cry faster than the hours other methods produce.

By default, this script will return the entire directory ($Username=*) and save to a CSV ($FileName).  You may call the function with a -Username to return a single account, and this will output on screen instead of a file.

<########################################################################### 
    The purpose of this PowerShell script is to collect the last logon  
    for user accounts on each DC in the domain, evaluate, and return the 
    most recent logon value. 
 
        Author:   Jeremy Reeves 
        Modified: 02/14/2018 
        Notes:    Must have RSAT Tools if running on a workstation 
 
Note: Added enabled true/false to output
###########################################################################> 
 
 
Import-Module ActiveDirectory 
 
function Get-ADUsersLastLogon($Username="*") { 
 
    $FilePath_Prefix = "C:\temp\UserLastLogon-" 
 
    function Msg ($Txt="") { 
        Write-Host "$([DateTime]::Now)    $Txt" 
    } 
 
    #Cycle each DC and gather user account lastlogon attributes 
     
    $List = @() #Define Array 
    (Get-ADDomain).ReplicaDirectoryServers | Sort | % { 
 
        $DC = $_ 
        Msg "Reading $DC" 
        $List += Get-ADUser -Server $_ -Filter "samaccountname -like '$Username'" -Properties LastLogon | Select samaccountname,lastlogon,enabled,@{n='DC';e={$DC}} 
 
    } 
 
    Msg "Sorting for most recent lastlogon" 
     
    $LatestLogOn = @() #Define Array 
    $List | Group-Object -Property samaccountname | % { 
 
        $LatestLogOn += ($_.Group | Sort -prop lastlogon -Descending)[0] 
 
    } 
     
    $List.Clear() 
 
    if ($Username -eq "*") { #$Username variable was not set.    Running against all user accounts and exporting to a file. 
 
        $FileName = "$FilePath_Prefix$([DateTime]::Now.ToString("yyyyMMdd-HHmmss")).csv" 
         
        try { 
 
            $LatestLogOn | Select samaccountname, lastlogon, @{n='lastlogondatetime';e={[datetime]::FromFileTime($_.lastlogon)}}, Enabled, DC | Export-CSV -Path $FileName -NoTypeInformation -Force 
            Msg "Exported results. $FileName" 
 
        } catch { 
 
            Msg "Export Failed. $FileName" 
 
        } 
 
    } else { #$Username variable was set, and may refer to a single user account. 
 
        if ($LatestLogOn) { $LatestLogOn | Select samaccountname, @{n='lastlogon';e={[datetime]::FromFileTime($_.lastlogon)}}, Enabled, DC | FT } else { Msg "$Username not found." } 
 
    } 
 
    $LatestLogon.Clear() 
 
} 

Create a Dynamics NAV NST Instance with Powershell

How to create a NST instance with a powershell script:

 

Set-ExecutionPolicy -ExecutionPolicy Unrestricted
Set-ExecutionPolicy -Scope CurrentUser -ExecutionPolicy Unrestricted

Import-Module "C:\Program Files\Microsoft Dynamics NAV\100\Service\NavAdminTool.ps1" -DisableNameChecking

# Set varaibles for NST
$NAVServiceInstance = 'instance-name'
$DatabaseName = 'database-name'
$DatabaseServer = 'database-server'
$NAVServiceUser = 'service-account'
$NAVServiceUserPW = 'service-account-password'

$DefaultTimeZone = 'Server Time Zone'
$MaxUploadSize = 2047
$EnableTaskScheduler = 'False'

$UseNTLM = $TRUE

$SOAPMaxMsgSize = '5120'

$ChangeTimeout = $FALSE
$IdleClientTimeout = '01:30:00'

$IsNAS = $FALSE
$NASArgument = 'JOBQUEUE'
$NASCodeunit = '450'
$NASMethod = ''
$DefaultCompany = ''

$IsNOR = $FALSE
$LanguageID = '1044'
$Language = 'no-NO'

# NAV Service Account
$secpasswd = ConvertTo-SecureString $NAVServiceUserPW -AsPlainText -Force
$mycreds = New-Object System.Management.Automation.PSCredential ($NAVServiceUser, $secpasswd)

##Creating NST
New-NAVServerInstance $NAVServiceInstance -DatabaseName $DatabaseName `
                                          -DatabaseServer $DatabaseServer `
                                          -ManagementServicesPort 7045 `
                                          -ClientServicesPort 7046 `
                                          -ODataServicesPort 7048 `
                                          -SOAPServicesPort 7047 `
                                          -ServiceAccount user `
                                          -ServiceAccountCredential $mycreds `
                                          -Verbose

Set-NAVServerConfiguration   -ServerInstance $NAVServiceInstance `
                             -KeyName ServicesDefaultTimeZone `
                             -KeyValue $DefaultTimeZone `
                             -WarningAction SilentlyContinue

Set-NAVServerConfiguration   -ServerInstance $NAVServiceInstance `
                             -KeyName ClientServicesMaxUploadSize `
                             -KeyValue $MaxUploadSize `
                             -WarningAction SilentlyContinue

Set-NAVServerConfiguration   -ServerInstance $NAVServiceInstance `
                             -KeyName EnableTaskScheduler `
                             -KeyValue $EnableTaskScheduler `
                             -WarningAction SilentlyContinue

Set-NAVServerConfiguration   -ServerInstance $NAVServiceInstance `
                             -KeyName ServicesUseNTLMAuthentication `
                             -KeyValue $UseNTLM `
                             -WarningAction SilentlyContinue

Set-NAVServerConfiguration   -ServerInstance $NAVServiceInstance `
                             -KeyName SOAPServicesMaxMsgSize `
                             -KeyValue $SOAPMaxMsgSize `
                             -WarningAction SilentlyContinue
                             
                             
##Creating NAS
IF ($IsNAS) {  
Set-NAVServerConfiguration   -ServerInstance $NAVServiceInstance `
                             -KeyName ClientServicesEnabled `
                             -KeyValue FALSE `
                             -WarningAction SilentlyContinue

Set-NAVServerConfiguration   -ServerInstance $NAVServiceInstance `
                             -KeyName SOAPServicesEnabled `
                             -KeyValue FALSE `
                             -WarningAction SilentlyContinue

Set-NAVServerConfiguration   -ServerInstance $NAVServiceInstance `
                             -KeyName ODataServicesEnabled `
                             -KeyValue FALSE `
                             -WarningAction SilentlyContinue

Set-NAVServerConfiguration   -ServerInstance $NAVServiceInstance `
                             -KeyName ManagementServicesEnabled `
                             -KeyValue FALSE `
                             -WarningAction SilentlyContinue

Set-NAVServerConfiguration   -ServerInstance $NAVServiceInstance `
                             -KeyName NASServicesStartupArgument `
                             -KeyValue $NASArgument `
                             -WarningAction SilentlyContinue

Set-NAVServerConfiguration   -ServerInstance $NAVServiceInstance `
                             -KeyName NASServicesStartupCodeunit `
                             -KeyValue $NASCodeunit `
                             -WarningAction SilentlyContinue

Set-NAVServerConfiguration   -ServerInstance $NAVServiceInstance `
                             -KeyName NASServicesStartupMethod `
                             -KeyValue $NASMethod `
                             -WarningAction SilentlyContinue

Set-NAVServerConfiguration   -ServerInstance $NAVServiceInstance `
                             -KeyName ServicesDefaultCompany `
                             -KeyValue $DefaultCompany `
                             -WarningAction SilentlyContinue
}

##Set Idle Client Timeout
IF ($ChangeTimeout) {  
Set-NAVServerConfiguration   -ServerInstance $NAVServiceInstance `
                             -KeyName ClientServicesIdleClientTimeout `
                             -KeyValue $IdleClientTimeout `
                             -WarningAction SilentlyContinue
}

##Set Services Language
IF ($IsNOR) {  
Set-NAVServerConfiguration   -ServerInstance $NAVServiceInstance `
                             -KeyName DefaultLanguageId `
                             -KeyValue $LanguageID `
                             -WarningAction SilentlyContinue

Set-NAVServerConfiguration   -ServerInstance $NAVServiceInstance `
                             -KeyName ServicesLanguage `
                             -KeyValue $Language `
                             -WarningAction SilentlyContinue
}

#Add NAVService to portsharing and start Service.
#Import-Module $PSScriptRoot\NAVServerInstancePortSharing.ps1
#Enable-NAVServerInstancePortSharing $NAVServiceInstance

 

Synching Microsoft NPS (RADIUS) servers

source: http://www.routereflector.com/2016/07/syncing-microsoft-nps-servers/

Use the following powershell script to sync microsoft NPS (radius) servers.

Export-NpsConfiguration -Path C:\TEMP\LocalNPSExportedConfig.xml

$CurrentServerNPS = $env:computername

$NPServers = Get-ADGroupMember "RAS and IAS Servers"
$NPServers | ForEach-Object {

	$NPServerName = $_.Name

	if ($NPServerName -ne $CurrentServerNPS) {
		$NPServerName
		copy-item Export-NpsConfiguration -Path  \\$NPServerName\C$\TEMP\LocalNPSExportedConfig.xml
		Invoke-Command -ComputerName $NPServerName -ScriptBlock {Export-NPSConfiguration -Path C:\TEMP\BackupNPSExportedConfig.xml
		Invoke-Command -ComputerName $NPServerName -ScriptBlock {Import-NPSConfiguration -Path C:\TEMP\LocalNPSExportedConfig.xml
	}
}

 

Copy SQL server database role

source: http://stackoverflow.com/questions/6300740/how-to-script-sql-server-database-role

 

With the following script a script is generated which can be used to copy a SQL database role

declare @RoleName varchar(50) = 'RoleName'

declare @Script varchar(max) = 'CREATE ROLE ' + @RoleName + char(13)
select @script = @script + 'GRANT ' + prm.permission_name + ' ON ' + OBJECT_NAME(major_id) + ' TO ' + rol.name + char(13) COLLATE Latin1_General_CI_AS 
from sys.database_permissions prm
    join sys.database_principals rol on
        prm.grantee_principal_id = rol.principal_id
where rol.name = @RoleName

print @script

 

VMware get inventory

source: http://www.wooditwork.com/2010/08/16/exporting-all-that-useful-vm-information-with-powercli/

To export a list of usefull information about your environment use the following oneliner to export it to a CSV.

Get-VM | Select Name, PowerState, NumCPU, MemoryMB, ProvisionedSpaceGB, UsedSpaceGB, @{N=”ESX Host”;E={Get-VMHost -VM $_}}, @{N=”Datastore”;E={Get-Datastore -VM $_}}, @{N=”Guest OS”;E={Get-VMGuest -VM $_}} | Export-CSV d:\temp\report.csv

 

 

Enable CBT on VMware

See also http://www.bvanleeuwen.nl/faq/?p=1236

Create vm list

$targets = Get-VM | Select Name, @{N="CBT";E={(Get-View $_).Config.ChangeTrackingEnabled}} | WHERE {$_.CBT -like "False"}

Enable CBT

ForEach ($target in $targets) {
   $vm = $target.Name
   $vmView = Get-vm $vm | get-view
   $vmConfigSpec = New-Object VMware.Vim.VirtualMachineConfigSpec
   $vmConfigSpec.changeTrackingEnabled = $true
   $vmView.reconfigVM($vmConfigSpec)
}

Create/Remove snapshot to enable CBT

ForEach ($target in $targets) {
   $vm = $target.Name
   Get-VM $vm | Get-Snapshot | Remove-Snapshot -confirm:$false
   New-Snapshot -VM (Get-VM $vm ) -Name "CBTSnap"
   Get-VM $vm | Get-Snapshot -Name "CBTSnap" | Remove-Snapshot -confirm:$false
}

 

Update a HA Netscaler environment

source:http://support.citrix.com/article/CTX127455

To update an HA netscaler, do the following steps.

User-added image

Upgrade the secondary netscaler appliance

    1. Save the config: save config
    2. Switch to shell: shell
    3. Change to the installation directory: cd /var/nsinstall
    4. Create a temporary directory: mkdir x.xnsinstall
    5. Chang to the created directory: cd x.xnsinstall
    6. Upload the files to the temporary directory (e.g. pscp build-11.0-66.11_nc.tgz nsroot@192.168.1.1:/var/nsinstall/11.0nsinstall/build-11.0-66.11_nc.tgz)
    7. Extract the files: tar -zxvf ns-x.0-xx.x-doc.tgz
    8. Install the software: # ./installns
    9. Press y to restart the appliance
    10. Check the state of the appliance: > show ha node
      This should state that it is the secondary node and synchronization is disabled. To disable the synchronization manually run the command: > set node -hasync disable
    11. Check the configuration
      The version can be found with the command:  > show version
    12. Failover the appliance: > force failover

Upgrade the primary netscaler appliance

  1. Follow step 1 to 9 mentioned in the previous section
  2. Check if the appliance is UP and it is the primary node: > show ha node
    If the appliance isn’t the primary application a failover can be initiated: > force failover

Enable Synchronization

  1. Log on the secondary node, check if it is the secondary node: > show node
  2. Enable synchronization: > set node -hasync enable
  3. Check synchronization status: > show ns runningconfig

The update has been completed. Additional backups can be removed as well the downloaded files in the created directory (step 4).

Disable all active users from an AD group

To disable all enabled users from an Active Directory group use the following script. Replace the groupname with the correct groupname. Also this script will show what it does, but does not disable the accounts until you remove the “-whatif” option from the command: Disable-ADAccount -Identity $user -whatif

 

The original script comes from https://community.spiceworks.com/topic/380100-disable-enable-ad-account-based-on-group-membership

 

$Group = "groupname"
Try { Import-Module ActiveDirectory -ErrorAction Stop }
Catch { Write-Host "Unable to load Active Directory module, is RSAT installed?"; Exit }

Try {
    $GroupDN = (Get-ADGroup $Group -ErrorAction Stop).DistinguishedName
}
Catch {
    Write-Host "Unable to locate group: $Group because ""$($Error[0])""" -ForegroundColor Red
    Exit
}

ForEach ($User in (Get-ADUser -Filter * -Properties MemberOf))
{   If ($User.MemberOf -contains $GroupDN)
    {   
        If ($user.enabled)
        {
            Write-Host "Disabling $($User.Name)"
            Disable-ADAccount -Identity $user -whatif
        }
    }
}