By: Tim Smith | Comments (3) | Related: > PowerShell
Problem
I frequently run into SQL Server administrative and development tasks, as well as being asked by other administrators and developers, for certain tasks to be performed. In this tip, we cover 6 PowerShell scripts that make these tasks much easier. Keep in mind that these scripts can be called from an ISE window, PS prompt, SQL Server Job Agent, Windows Scheduler, through an application, etc., and I've used all of the below scripts on numerous occasions.
Solution
The following are six useful PowerShell scripts that you can use to help minimize the time it takes for tasks you do on a regular basis.
Unzip All Zip Files In A Folder
With PowerShell, we can add a reference to a DLL, and access its methods. In the below case, we access the FileSystem.dll and call its method ExtractToDirectory, which per Microsoft, takes in a string parameter, pointing to the file to be unzipped, and a string parameter, pointing to the destination. Note in some cases (depending on the .NET version), you will want to verify the existence and location of this DLL (see the below path's reference). The below script unzips all the files in a folder, stores the unzipped files in the folder, and removes all the zip files. If we wanted to change this script to point to a destination, we would just add a parameter $dest and change the line [System.IO.Compression.ZipFile]::ExtractToDirectory($z.FullName, $fold) to [System.IO.Compression.ZipFile]::ExtractToDirectory($z.FullName, $dest). I have yet to work with clients that use no zip files whatsoever, and data vendors have a strong tendency to provide data in files that have been zipped, making this script all the more useful for automating.
Function Unzip-Files ($fold)
{
Add-Type -Path "C:\Windows\Microsoft.NET\assembly\GAC_MSIL\System.IO.Compression.FileSystem\v4.0_4.0.0.0__b77a5c561934e089\System.IO.Compression.FileSystem.dll"
$a_zps = Get-ChildItem $fold -Filter *.zip
foreach ($z in $a_zps)
{
[System.IO.Compression.ZipFile]::ExtractToDirectory($z.FullName, $fold)
}
## Optional - removes the zips after they've been extracted
$r_zip = $fold + "*.zip"
Remove-Item $r_zip
}Zip All Files In A Folder To A Zip File
Below, I do the opposite of the above code, again, using the same DLL reference, this time using its CreateFromDirectory method. I pass in the two required strings for the method - the first is the folder and the second is the location and name of the zip file.
Function Zip-Folder ($fold, $f_zip)
{
Add-Type -Path "C:\Windows\Microsoft.NET\assembly\GAC_MSIL\System.IO.Compression.FileSystem\v4.0_4.0.0.0__b77a5c561934e089\System.IO.Compression.FileSystem.dll"
[System.IO.Compression.ZipFile]::CreateFromDirectory($fold, $f_zip)
}Add Existing Code To Locations or New Projects
In the below, the path "C:\GitHub\AddToProjects\" is hardcoded for a reason, as that is the official location for all potential repetitive code for this example's purposes (a good location in general would be a share that all necessary computers on the network have access to). This script requires that I pass in the location to the new project (or new location), and I have the option to either add the extension or name of the files I want to copy.
Suppose that we are creating an econometrics application and we want to compare some calculations from Austrian economists with calculations from Keynesian economists and we have these in separate files (SQL - .sql, C# - .cs, Ruby - .rb, Python - .py, etc.). If we've named our files in a manner that we can call them, like data_economics_keynesian, then we can import them by referencing data_economics in the $name parameter and (or) if we only need Ruby files, we can filter by that extension (or SQL, C#, etc.). This also holds true for configuration files, like MongoDB's .conf or .yaml files, where we may want to re-use a similar configuration.
From a project standpoint, the code is already in the necessary location, and from there it's simply adding the references (in some cases; in others, it's set). Of course, if developers and administrators don't build code and configurations for re-use, this will have little utility.
Function AddTo-Projects ($newproject, $ext = $null, $name = $null)
{
if (($ext -ne $null) -and ($name -ne $null))
{
$ext = "*." + $ext
$add_projects = Get-ChildItem "C:\GitHub\AddToProjects\" -Filter $ext | Where-Object { $_.BaseName -like "*$name*" }
}
elseif ($ext -ne $null)
{
$ext = "*." + $ext
$add_projects = Get-ChildItem "C:\GitHub\AddToProjects\" -Filter $ext
}
elseif ($name -ne $null)
{
$add_projects = Get-ChildItem "C:\GitHub\AddToProjects\" | Where-Object { $_.BaseName -like "*$name*" }
}
else
{
$add_projects = Get-ChildItem "C:\GitHub\AddToProjects\"
}
foreach ($f in $add_projects)
{
Copy-Item $f.FullName $newproject
}
}Some examples and explanations to Add to a Project
AddTo-Projects -newproject "C:\OurNewProject" -ext "cs"
The above call would move all the C# files from our hard-coded folder ("C:\GitHub\AddToProjects\") to our new project at "C:\OurNewProject".
Here is another example:
AddTo-Projects -newproject "C:\OurNewProject" -name "data"
The above call would move all files with "data" in their name from our hard-coded folder ("C:\GitHub\AddToProjects\") to our new project at "C:\OurNewProject".
And one more example:
AddTo-Projects -newproject "C:\OurNewProject" -ext "ps1" -name "ETL"
The above call would move all PowerShell files with "ETL" in their name from our hard-coded folder ("C:\GitHub\AddToProjects\") to our new project at "C:\OurNewProject".
Automatically Rename A Folder With A Date
In this tip, we see how to re-name a file and re-naming a folder automatically with a date added to it after an underscore is similar. Note that if you pass in the folder name as "C:\foldername\" it will rename it to "C:\YYYYMMDD\" whereas if you pass in "C:\foldername" it will rename the folder to "C:\foldername_YYYYMMDD" which, depending on needs, both can be useful.
Function Rename-ToDate ($fold)
{
$d = Get-Date -uFormat "%Y%m%d"
if ($fold.Substring(($fold.Length-1),1) -eq "\")
{
Rename-Item $fold $d
}
else
{
$n_fd = $fold + "_" + $d
Rename-Item $fold $n_fd
}
}Automatically Create A Folder With A Date and Dump Filter Files Into It
Like the above script, I get asked about this a lot - how can I automatically create a folder, which will have today's date, then automatically move some files by extension to that new folder? The below automates this: we will enter the path where the files are and where we want to create the file ($path) and the extension of the file without the period ($ext, such as "sql" for a .sql file). So, if I wanted to move all text files in the folder "C:\OurFolder\" to a folder inside of it with a date, I would call the function, Archive-NewFolder -path "C:\OurFolder\" -ext "txt" and it would create the folder "C:\OurFolder\20141008\" and move all the text files from "C:\OurFolder\" to it.
Function Archive-NewFolder ($path, $ext)
{
$d = Get-Date -uFormat "%Y%m%d"
$new_f = $path + $d + "\"
$move = $path + "*." + $ext
New-Item $new_f -ItemType Directory
Move-Item $move $new_f
}Add Date (And Possibly Text) To All Files By Extension In A Path
With the below script, we can pass in a folder location and an extension, and this script will automatically re-name all the files with an _YYYYMMDD added to them without affecting files that don't end with that extension; for an example, a file named checkdate.sql would be re-named to checkdate_20141007.sql, and any other file not ending in .sql would be unaffected. In addition, we can also add the parameter $add, which will add text to the file name, such as adding query_ to the file checkdate.sql would become query_checkdate_20141007.sql.
Function Add-DateNameFiles ($path, $ext, $add = $null)
{
$d = "_" + (Get-Date -uFormat "%Y%m%d") + "." + $ext
$ext = "*." + $ext
$allfiles = Get-ChildItem $path -Filter $ext
foreach ($f in $allfiles)
{
if ($add -ne $null)
{
Rename-Item $f.FullName ($add + $f.BaseName + $d)
}
else
{
Rename-Item $f.FullName ($f.BaseName + $d)
}
}
}Next Steps
- Test and verify the scripts with your environment, PowerShell version and .NET library (on applicable scripts).
- Identify where re-using these would save time.
About the author
Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
