Setting The Database Owner, Compatibility Level, And Recovery Model With PowerShell

One of the great things about dbatool is that it allows you to run commands against multiple things. Sometimes you may need to run the same command on every server, or every database on a server, or every database on every server (not recommended). Today we’re going to look at some simple examples of how you might use that.

Database Settings

There may be times that you want to ensure certain settings are applied to a database, or multiple databases. For example, if you restore a Production database to a QA environment, you may need to change the Recovery Model. Or if you’re migrating databases to a new SQL Server version you want to make sure to update the Compatibility Level. With dbatools this is really, really easy. Here are some examples:

Setting The Database Owner

A lot of people don’t realize, or they forget, that whenever you restore a database SQL Server will automatically set the database owner to the login of whomever did the restore. This has always been a pet peeve of mine, to see random logins assigned as owners of databases all over the server. To fix this, as part of my standard restore process I always run the Set-DbaDbOwner command to reset the owner to the default ‘sa’ account. This command can be run against a single database (in the case of a restore), or every database on the server.

Single Database:

All Databases:

 

Setting The Compatibility Level

Another command you may want to run is Set-DbaDbCompatibility. This sets the compatibility level of a database or databases on your server. You can optionally remove the -TargetCompatibility parameter and it will default to the highest compatibility level of the server it is being run on.

Single Database:

All Databases:

 

Setting The Recovery Model

In most cases our Production databases use FULL Recovery Model, and our lower environment (Dev, QA, etc…) databases use SIMPLE. Guess what happens when you have a FULL Recovery Model database on a server that isn’t doing T-Log backups? The log file grows and grows and grows. That’s why I always makes sure to reset the Recovery Model when I restore a database. This is easily done using Set-DbaDbRecoveryModel

Single Database:

All Databases: