Windows Power Shell script to Purge/Cleanup Backup & Transaction files

At times, we need to purge backup files (.bak, *.trn) which are older than some x days from the server on the regular basis, such that Disk Space and SQL Data backups will be maintained consistently within the server.

Power Shell Script:-

The below power shell script will purge the backup files (which are older than 5 days) from server, this script identifies the older files based on the last modified date time.

Based on your requirement, you can change the date range and schedule this script for Server Maintenance.

Get-ChildItem -Path "C:\Backups" -Recurse -ErrorAction SilentlyContinue -include *.bak, *.trn | 
Where-Object {$_.LastWriteTime -lt (Get-Date).AddDays(-5) -and $_.PSIsContainer -eq $False} | 
Remove-Item

How to get list of filenames in a folder – Command Shell Script

There may be some situation where you want to find the list of all filenames whose patterns are like “*.txt”, “File*.csv”, “*.html”.., etc. In such scenario you can use the below command shell scripts to get the desired results which you are looking for.

1) To get the list of all filenames and folder names present in a folder “C:\testdata\”

cd "C:\testdata\"
dir /b /s

Output:-

2) To get all the filenames of pattern “*.txt” or “*.html” from folder “C:\testdata\”

dir /b /s "*.txt"
dir /b /s "*.html"

Output:-

3) To get all the filenames of pattern “*.txt” from folder “C:\testdata\” and store the corresponding results in the text document

dir /b /s "*.txt" > output.txt
dir /b /s "*.txt" > "c:\testdata\test\outputs.txt"

Output:-

Using Windows Power Shell to get the Server Disk Space Information

To find Drive Names, Total Space, Free Space and Free Space Percentage for the list of Servers specified in computers.txt file using Power Shell Script.

Prerequisites:-

  1. Save the computers.txt file and below PS script in the same folder
  2. User should have access to the servers mentioned in computers.txt file
  3. There should not be any empty space or new line without Servername in computers.txt file

PS Script:-

$path=Split-Path $MyInvocation.MyCommand.path 
$Computers = get-content "$path\computers.txt" 
foreach ($Computer in $Computers)  
{  
$Disks = Get-wmiobject  Win32_LogicalDisk -computername $Computer -ErrorAction SilentlyContinue -filter "DriveType= 3" 
$Servername = (Get-wmiobject  CIM_ComputerSystem -ComputerName $computer).Name
foreach ($objdisk in $Disks)  
{  
        
    $total=“{0:N3}” -f ($objDisk.Size/1GB)  
    $free= “{0:N3}” -f ($objDisk.FreeSpace/1GB)  
    $freePercent="{0:P2}" -f ([double]$objDisk.FreeSpace/[double]$objDisk.Size)  
       
       Write-Host "Servername      :" $Servername  
       Write-Host "Drive Folder    :" $objDisk.DeviceID "Drive" 
       Write-Host "Total size (GB) :" $total 
       Write-Host "Free Space (GB) :" $free 
       Write-Host “Free Space (%)  :” $freePercent 
} 
} 

Output:-

To save the Output to Diskspace_Report.csv file, Use below PS script:-

$path=Split-Path $MyInvocation.MyCommand.path 
$Computers = get-content "$path\computers.txt" 
foreach ($Computer in $Computers)  
{  
$Disks = Get-wmiobject  Win32_LogicalDisk -computername $Computer -ErrorAction SilentlyContinue -filter "DriveType= 3" 
$Servername = (Get-wmiobject  CIM_ComputerSystem -ComputerName $computer).Name
foreach ($objdisk in $Disks)  
{  
        
    $total=“{0:N3}” -f ($objDisk.Size/1GB)  
    $free= “{0:N3}” -f ($objDisk.FreeSpace/1GB)  
    $freePercent="{0:P2}" -f ([double]$objDisk.FreeSpace/[double]$objDisk.Size)  

        $out=New-Object PSObject 
        $out | Add-Member -MemberType NoteProperty -Name "Servername" -Value $Servername 
        $out | Add-Member -MemberType NoteProperty -Name "Drive" -Value $objDisk.DeviceID  
        $out | Add-Member -MemberType NoteProperty -Name "Total size (GB)" -Value $total 
        $out | Add-Member -MemberType NoteProperty -Name “Free Space (GB)” -Value $free 
        $out | Add-Member -MemberType NoteProperty -Name “Free Space (%)” -Value $freePercent 
        $out | Add-Member -MemberType NoteProperty -Name "Name " -Value $objdisk.volumename 
        $out | Add-Member -MemberType NoteProperty -Name "DriveType" -Value $objdisk.DriveType 
        $out | export-csv $path\Diskspace_Report.csv -NoTypeInformation -Append   
} 
}

