Press "Enter" to skip to content

Category: Powershell

Searching for SQL Server Backup Locations

David Fowler can’t remember where those backups went:

Sometimes I find remembering where a particular server sends its backups to a nightmare.

You might have servers backing up to different locations, you might have different locations for individual databases and different locations for your fulls, diffs and logs. You might be trying to get your head around a customer’s set up, where the backups make no logical sense at all.

Whatever you’re up to, at some point, for some reason you’re going to need to access your backup location to get at the files.

Read on for a Powershell script which can help out with this task.

Comments closed

Reading the SQL Server Error Log

Lee Markum has two ways to read the SQL Server error log:

Reading the SQL Server Error Log is important when troubleshooting many issues for SQL Server. Some example issues would be errors related to Always On Availability Groups, long IO occurrence messages, and login failures.

Unfortunately, the SQL Server Error Log can be very full of information, making specific things hard to find, especially if you’re just visually scrolling the Error Log. Even if you’re recycling the Error Log each day and keeping 30 or more days of error log, on a busy system, the error log can still be quite full, even for a single day.

Click through for those techniques.

Comments closed

Finding Assigned Synapse RBAC Roles in Powershell

Charith Caldera wants to know your access level:

One of the key use cases that most customers face difficulties while retrieving or assigning the role-based access control in Azure Synapse Analytics, that they cannot find the correct usernames, group names or the service principal names using the PowerShell cmdlet “Get-AzSynapseRoleAssignment”. The PowerShell cmdlet only provides limited information and it’s difficult to understand since that contains the object IDs. 

Read on for a script which helps with this problem.

Comments closed

Changing Default Powershell Behavior via Commands or Proxies

Jeff Hicks gives us a choice:

I’ve often told people that I spend my day in a PowerShell prompt. I run almost my entire day with PowerShell. I’ve shared many of the tools I use daily on Github. Today, I want to share another way I have PowerShell work the way I need it, with minimal effort. This specific task centers on files and folders.

As you might expect, I am constantly creating, editing, and managing files. I do all of this from a PowerShell prompt. I rarely use the start menu to find a program to launch. My challenge has always been finding the files and folders I’ve recently been using. Get-ChildItem is naturally the PowerShell tool of choice, but I’ve finally gotten around to making it work the way I need.

Not having done either of these before, I’m not sure which would be my preference, as I’d like to make sure it’s easy for me to remember later how I got to this non-standard state in case I need to replicate it elsewhere or if somebody else is at my keyboard. That’s one nice thing about the .bashrc file: it’s just there and well-known enough that people can look for changes there.

Comments closed

Pre-Staging Log Shipping Backups with dbatools

Jess Pomfret doesn’t waste time:

Log shipping is a SQL Server feature used for disaster-recovery where the transaction log backups are ‘shipped’ from your production instance to a secondary instance. This enables you to cutover to this secondary server in the event of a disaster where you lose your primary instance. Log shipping is not a new feature but is still quite popular.

Recently I was tasked with setting up Log Shipping for a reasonably large production database. I didn’t want to initialize the secondary database with a new full backup as I was already taking full and log backups of the database. In this case we have the option of initialising the database by restoring the full & log backups up to the latest point in time and then configuring log shipping.

Read on to see how you can use dbatools to do this easily.

Comments closed

Reversing Strings with Powershell

Jeff Hicks solves a challenge:

The warm-up challenge was all about manipulating strings. That alone may not sound like much. But the process of discovering how to do it and wrapping it up in a PowerShell function is where the true value lies.

Beginner Level: Write PowerShell code to take a string like ‘PowerShell’ and display it in reverse.

Intermediate Level: Take a sentence like, “This is how you can improve your PowerShell skills.” and write PowerShell code to display the entire sentence in reverse with each word also reversed. You should be able to encode and decode text. Ideally, your functions should take pipeline input. For bonus points, toggle upper and lower case when reversing the word.

Click through to see both of these solutions in action.

Comments closed

Getting Cluster File Share Witness Sharepath via Powershell

Tom Collins needs some information:

I normally use the Windows reg utility to get the  Cluster File Share Witness sharepath information. This is an example command line on a server returning the File Share Witness details

reg query \\myCluster\HKEY_LOCAL_MACHINE\Cluster\Resources /s /f sharepath

I want to start integrating these sorts of tasks into automated information gathering procedures and migrate this task into the Powershell library. Could you share some Powershell code to get the Cluster File Share Witness sharepath  information

Click through for a Powershell one-liner which gets this information.

Comments closed

Debugging Powershell Modules in Visual Studio Code

David Wilson takes us through debugging Powershell code in Visual Studio Code:

Microsoft has a created a great Powershell extension for VS Code that makes it easy to work with Powershell files. Once the plugin is installed you get intellisense, syntax highlighting, and even visual debugging for files that have a Powershell extension. This debugger can be easily started by pressing F5 when you’re in a Powershell script. Stopping execution on a particular line is as easy as setting a breakpoint by clicking next to a line number.

If you’re coming at debugging from Visual Studio, the VS Code is a little more complicated than what’s in Visual Studio, at least in terms of things to set up first. Once you’ve tried it out a couple of times, though, it’s a pretty convenient experience.

Comments closed

Downloading a Report Authored in Browser as PBIX

James Bartlett solves a tricky problem:

Most of you have probably run into a situation where someone in your organization has authored a report in the Power BI web service, and now they want to make changes that can only be done with Power BI Desktop. So, you try to download the PBIX file from the Power BI web service, only to discover that you can’t, because if it wasn’t created as a PBIX, it can’t be downloaded as a PBIX. Infuriating!

Read on for a solution as well as a Powershell function to make it easier.

Comments closed