Altering database properties

This recipe shows you how to change database properties using SMO and PowerShell.

Getting ready

Create a database called TestDB by following the steps in the Creating a database recipe.

Using TestDB, we will perform the following tasks:

  • Change ANSI NULLS Enabled to False
  • Change ANSI PADDING Enabled to False
  • Change compatibility version to 110 (SQL Server 2012)
  • Restrict user access to RESTRICTED_USER
  • Set the database to Read Only

How to do it...

Let's look at the steps involved in altering databases using PowerShell:

  1. Open PowerShell ISE as administrator.
  2. Import the SQLPS module and create a new SMO Server Object:
    #import SQL Server module Import-Module SQLPS -DisableNameChecking #replace this with your instance name $instanceName ...

Get SQL Server 2014 with PowerShell v5 Cookbook now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.