Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

6 Useful PowerShell Scripts to Help SQL Server DBAs and Developers


By:   |   Last Updated: 2014-12-01   |   Comments (3)   |   Related Tips: More > 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.


Last Updated: 2014-12-01


next webcast button


next tip button



About the author
MSSQLTips author Tim Smith Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, December 13, 2016 - 2:55:58 PM - samarjun Back To Top

 Hi

all these scrips are quite helpful.

Please share if any Powershell script for scripting logins & permissions, sql jobs in a server \ multiple servers and keep it in a folder of same server

Thanks in advance.

 


Thursday, December 04, 2014 - 3:48:12 AM - Thomas Franz Back To Top

Nice to know, even if you could this stuff easy by using tools like the Total Commander too (maybe except the Add to project stuff - I don't understand, what it does exactly)


Monday, December 01, 2014 - 8:05:29 AM - Praveen Back To Top

Thankyou for sharing -  great ! 


Learn more about SQL Server tools