MsSQL – Backup all SQL Server Databases

source: https://www.mssqltips.com/sqlservertip/1070/simple-script-to-backup-all-sql-server-databases/

Change @Path for the location of the backups.

DECLARE @name VARCHAR(50) — database name 
DECLARE @path VARCHAR(256) — path for backup files 
DECLARE @fileName VARCHAR(256) — filename for backup 
DECLARE @fileDate VARCHAR(20) — used for file name
 
— specify database backup directory
SET @path = ‘C:\Backup\’ 
 
— specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
 
— specify filename format Including Time
— SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + ‘_’ + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),’:’,”)
 
DECLARE db_cursor CURSOR READ_ONLY FOR 
SELECT name
FROM master.sys.databases
WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’)  — exclude these databases
AND state = 0 — database is online
AND is_in_standby = 0 — database is not read only for log shipping
 
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  
 
WHILE @@FETCH_STATUS = 0  
BEGIN  
   SET @fileName = @path + @name + ‘_’ + @fileDate + ‘.BAK’ 
   BACKUP DATABASE @name TO DISK = @fileName 
 WITH STATS, COMPRESSION, COPY_ONLY
   FETCH NEXT FROM db_cursor INTO @name  
END  
 
 
CLOSE db_cursor  
DEALLOCATE db_cursor

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() 
 
} 

Get Office 365 users with a specific license type via Powershell

It can sometimes be useful to get a list of Office 365 users with a specific license type via PowerShell. Instead of logging into the Office 365 portal and using a filtered view in the admin center, you can do it straight from the command line.

  • Connect to Office 365 via Powershell. If this cmdlet doesn’t work for you, follow this quick guide for instructions on installing the required PowerShell module.

Connect-MsolService

  • Run Get-MsolAccountSku to get a list of the current licenses in your Office 365 tenant. Make a note of the AccountSkuId value for the license you want to filter on.

Get-MsolAccountSku

Get-MsolAccountSku Information
  • Now you can edit this short script to get the users matching that license. In this case, we’re getting users with the EnterprisePremium license.

Get-MsolUser | Where-Object {($_.licenses).AccountSkuId -match "EnterprisePremium"}

Replace EnterprisePremium with the AccountSkuID you’re trying to filter by. Since we’re using the -match operator we don’t need to type the entire AccountSkuID, we can just type enough of it to ensure that we’re only retrieving that specific one.

The script can be tweaked for specific use

Get-MsolUser -MaxResults 100000 | Where-Object {($_.licenses).AccountSkuId -match "EnterprisePremium"} | ft UserPrincipalName, IsLicensed | Out-File c:\temp\E3.csv

Get-MsolUser With Specific Office 365 License

Export these users to a text document

You can export these users to a text document using the Out-File cmdlet.

Get-MsolUser | Where-Object {($_.licenses).AccountSkuId -match "EnterprisePremium"} | Out-file C:\temp\EnterprisePremiumUsers.csv

Source: https://gcits.com/knowledge-base/get-office-365-users-specific-license-type-via-powershell/

Terminal Server Inactive Printer Ports

During the restart of a Terminal Server, inactive TS ports should be deleted. When these inactive ports are not deleted, over time, a server can become sluggish or even hang because of this. When there are a lot of Inactive TS Ports in the registry, printer redirection may also suffer.

If you are experiencing OS performance, Print spooler or RDS printer redirection hangs, check the presences of Inactive TS Ports under the key:

HKLM\SYSTEM\CurrentControlSet\Control\DeviceClasses\{28d78fad-5a12-11d1-ae5b-0000f803a8c2}\##?#Root#RDPBUS#0000#{28d78fad-5a12-11d1-ae5b-0000f803a8c2}

If the subkeys with #TS001\device parameters in it have a “Port Description” with “Inactive TS Port” then the entier root key should be deleted.

With the powershell script below, it is possible to delete these Inactive TS Ports from a remote machine. The scripts reads an OU in the Active Directory where all RDSH servers are located and then it will delete all Inactive TS Ports from those servers.

#Get RDSH Servers
$servers = Get-ADComputer -Filter * -SearchBase "OU=Netherlands, OU=Session Hosts, OU=RDS2012, OU=Servers, OU=Infra,DC=infra,DC=imtech,DC=NL"  

