Data Management optimize

Get database properties using PowerShell in SQL Server 2008

Tim Chapman shows how you can use PowerShell scripts in SQL Server 2008 to take an inventory of database properties for SQL Server instances on your network.

Windows PowerShell functionality is embedded in SQL Server 2008. PowerShell can be invoked from SQL Server Management Studio so that you can easily take advantage of its SQL Server functionality.

PowerShell is great to use on SQL Server instances, but its real power is harnessed when you use it to administer all servers on your network. For this tutorial, I'll write a PowerShell script that loops through a list of SQL Server instances that I pull from a text file; for each database on that instance, I will run a SQL Script to output the properties for the given database. I'll also look at how to invoke SQL Server Management Objects in the example and demonstrate how easy the Invoke-SQL cmdlet is to use in PowerShell.

Note: If you're on a computer that does not have PowerShell installed, you can download the PowerShell environment.

The server list

PowerShell makes reading data from a text file and looping through its contents very easy. For our server list, we'll create a new text file in Notepad (or your text editor of choice) and write SQL Server instances in the list. For my example, I will include two database instances: Wilma and Wilma\R2Eval (Figure A). Figure A

Save this text file to your C:\ drive. We'll call it in a few minutes.

The SQL file

Now I will write a SQL script that will call the function DATABASEPROPERTY and several of its properties; this script will be called by our PowerShell script. The contents of this file are below:

DECLARE @db SYSNAME

SET @db = DB_NAME()

SELECT @db AS DatabaseName, 'IsAnsiNullDefault' AS DBProperty, DATABASEPROPERTY(@db, 'IsAnsiNullDefault') AS Value

UNION ALL

SELECT @db AS DatabaseName, 'IsAnsiNullsEnabled', DATABASEPROPERTY(@db, 'IsAnsiNullsEnabled')

UNION ALL

SELECT @db AS DatabaseName, 'IsAnsiWarningsEnabled', DATABASEPROPERTY(@db, 'IsAnsiWarningsEnabled')

UNION ALL

SELECT @db AS DatabaseName, 'IsAutoClose', DATABASEPROPERTY(@db, 'IsAutoClose')

UNION ALL

SELECT @db AS DatabaseName, 'IsAutoCreateStatistics', DATABASEPROPERTY(@db, 'IsAutoCreateStatistics')

UNION ALL

SELECT @db AS DatabaseName, 'IsAutoShrink', DATABASEPROPERTY(@db, 'IsAutoShrink')

UNION ALL

SELECT @db AS DatabaseName, 'IsAutoUpdateStatistics', DATABASEPROPERTY(@db, 'IsAutoUpdateStatistics')

UNION ALL

SELECT @db AS DatabaseName, 'IsBulkCopy', DATABASEPROPERTY(@db, 'IsBulkCopy')

UNION ALL

SELECT @db AS DatabaseName, 'IsCloseCursorsOnCommitEnabled', DATABASEPROPERTY(@db, 'IsCloseCursorsOnCommitEnabled')

UNION ALL

SELECT @db AS DatabaseName, 'IsDboOnly', DATABASEPROPERTY(@db, 'IsDboOnly')

UNION ALL

SELECT @db AS DatabaseName, 'IsDetached', DATABASEPROPERTY(@db, 'IsDetached')

UNION ALL

SELECT @db AS DatabaseName, 'IsEmergencyMode', DATABASEPROPERTY(@db, 'IsEmergencyMode')

UNION ALL

SELECT @db AS DatabaseName, 'IsFulltextEnabled', DATABASEPROPERTY(@db, 'IsFulltextEnabled')

UNION ALL

SELECT @db AS DatabaseName, 'IsInLoad', DATABASEPROPERTY(@db, 'IsInLoad')

UNION ALL

SELECT @db AS DatabaseName, 'IsInRecovery', DATABASEPROPERTY(@db, 'IsInRecovery')

UNION ALL

SELECT @db AS DatabaseName, 'IsInStandBy', DATABASEPROPERTY(@db, 'IsInStandBy')

UNION ALL

SELECT @db AS DatabaseName, 'IsLocalCursorsDefault', DATABASEPROPERTY(@db, 'IsLocalCursorsDefault')

UNION ALL

SELECT @db AS DatabaseName, 'IsNotRecovered', DATABASEPROPERTY(@db, 'IsNotRecovered')

UNION ALL

SELECT @db AS DatabaseName, 'IsNullConcat', DATABASEPROPERTY(@db, 'IsNullConcat')

