Weekend Scripter: Use PowerShell to Work with SQL Server 2012 Database Objects

Summary: Ed Wilson, the Microsoft Scripting Guy, shows you how to use the SQL Server 2012 provider for Windows PowerShell to work with database objects.

Microsoft Scripting Guy, Ed Wilson, is here. Today I want to continue my series of articles that explore the SQL Server 2012 provider for Windows PowerShell.

Note   This is the third article in a series of articles discussing using Windows PowerShell to manage SQL Server 2012. You should read the first two articles in this series prior to reading this article. In the first article, Introduction to the SQL Server 2012 PowerShell Provider, I introduced the SQL Server 2012 provider for Windows PowerShell. In the second article, Use PowerShell to Work with the SQL Server Object, I talked about the properties and methods that become available once you store the instance in a variable.

Working with the PowerShell provider database node

Once I connect to the Databases node, I gain access to a number of really cool properties and methods. These are instances of the Database class, and it is one of the more important objects available through SMO. To access the Databases note, I navigate to SQL / Server Name / Instance Name / Databases. When I arrive at the Databases node, I use the Get-ChildItem (dir is the alias) cmdlet to retrieve database information. This technique is shown here.

All this navigating is a bit annoying if I want to work specifically with the databases node. In addition to the work of bouncing around to get to the databases node, there is also the fact that over half of my Windows PowerShell console prompt is missing because of the long path. To solve these issues, I use a standard Windows PowerShell technique of creating a PSDrive.

A PSDrive works like any other Windows PowerShell drive in that I can use the same cmdlets I have been using. The advantage is that it drops me directly to the node with which I want to work—so it is convenient. A second advantage is that I regain nearly all of my Windows PowerShell console prompt. The command that follows creates a PSDrive named db that has a root location based upon the databases node of instance_a on server SQL1.

New-PSDrive -Name db -PSProvider sqlserver -Root SQLSERVER:\sql\sql1\instance_a\databases

To remove a PSDrive, use the Remove-PSDrive cmdlet as shown here.

Remove-PSDrive db

The image that follows shows creating a new PSDrive named db, changing the working location to that newly created drive, and then using the get-ChildItem cmdlet (dir is the alias) to obtain a list of the databases.

If you work with SQL Server databases on a regular basis, you may want to create a PSDrive similar to the one shown above in your Windows PowerShell profile.

Working with databases through the databases collection node

To obtain a list of the databases residing on instance_a of my SQL1 server, I use the Get-ChildItem cmdlet (dir is the alias) and select the name of each database as shown here.

[sql1]: PS db:\> dir | select name

Name

—-

Northwind

pubs

ReportServer$INSTANCE_A

ReportServer$INSTANCE_ATempDB

I use the Get-Item cmdlet to retrieve a single database. I can examine the properties of the database by using the Format-List (fl is an alias) cmdlet.

$pubs = Get-Item pubs

$pubs | fl *

The database object supplies a lot of properties as shown here.

[sql1]: PS db:\> $pubs | gm -mem property | fw -aut

ActiveConnections                          ActiveDirectory

AnsiNullDefault                            AnsiNullsEnabled

AnsiPaddingEnabled                         AnsiWarningsEnabled

ApplicationRoles                           ArithmeticAbortEnabled

Assemblies                                 AsymmetricKeys

AutoClose                                  AutoCreateStatisticsEnabled

AutoShrink                                 AutoUpdateStatisticsAsync

AutoUpdateStatisticsEnabled                AvailabilityDatabaseSynchronizationState

AvailabilityGroupName                      BrokerEnabled

CaseSensitive                              Certificates

ChangeTrackingAutoCleanUp                  ChangeTrackingEnabled

ChangeTrackingRetentionPeriod              ChangeTrackingRetentionPeriodUnits

CloseCursorsOnCommitEnabled                Collation

CompatibilityLevel                         ConcatenateNullYieldsNull

ContainmentType                            CreateDate

DatabaseAuditSpecifications                DatabaseEncryptionKey

DatabaseGuid                               DatabaseOptions

DatabaseOwnershipChaining                  DatabaseSnapshotBaseName