Generate Random characters in SQL Server

Here is a script to generate randon characters in SQL Server

It also allows you to generate the characters in random as well as required format. There are four types of format the function supports:

Formats:
1. ‘Proper’ – proper name form (i.e. Xxxxx)
2. ‘Upper’ – all uppercase (i.e. XXXXX)
3. ‘Lower’ – all lowercase (i.e. xxxxx)
5. ‘Mixed’ – randomly mixed case (i.e. xXxxxXXxx)


Create View dbo.Q_Random as 
	Select Rand() as [RandomNumber]
GO
Create Function dbo.fn_GenerateText 
				(@Length integer, @Format varchar(6) = 'Mixed')
Returns varchar(256)
as 
Begin

--	Formats:	
--		'Proper' - proper name form (i.e. Xxxxx)
--		'Upper'  - all uppercase (i.e. XXXXX)
--		'Lower'  - all lowercase (i.e. xxxxx)
--		'Mixed'  - randomly mixed case (i.e. xXxxxXXxx)
--		 null    - randomly mixed case (i.e. xXxXxxxxxX)
--

Declare 
	@RandomValue             varchar(256), 
	@Count                   integer,
	@RandomNumber            float, 
	@RandomNumberInteger     integer, 
	@CurrentCharacter        char(1),
	@ValidCharactersLength   integer,
	@ValidCharacters         varchar(255) 

Set @RandomValue = '';

If (@Length = 0) 
	Goto ReturnData 


If (@Format = 'Mixed') 
	Set @ValidCharacters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'; 
else
	Set @ValidCharacters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz';


Set @ValidCharactersLength = Len(@ValidCharacters); 
Set @CurrentCharacter      = ''; 
Set @RandomNumber          = 0; 
Set @RandomNumberInteger   = 0; 

Set @Count = 1; 


While @Count <= @Length 
Begin 
	Set @RandomNumber = (Select RandomNumber from Q_Random); 

	Set @RandomNumberInteger = Convert(integer, ((@ValidCharactersLength - 1) * @RandomNumber + 1)); 
 
	Set @CurrentCharacter = SubString(@ValidCharacters, @RandomNumberInteger, 1); 

	Set @RandomValue = @RandomValue + @CurrentCharacter; 

	Set @Count = @Count + 1; 
End 


If @Format = 'Lower' 
	Set @RandomValue = Lower(@RandomValue); 

If @Format = 'Upper' 
	Set @RandomValue = Upper(@RandomValue); 

If @Format = 'Proper' 
	Set @RandomValue = Upper(Left(@RandomValue, 1)) + Substring(Lower(@RandomValue), 2, (@Length - 1));  

--	... or the default  gives random `casing`, and 'Mixed' gives random alphanumeric `casing`


ReturnData:

Return @RandomValue 

End 

Sample Executions:

Select dbo.fn_GenerateText(10,’Lower’)
Select dbo.fn_GenerateText(10,’Upper’)
Select dbo.fn_GenerateText(10,’Mixed’)–default value
Select dbo.fn_GenerateText(10,’Proper’)

How to change mdf and ldf files path to instance default path in SQL Server

Here is a code snippet to change the mdf and ldf file path of an existing database to instance default path in SQL Server.
If you want to move to fixed path, then you can change the script as required.

Assumptions:
1. The script is mainly written for SQL Server 2016, for other version, please change as required.
2. The script will not execute or change the path automatically. It just prints the statements to execute. The user can validate the scripts and do the action as required. This is to prevent any unexpected events.


--Print 'Collect Default Data & Log information'
declare @DefLOG nvarchar(512)
declare @DefDATA nvarchar(512)

--Prepare the SQL statements for remapping
if (Cast(SERVERPROPERTY('Productmajorversion') as varchar(2))='13')
Begin

Set @DefLog = Cast( Serverproperty('InstanceDefaultLogPath') as varchar(512))
Set @DefDATA = Cast( Serverproperty('InstanceDefaultDataPath') as varchar(512))