UNION ALL

SELECT @db AS DatabaseName, 'IsOffline', DATABASEPROPERTY(@db, 'IsOffline')

UNION ALL

SELECT @db AS DatabaseName, 'IsParameterizationForced', DATABASEPROPERTY(@db, 'IsParameterizationForced')

UNION ALL

SELECT @db AS DatabaseName, 'IsQuotedIdentifiersEnabled', DATABASEPROPERTY(@db, 'IsQuotedIdentifiersEnabled')

UNION ALL

SELECT @db AS DatabaseName, 'IsReadOnly', DATABASEPROPERTY(@db, 'IsReadOnly')

UNION ALL

SELECT @db AS DatabaseName, 'IsRecursiveTriggersEnabled', DATABASEPROPERTY(@db, 'IsRecursiveTriggersEnabled')

UNION ALL

SELECT @db AS DatabaseName, 'IsShutDown', DATABASEPROPERTY(@db, 'IsShutDown')

UNION ALL

SELECT @db AS DatabaseName, 'IsSingleUser', DATABASEPROPERTY(@db, 'IsSingleUser')

UNION ALL

SELECT @db AS DatabaseName, 'IsSuspect', DATABASEPROPERTY(@db, 'IsSuspect')

UNION ALL

SELECT @db AS DatabaseName, 'IsTruncLog', DATABASEPROPERTY(@db, 'IsTruncLog')

UNION ALL

SELECT @db AS DatabaseName, 'Version', DATABASEPROPERTY(@db, 'Version')

Copy the script above into Notepad and save the script as DatabasePropertiesSQLScript.txt on your C:\ drive.

PowerShell script

Now let's define our PowerShell script. In this script, I'll need to invoke a SQL Server Management Objects (SMO) object so that I am able to loop through the databases on our SQL Server instances. In the first line of the script, I load the SMO assembly so I can make use of its objects.

In the second line, I load the contents of our InstanceList.txt file into an object named $servers. Once I've loaded the object with the server list, I am able to loop through these servers using a foreach loop.

In the next line, I am creating a new SMO server object and assigning it to the $sqlserver object. Notice that I am passing the name of the server to this function call; I am doing this so I can tell SMO which instance name I want my $sqlserver object to represent. Once the SMO server object has been defined, it exposes a Databases object, which is an enumeration of the databases present on the SQL Server instance. I can take advantage of the Databases property to loop through each of these databases on the instance.

As I loop through each database, I call a SQL Server specific PowerShell cmdlet named invoke-sqlcmd. This command allows you to run a SQL Server command against a specific SQL Server instance and database; it also allows you to run a SQL Script from a file. This is where the SQL script that contains our DATABASEPROPERTY calls comes into play. I am now able to dynamically pass the server and database names into this command and call the SQL script for each database that we loop through.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null

$servers = get-content c:\InstanceList.txt

foreach($server in $servers)

{

$sqlserver = new-object "Microsoft.SqlServer.Management.Smo.Server" $server

foreach ($db in $sqlserver.Databases)

{

invoke-sqlcmd -inputfile c:\DatabasePropertiesSQLScript.txt -database $db.name -ServerInstance $sqlserver.name -IgnoreProviderContext

}

}

Copy the above script and paste into a text editor. Save this file to your C:\ drive under the name PSLooperScript.ps1.

Now that all of our scripts are defined, we need to run them. To run the scripts, follow these steps:

  1. Open SQL Server Management Studio 2008.
  2. Right-click a server instance and select Start PowerShell. This opens a command-type window where you can enter PowerShell commands. Since we've defined our PowerShell scripts in a file, all we need to do is call the file from the interface.
  3. At the PowerShell prompt, type the location of the PowerShell script you just saved and hit [Enter].

C:\PSLooperScript.ps1

If everything works properly, you'll see a long list of databases and their properties in your PowerShell window.

Summary

In this tutorial, we used PowerShell and SMO to loop through a list of SQL Server instances from a text file. We then looped through each database on the SQL Server instance and ran a SQL Server script that output the properties for the given database.

This simple example shows how powerful it can be to use PowerShell with SQL Server. This script could easily be built upon to write scripts for administering your SQL Servers, gathering instance-related information, and handling security or policies on your SQL Server 2008 instances.

TechRepublic's Servers and Storage newsletter, delivered on Monday and Wednesday, offers tips that will help you manage and optimize your data center. Automatically sign up today!

About

Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.

0 comments