DataSpaceUsage                             DateCorrelationOptimization

DboLogin                                   DefaultFileGroup

DefaultFileStreamFileGroup                 DefaultFullTextCatalog

DefaultFullTextLanguage                    DefaultLanguage

Defaults                                   DefaultSchema

EncryptionEnabled                          Events

ExtendedProperties                         ExtendedStoredProcedures

Federations                                FileGroups

FilestreamDirectoryName                    FilestreamNonTransactedAccess

FullTextCatalogs                           FullTextStopLists

HonorBrokerPriority                        ID

IndexSpaceUsage                            IsAccessible

IsDatabaseSnapshot                         IsDatabaseSnapshotBase

IsDbAccessAdmin                            IsDbBackupOperator

IsDbDatareader                             IsDbDatawriter

IsDbDdlAdmin                               IsDbDenyDatareader

IsDbDenyDatawriter                         IsDbManager

IsDbOwner                                  IsDbSecurityAdmin

IsDesignMode                               IsFederationMember

IsFullTextEnabled                          IsLoginManager

IsMailHost                                 IsManagementDataWarehouse

IsMirroringEnabled                         IsParameterizationForced

IsReadCommittedSnapshotOn                  IsSystemObject

IsUpdateable                               IsVarDecimalStorageFormatEnabled

LastBackupDate                             LastDifferentialBackupDate

LastLogBackupDate                          LocalCursorsDefault

LogFiles                                   LogReuseWaitStatus

MasterKey                                  MirroringFailoverLogSequenceNumber

MirroringID                                MirroringPartner

MirroringPartnerInstance                   MirroringRedoQueueMaxSize

MirroringRoleSequence                      MirroringSafetyLevel

MirroringSafetySequence                    MirroringStatus

MirroringTimeout                           MirroringWitness

MirroringWitnessStatus                     Name

NestedTriggersEnabled                      NumericRoundAbortEnabled

Owner                                      PageVerify

Parent                                     PartitionFunctions

PartitionSchemes                           PlanGuides

PrimaryFilePath                            Properties

QuotedIdentifiersEnabled                   ReadOnly

RecoveryForkGuid                           RecoveryModel

RecursiveTriggersEnabled                   ReplicationOptions

Roles                                      Rules

Schemas                                    SearchPropertyLists

Sequences                                  ServiceBroker

ServiceBrokerGuid                          Size

SnapshotIsolationState                     SpaceAvailable

State                                      Status

StoredProcedures                           SymmetricKeys

Synonyms                                   Tables

TargetRecoveryTime                         TransformNoiseWords

Triggers                                   Trustworthy

TwoDigitYearCutoff                         Urn

UserAccess                                 UserData

UserDefinedAggregates                      UserDefinedDataTypes

UserDefinedFunctions                       UserDefinedTableTypes

UserDefinedTypes                           UserName

Users                                      Version

Views                                      XmlSchemaCollections

To work with specific properties, I use the Select-Object cmdlet to retrieve just the interesting properties. I can use wildcard characters in the property names (some of which are very long) and, therefore, return one or more properties with only a few characters. Some of the properties that interest me are the location of the log files and file groups as well as the size of the database. I also like to check a few other properties at this time as well. The command and associated output are shown here.

[sql1]: PS db:\> $pubs | select name, primary*, filegroups, size, space*, isac*,isup*

, auto*, col*, logfiles

Name                        : pubs

PrimaryFilePath             : C:\Program Files\Microsoft SQL

                              Server\MSSQL11.INSTANCE_A\MSSQL\DATA

FileGroups                  : {PRIMARY}

Size                        : 4.125

SpaceAvailable              : 464

IsAccessible                : True

IsUpdateable                : True

AutoClose                   : False

AutoCreateStatisticsEnabled : True

AutoShrink                  : False

AutoUpdateStatisticsAsync   : False

AutoUpdateStatisticsEnabled : True

Collation                   : SQL_Latin1_General_CP1_CI_AS

LogFiles                    : {pubs_log}

That’s it for now. Join me tomorrow when I will play around with backing up the databases.

I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

Ed Wilson, Microsoft Scripting Guy