Voice of the DBA

Tag: powershell

Using Backup-DbaDatabase for a Quick Backup

I really like the dbatools project. This is a series of PowerShell cmdlets that are built by the community and incredibly useful for migrations between SQL Servers, but also for various administrative actions. I have a short series on these items.

One of the core tasks of a DBA is backing up a database. In fact, I’d argue that it’s the most important thing for a DBA to know. Second would be restores.

I’ve used the BACKUP DATABASE command so often from T-SQL that it’s a quick way for me to just get a backup of a database. I almost always have SSMS running, so I can easily just run the backup.

For regular backups there are some great tools out there, SQL Backup Pro from Redgate, Minion Backup, Ola Hallengren’s scripts, and more. However, there still might be a time I want to make a few backups, perhaps copy them over, and that’s where I think dbatools and Backup-DbaDatabase might help.

This is a nice, easy cmdlet to use. You can probably guess how to use it. Give it an instance, a database (or few), include a path, maybe create folders for each database, and let it go.

That’s about it.

If I run this interactively, I’d get the progress bar:

$2017-10-02 17_04_29-{15%} cmd - powershell (Admin)$

When the command is done, I see the results from each database. I get the file, folder, full path, and the script used.

I could include a different path if I wanted, and certainly I can chain this along with other PoSh commands.

I don’t know that I’d use this often, but it is handy in places, and certainly if I want to script the movement of some files, perhaps for something like HA/DR testing or setup, or even to refresh other environments.

October 16, 2017
Get-DbaPowerShell

When I first saw PowerShell, at TechEd 2006 (ish) and I was enamored. This was a much better environment than VBScript for working in a shell. Then I realized I needed -eq, -gt, etc. and was less excited. These language elements felt like a step back. Then I tried to build a restore script for SQL Server databases early on and was much less excited.

Across the years, I’ve played with various tasks in PoSh as opposed to T-SQL or simple command scripts in various languages, and I find myself going back and forth. PoSh is very useful in some ways, more cumbersome in others, and I still am not sure exactly how I feel about it as a go-to tool. I am coming around to use more PoSh because of the dbatools project, and I find myself considering PoSh instead of T-SQL, especially when I think I may want to work across instances and string together commands.

I was quite excited recently by T-SQL Tuesday #94, hosted by Rob Sewell. The topic was PoSh, and quite a few people participated, as you can see from the Get-PostRoundup. There are some creative uses of PoSh, such as importing Excel data, administering SSRS, turning Azure VMs off, cleaning up orphaned files, and more. Personally, once you start to use Azure more, PoSh makes the portal seem cumbersome and slow, and I find that for recurring tasks, it’s a much better tool.

Recently I saw the DBAfromtheCold write a short piece on why you should learn PoSh. It’s a good look at some of the tasks that are easy through PowerShell. Some of these could be done in T-SQL, some with other scripts, but PoSh certainly is a possibility and an easy way to manage some of the tasks you might need to do at scale.

I don’t think PoSh is the best way to do everything. In fact, I often still lean on T-SQL for many admin things, especially if I already have a connection open in SSMS. However, as I learn more about PoSh and how to use it, I can make those judgment calls about when it’s a better fit. That knowledge helps me become a more capable and stronger data professional, because I have choices about how to work with systems, and I have some knowledge or basis for the decisions I make.

I advocate continuing to learn throughout your career, and certainly including PoSh as a topic if you work on the Microsoft platforms.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 4.0MB) podcast or subscribe to the feed at iTunes and Libsyn.

October 2, 2017
Test Your SQL Memory Setting with dbatools

I really like the dbatools project. This is a series of PowerShell cmdlets that are built by the community and incredibly useful for migrations between SQL Servers, but also for various administrative actions. I have a short series on these items.

One of the settings that has caused me problems at times is the max memory setting for a SQL Server. Overall, I like to have dedicated hosts for SQL Server and may not care about limiting SQL Server in way way. However, there are places where I may have multiple instances, or other applications, and I should be setting memory to some level.

Jonathan Kehayias wrote a post about calculating max memory, and that was the inspiration for Test-DbaMaxMemory. This is a great little resource that you may use rarely, but it saves some time and makes the memory check quick and easy.

It’s a simple command, with a parameter for the instance. The Format-Table is a great place to pipe this output. For one of my instances, I see:

Not great, since I have other instances running. I wouldn’t want this to take over my workstation. My other instance is a little better configured.

There are all sorts of filters here to use with this. The doc pages shows filtering to find instances where the setting is greater than total memory. Maybe a better one might be looking for instances greater than (total memory –2GB), or 4GB. Or maybe you want some deviance from the recommended value.

You can run this with any list of servers as well, and get a nice report. Maybe even one that you can use to convince your boss that you need a bit more RAM for that busy instance.

As with the other dbatools, this fulfills a simple function. It’s quick, easy, and helpful. Give it a try today.

October 2, 2017
Test Database Compatability with dbatools

I really like the dbatools project. This is a set of PowerShell cmdlets that are built by the community and incredibly useful for migrations between SQL Servers, but also for various administrative actions. I have a short series on these items.

One of the items that few DBAs think about often is compatibility. We tend to create databases with the default level, based on the instance. If we upgrade an instance, I’ve seen many people assume the level changes with the upgrade script (it does). However, if you restore a database, or attach one, the compatibility level might not match, and that could cause issues.

The dbatools project has thought of this, and they have a great cmdlet that can help you build checks of all your databases. The Test-DbaDatabaseCompatibility cmdlet will return the databases and whether they match the server level. You can also get more details if you like. Here’s a quick view. I can run:

Test-DbaDatabaseCompatibility -SqlServer .\SQL2016

This gives me a lot of databases on this instance. In fact, they scroll off the results quickly.

I don’t really worry about those databases that match. In fact, I expect that most databases do. My rule of DBA information is often that I want to only see those items that might cause me to take some action. Here, I only want those items that don’t match.

I can easily use filtering to do this:

Of course, I could simple wrap this in a function as well, add this in a pipeline that takes a list of instances, or more. For me, I’d want each instance to check itself with this cmdlet and record the results. That way I could periodically review the list and decide if I can actually can change any of these.

I could also put a query in my SQL Monitor monitoring system as a custom metric and have this tracked there, but I wouldn’t want alerts for this. It’s not critical enough to interrupt me too often.

September 20, 2017