Another common request for DBA’s is to search for or validate that certain tables or other objects exist and in what database. This post is a follow-up to our “Check If Database Exists Using Powershell” post where we search look for a database remotely using the “invoke-sqlcmd” commandlet. Note: This… Read more »
Using Powershell we can connect to and return a simple T-SQL query result set using your existing SQL code. We are using the invoke-sqlcmd commandlet for this example so I included the Powershell command to install. (you may need to start VSCode as Administrator). The $resultdata variable will contain the… Read more »
We are looking at the result set for invoke-sqlcmd which normally returns an array of objects. We can re-assign the value to an array or deal with the object and their methods using a different notation. To identify the result set “object” you can execute the following powershell. Notice using… Read more »
Strings tend to be difficult to handle when attempting to locate or modify specific words and characters. Powershell provides quite a few tools to search for special characters, replace words, remove spaces and much more.Here is a quick example to test and find a word within a string, assuming we… Read more »
This script is useful in more complex processes where you want to check if the server is online before attempting to execute something remotely or connecting to the SQL database. Many commands do not handle timeouts well and this test will keep things moving if there is a down server…. Read more »
In a more distributed environment there is a need to use Powershell script to connect to multiple SQL servers and execute queries. The results can include service or Database records that are then combined into an array to be manipulated or displayed. This is very useful to the modern SQL… Read more »
Powershell offers a few cmdlets targeted to managing a remote server and setting the service state as well as the startup. We will run through a few examples of getting the service information and than setting their state as well as the startup option. These scripts requires Administrator access to… Read more »
Powershell has a nice set of Cluster cmdlets that make life a little easier when attempting to inventory your clustered environment. Many of these cmdlets can be executed remotely from a server or workstation and use a virtual name but some cmdlets require you to execute them on the cluster… Read more »
While working with Powershell I have begun to move away from using the SSMS GUI to get basic details on SQL instances. Management Studio is an excellent tool but I find myself keeping Powershell open and using it for quick queries to answer the basics. One of the basics is… Read more »
Working in Powershell with dates, time and time zones is critical if you have data or automated processes that reference different geographical areas. Understanding how to compare these dates with and without a time value can mean the difference between an incorrect report or failing processes. We have written before… Read more »
We can use a commandlet called get-sqlinstance to quickly query an existing SQL instance on a remote server for more information. The administrator often needs to inventory the server environment and that includes identifying the SQL servers as well as all of the instances. Another useful commandlet for SQL Server… Read more »
Another method to query a remote SQL instance and collect DB details is the get-sqldatabase commandlet. Using the “-ServerInstance” parameter you can easily summarize the databases on that instance. This commandlet may require you to import a new module if you have not already. To just get a list of… Read more »
Working with strings is important when handling automation processes especially regarding server names and the SQL Server instances. Administrators define their own naming conventions and usually stay away from unique characters in the string. With SQL Server we have a slash “\” that is required to define the SQL instance… Read more »
Using Active Directory Groups (AD) for SQL Server authentication makes managing access permissions easy. Instead of managing individual login permissions the DBA can recommend using some AD groups that can contain many users and the login and permissions are assigned only one time. I have limited knowledge of Active Directory… Read more »
One of the more common approaches to ETL is using comma separated (csv) files. It’s easy to understand copying files and the file contents too. In this example we are using a few cmdlets to import a CSV file into a Datatable. We will grab the header info from the… Read more »
The more often you work with datatables in Powershell the more opportunities appear where you can utilize more features. We will look at the Merge method as well as updating data and adding new columns to an existing datatable. For this example we will be using the “WideWorldImporters” (WWI) database… Read more »
We want to check for a pending restart. If a server is awaiting a reboot before attempting to to install new software or make OS changes. These powershell examples will help with checking the last reboot date and time as well as return a Yes and No if it is… Read more »
Following up on the last post about dates and using Get-Date we look at the difference between dates and how to compare or calculate them. Dates can be tricky because you are counting on the system to do the calculation correctly but this requires passing the right info or string…. Read more »
No matter what Database you deal with there will be times where you need to manipulate or calculate dates and Powershell offers many different methods to get them. Whether you’re going back in time to 90 days ago or you need to set a variable for a future time and… Read more »
Searching for and cataloging the SQL server instances on your network is a key part of Administration. Powershell offers some quick and simple methods to find servers base on the their name or description in Active Directory. We can use the Powershell module to collect these servers and add them… Read more »
