Set Database Option Properties with PowerShell

SQL Server databases have a lot of options you can change so that you can properly manage those databases. I'm talking about options like the recovery model, the compatibility level, etc. SQL Server Management Studio (SSMS) gives you access to all these options, but if you have a lot of databases, and you have to set each one using that interface, you can easily miss one or make mistakes due to the tedious nature of working with that interface.

Related: Gather SQL Server Instance Information with PowerShell

For many clients, I've set up processes that periodically refresh the Quality Assurance servers using backups from the production databases, but, for example, while the production databases use the FULL recovery model, the QA databases need to be set to the SIMPLE recovery model, so that backing up the transaction logs isn't necessary. In addition to automating the refresh process, I also want to automate the change to the database options appropriate for the new environment.

Set of Scripts to Change Options

To this end, I've written a set of scripts I can use to change the options individually, and I just run the set of scripts appropriate to set the options I want in each circumstance.

I've talked before about the SMO Server object, and the server has a property called Databases, which contains a collection of all the databases on the server. Each database is represented by a Database object, and that object has the options we're interested in as properties.

AutoShrink Option

We'll start with an easy one—AutoShrink. This option still exists in spite of the known problems it causes, because the shrink process causes significant fragmentation in the database, slowing down query processing. This option should be set to False to avoid the fragmentation issues.

In each script, I set up as a mandatory parameter in the opening Param block, the name of the SQL Server instance will evaluate and change. (I also add the CmdletBinding() method before the Param block to ensure that no extra parameters are provided.)

# Get the SQL Server instance name to be evaluated[CmdletBinding()]param(  # inst is the SQL Server instance being evaluated  [Parameter(Mandatory=$true)]  [string]$inst=$null  )

Once I have the instance, I can connect to that instance by instantiating a new Server object. (The code assumes you have administrative access via your Windows Login.)

# Connect to the specified instance$srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $inst

Now, I can cycle through the servers databases, check to make sure the database isn't a system database and that the AutoShrink property is set to True. If these conditions are true, then write a message with the name of the database I'm changing, reset the property to False, and alter the database. When I'm done, I write a message saying I'm done.

# Cycle through the databasesforeach ($db in $srv.Databases) {if ($db.IsSystemObject -ne $True -and $db.AutoShrink -eq $True) {$dbname = $db.Namewrite-output "Changing database $dbname to turn off AutoShrink"$db.AutoShrink = $False$db.Alter()}}write-output "Server $inst evaluation complete"

Recovery Model Option

Another option I've already mentioned is Recovery Model. For QA and Development servers I generally set this to Simple, and for production servers I generally set it to FULL. Since I usually am taking a production backup and restoring it in Dev or QA I have a standalone script that sets the option to Simple for each database.

# Cycle through the databasesforeach ($db in $srv.Databases) {if ($db.IsSystemObject -ne $True -and $db.RecoveryModel -ne 'Simple') {$dbname = $db.Namewrite-output "Changing database $dbname to set Recovery Model to Simple"$db.RecoveryModel = 'Simple'$db.Alter()}}write-output "Server $inst evaluation complete"

I hope you're noticing a pattern here.

Compatibility Level Setting

Another setting that needs more attention than it gets is Compatibility  Level. SQL Server 2008 and SQL Server 2008 R2 support Compatibility Levels 100, 90 and 80, representing SQL Server 2008/R2, SQL Server 2005 and SQL Server 2000, respectively. SQL Server 2012 supports levels 110, 100 and 90. When the Compatibility Level is set at a level less than the version of SQL Server its run under, the query processor has to go into 'emulation mode' to follow the behavior of the processor for that version. Most of the time when I see this set to lower levels, it's because database upgrades have been done and no one went in and changed the Compatibility Level of the database after it was loaded on the newer server.

Related: When Upgrading, Check Compatibility Level

To correct this, I have a script that grabs the Compatibility Level from the 'master' database, then applies that to all the remaining databases. (Make sure that you're not going to break some code that relies on the older compatibility level if you do this.)

# Get the master database CompatibilityLevel property$mstr = $srv.Databases['master']$target = $mstr.CompatibilityLevel # Cycle through the databasesforeach ($db in $srv.Databases) {if ($db.IsSystemObject -ne $True -and $db.CompatibilityLevel -ne $target) {$dbname = $db.Namewrite-output "Changing database $dbname to set CompatibilityLevel to $target"$db.CompatibilityLevel = $target$db.Alter()}}write-output "Server $inst evaluation complete"

Set Database Owner to SA

Finally, I have a script which sets the database owner to 'sa'. When you restore a database, it sets the owner to your login, and while that may not cause any problem now, should your login be disabled or removed at a later date for some reason, the database may become inaccessible. I just run my script and set the owner to 'sa', to eliminate the possibility of that happening.

Now, the Database object has an Owner property, but it's a read-only property in SMO, so you'll have to use the sp_changedbowner system procedure to make the change, and I do that by using the Invoke-SQLCMD cmdlet provided by Microsoft.

# Cycle through the databasesforeach ($db in $srv.Databases) {if ($db.IsSystemObject -ne $True -and $db.Owner -ne 'sa') {$dbname = $db.Namewrite-output "Changing database $dbname to set Owner to SA"invoke-sqlcmd -ServerInstance $inst -Database $dbname -Query "EXEC sp_changedbowner 'sa'"}}write-output "Server $inst evaluation complete"

These are the database options I find I need to set most frequently, so I keep these scripts in my toolbox, and hopefully, you'll find them equally useful.

Related: Free Tool Review: Scripted PowerShell Automated Deployment Engine