SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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.

2017-09-14 10_50_03-Windows PowerShell ISE

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:

2017-09-14 10_55_43-Windows PowerShell ISE

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.


Filed under: Blog Tagged: dbatools, powershell, syndicated

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Comments

Leave a comment on the original post [voiceofthedba.com, opens in a new window]

Loading comments...