#Delete inactive TS Ports (https://archive.codeplex.com/?p=inactivetsport)
foreach ($server in $servers) {
    $RemoteComputer =$server.name 
    if (Test-Connection -ComputerName $RemoteComputer -Count 1 -ErrorAction SilentlyContinue) { 
        Invoke-Command -ComputerName $RemoteComputer -ScriptBlock {
            $Gegevens = Get-ChildItem -path 'HKLM:SYSTEM\CurrentControlSet\Control\DeviceClasses\{28d78fad-5a12-11d1-ae5b-0000f803a8c2}\##?#ROOT#RDPBUS#0000#{28d78fad-5a12-11d1-ae5b-0000f803a8c2}' -Recurse
            ($gegevens.Name ) -replace "\\Device parameters" | Select-Object -Unique | ForEach-Object { 
                $subkey = ($_ -replace "HKEY_LOCAL_MACHINE\\" , "HKLM:\") + "\Device Parameters"         
                $PortDescription = (Get-itemproperty -path $subkey)."Port Description"
                if ($PortDescription -eq "Inactive TS Port") {
                    $subkeydelete = ($_ -replace "HKEY_LOCAL_MACHINE\\" , "HKLM:\")
                    write-host "delete subkey from $env:COMPUTERNAME => $subkeydelete"
                    Remove-Item -Path $subkeydelete -Recurse                   
                }
            }
        } 
    }
}

For more information about this issue in Windows Server 2008 please check the site: https://archive.codeplex.com/?p=inactivetsport

How to enable the Disk Cleanup tool on Windows Server 2008 R2

source: https://support.appliedi.net/kb/a110/how-to-enable-the-disk-cleanup-tool-on-windows-server-2008-r2.aspx

How to enable the Disk Cleanup tool:

1) Go to Programs & Features, and in the Features section, enable/install “Desktop Experience”.   The downside to this is that you will need to reboot your server after installing this and it installs other components you do not need on a server.

2) [RECOMMENDED] –  All you really need to do is copy some files that are already located on your server into specific system folders, as described at http://technet.microsoft.com/en-us/library/ff630161(WS.10).aspx

The location of the files you need to copy depend on your version of Windows:

Operating System Architecture File Location
Windows Server 2008 R2 64-bit C:\Windows\winsxs\amd64_microsoft-windows-cleanmgr_31bf3856ad364e35_6.1.7600.16385_none_c9392808773cd7da\cleanmgr.exe
Windows Server 2008 R2 64-bit C:\Windows\winsxs\amd64_microsoft-windows-cleanmgr.resources_31bf3856ad364e35_6.1.7600.16385_en-us_b9cb6194b257cc63\cleanmgr.exe.mui
Windows Server 2008 64-bit C:\Windows\winsxs\amd64_microsoft-windows-cleanmgr.resources_31bf3856ad364e35_6.0.6001.18000_en-us_b9f50b71510436f2\cleanmgr.exe.mui
Windows Server 2008 64-bit C:\Windows\winsxs\amd64_microsoft-windows-cleanmgr_31bf3856ad364e35_6.0.6001.18000_none_c962d1e515e94269\cleanmgr.exe.mui
Windows Server 2008 32-bit C:\Windows\winsxs\x86_microsoft-windows-cleanmgr.resources_31bf3856ad364e35_6.0.6001.18000_en-us_5dd66fed98a6c5bc\cleanmgr.exe.mui
Windows Server 2008 32-bit C:\Windows\winsxs\x86_microsoft-windows-cleanmgr_31bf3856ad364e35_6.0.6001.18000_none_6d4436615d8bd133\cleanmgr.exe

Windows Server 2012:

C:\Windows\WinSxS\amd64_microsoft-windows-cleanmgr_31bf3856ad364e35_6.2.9200.16384_none_c60dddc5e750072a\cleanmgr.exe
C:\Windows\WinSxS\amd64_microsoft-windows-cleanmgr.resources_31bf3856ad364e35_6.2.9200.16384_en-us_b6a01752226afbb3\cleanmgr.exe.mui

Windows Server 2012 R2:  must install Desktop Experience. Use Powershell command:
Install-WindowsFeature Desktop-Experience

 

Once you’ve located the files move them to the following locations (Server 2012 non-R2 and earlier):

  1. Copy Cleanmgr.exe to %systemroot%\System32.
  2. Copy Cleanmgr.exe.mui to %systemroot%\System32\en-US.

You can now launch the Disk cleanup tool by running Cleanmgr.exe from the command prompt.

If an old cleanup manager is used, windows update files will not be cleaned. For this you need Microsoft hotfix 2852386

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
	}
}

 

Failed To Open or Edit GPO

source: https://mohdzmaili.wordpress.com/2017/02/15/failed-to-open-or-edit-gpo/comment-page-1/#comment-30

 

Group Policy Object cannot be opened or edited with the error: Failed to open Group Policy Object. You might not have the appropriate rights.

Details: The volume for a file has been externally altered so that the opened file is no longer valid.

gpo1

gpo2

Solution:

  • Get GPO GUID: from Group Policy Management Console (GPMC) –> choose GPO –> from right pane go to Details tab –> go to Unique ID field.
  • Open the path: C:\Windows\SYSVOL\sysvol\<Domain>\Policies\<GPO GUID>\User
  • Delete “registry.pol” file.

Note: if you have migrated SYSVOL replication type from FRS to DFSR, open the path:  C:\Windows\SYSVOL_DFSR\sysvol\<Domain>\Policies\<GPO GUID>\User