--Print 'Move all datafiles'
declare cur_move cursor for
select --DB_NAME(dbid),name,filename, reverse(SUBSTRING(reverse(filename),1,CHARINDEX('\',reverse(filename))-1)),
'ALTER DATABASE ['+DB_NAME(dbid)+'] MODIFY FILE ( NAME = '''+name+''' , FILENAME = '''+@DefDATA+reverse(SUBSTRING(reverse(filename),1,CHARINDEX('\',reverse(filename))-1))+''' )'
from sys.sysaltfiles where dbid <32000
and groupid=1 and dbid>5 and charindex(@DefDATA,filename)=0
union all
select --DB_NAME(dbid),name,filename, reverse(SUBSTRING(reverse(filename),1,CHARINDEX('\',reverse(filename))-1)),
'ALTER DATABASE ['+DB_NAME(dbid)+'] MODIFY FILE ( NAME = '''+name+''' , FILENAME = '''+@DefLOG+reverse(SUBSTRING(reverse(filename),1,CHARINDEX('\',reverse(filename))-1))+''' )'
from sys.sysaltfiles where dbid <32000 and charindex(@DefDATA,filename)=0
and groupid=0 and dbid>5

declare @move varchar(max)
open cur_move
fetch next from cur_move into @move

while @@fetch_status=0
begin

print @move
--Exec (@move)

fetch next from cur_move into @move
end
close cur_move
deallocate cur_move

End
Else
Begin
Select 'This script supports SQL Server 2016 or later! For older version, you need to below to identify the default path, otherwise all are same.'
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @DefLOG OUTPUT
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @DefDATA OUTPUT
return;
End

Dark theme in Sql Server Management Studio

Many people prefer dark theme for computer displays. It actually enhances the visual ergonomics by reducing eye strain and providing comfort of use at all conditions.Another important benefit, it conserves battery power, there by enabling device to run longer period of time.

Please refer this link for more details.

Now, let us take a look at the fact that dark theme can be applied to SSMS and how do we do and what are implications.

Writing this blog, I am using SSMS 18.2 version, the latest by now. In future, the facts may change if MS decided to make a change.

Do we have Dark theme by default available in SSMS 18.2?

The answer is NO. We do not have an option to change the SSMS into a dark theme by default.However, it provides a way for us to change to “Blue”, “Blue (Extra Contrast)” and “Light” as below.

How do we make SSMS in Dark theme?

There are two ways from my analysis:

1. Custom your SSMS window using Tools -> Options -> Environment -> Fonts and Colors.

The challenge here is to customize each and every items from the list and provide the color combinations. Honestly, this is something very challenging for me to identify the better combinations. There is a blog written by Aaron Betrand on the same, and its really helpful for the basic settings.

2. Change the SSMS settings

There is a file called ssms.pkgundef in the path – “Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE“. This file is where you will place registry entries that are to be removed during setup of your application. If you look at the content of this files, you can see the below code:


// Remove Dark theme
[$RootKey$\Themes\{1ded0138-47ce-435e-84ef-9ec1f439b749}]

The above code, actually removes the dark options from SSMS while setting up of SSMS. To enable dark theme for SSMS, you can simply comment the code as below and it will give the option of “Dark” in Tool -> Option -> General -> Color Theme as below.

Please be aware, this is a hack or undocumented way of changing SSMS config files. It may or may not work and its not supported by Microsoft. This may be made unavailable or documented because this may not be interoperable with other Windows display settings. So please be use this as an option at your own risk.

Cleanup of Load Runner Test data from SQL Server

Here is a script to purge the Load test data from your load runner database. This would be handy at times like running of disk space or performance issues related to load runner etc.


--Provide your Load runner database name
USE <<Loadrunner DBName>>
Create proc SQLZealot_CleanupTestData  (@DeleteDateUpto Datetime)
with encryption
as
Begin

	If not exists(Select 1 From sys.tables where name = 'TempTobeDeletedLoadRecords')
		Select LoadtestrunID into TempTobeDeletedLoadRecords from Loadtestrun (nolock) where StartTime < @DeleteDateUpto

	Declare @LoadtestrunID bigint
	While exists(Select 1 From TempTobeDeletedLoadRecords )
	Begin
		Set @LoadtestrunID = (Select Top 1 Loadtestrunid from TempTobeDeletedLoadRecords ORder by 1 asc)
		Exec Prc_DeleteLoadTestRun @Loadtestrunid
		Delete TempTobeDeletedLoadRecords Where Loadtestrunid = @LoadtestrunID
	End

	Drop Table TempTobeDeletedLoadRecords

End