{"id":4799,"date":"2012-10-20T00:01:00","date_gmt":"2012-10-20T00:01:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2012\/10\/20\/weekend-scripter-use-powershell-to-work-with-sql-server-2012-database-objects\/"},"modified":"2012-10-20T00:01:00","modified_gmt":"2012-10-20T00:01:00","slug":"weekend-scripter-use-powershell-to-work-with-sql-server-2012-database-objects","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/weekend-scripter-use-powershell-to-work-with-sql-server-2012-database-objects\/","title":{"rendered":"Weekend Scripter: Use PowerShell to Work with SQL Server 2012 Database Objects"},"content":{"rendered":"

Summary<\/b>: Ed Wilson, the Microsoft Scripting Guy, shows you how to use the SQL Server 2012 provider for Windows PowerShell to work with database objects.<\/p>\n

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.<\/p>\n

Note<\/b>   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<\/a>, <\/i>I introduced the SQL Server 2012 provider for Windows PowerShell. In the second article, Use PowerShell to Work with the SQL Server Object<\/a>, <\/i>I talked about the properties and methods that become available once you store the instance in a variable.<\/p>\n

Working with the PowerShell provider database node<\/h2>\n

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<\/a>, 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<\/b> (dir<\/b> <\/i>is the alias) cmdlet to retrieve database information. This technique is shown here.<\/p>\n

\"Image<\/a><\/p>\n

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. <\/i><\/p>\n

<\/i>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<\/b> <\/i>that has a root location based upon the databases node of instance_a on server SQL1.<\/i><\/p>\n

New-PSDrive -Name db -PSProvider sqlserver -Root SQLSERVER:\\sql\\sql1\\instance_a\\databases<\/p>\n

To remove a PSDrive, use the Remove-PSDrive<\/b> cmdlet as shown here.<\/p>\n

Remove-PSDrive db<\/p>\n

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

\"Image<\/a><\/p>\n

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.<\/p>\n

Working with databases through the databases collection node<\/h2>\n

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

[sql1]: PS db:\\> dir | select name<\/p>\n

Name<\/p>\n

—-<\/p>\n

Northwind<\/p>\n

pubs<\/p>\n

ReportServer$INSTANCE_A<\/p>\n

ReportServer$INSTANCE_ATempDB<\/p>\n

I use the Get-Item<\/b> cmdlet to retrieve a single database. I can examine the properties of the database by using the Format-List<\/b> (fl <\/b>is an alias) cmdlet.<\/p>\n

$pubs = Get-Item pubs<\/p>\n

$pubs | fl *<\/p>\n

The database object supplies a lot of properties as shown here.<\/p>\n

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

ActiveConnections                          ActiveDirectory<\/p>\n

AnsiNullDefault                            AnsiNullsEnabled<\/p>\n

AnsiPaddingEnabled                         AnsiWarningsEnabled<\/p>\n

ApplicationRoles                           ArithmeticAbortEnabled<\/p>\n

Assemblies                                 AsymmetricKeys<\/p>\n

AutoClose                                  AutoCreateStatisticsEnabled<\/p>\n

AutoShrink                                 AutoUpdateStatisticsAsync<\/p>\n

AutoUpdateStatisticsEnabled                AvailabilityDatabaseSynchronizationState<\/p>\n

AvailabilityGroupName                      BrokerEnabled<\/p>\n

CaseSensitive                              Certificates<\/p>\n

ChangeTrackingAutoCleanUp                  ChangeTrackingEnabled<\/p>\n

ChangeTrackingRetentionPeriod              ChangeTrackingRetentionPeriodUnits<\/p>\n

CloseCursorsOnCommitEnabled                Collation<\/p>\n

CompatibilityLevel                         ConcatenateNullYieldsNull<\/p>\n

ContainmentType                            CreateDate<\/p>\n

DatabaseAuditSpecifications                DatabaseEncryptionKey<\/p>\n

DatabaseGuid                               DatabaseOptions<\/p>\n

DatabaseOwnershipChaining                  DatabaseSnapshotBaseName<\/p>\n

DataSpaceUsage                             DateCorrelationOptimization<\/p>\n

DboLogin                                   DefaultFileGroup<\/p>\n

DefaultFileStreamFileGroup                 DefaultFullTextCatalog<\/p>\n

DefaultFullTextLanguage                    DefaultLanguage<\/p>\n

Defaults                                   DefaultSchema<\/p>\n

EncryptionEnabled                          Events<\/p>\n

ExtendedProperties                         ExtendedStoredProcedures<\/p>\n

Federations                                FileGroups<\/p>\n

FilestreamDirectoryName                    FilestreamNonTransactedAccess<\/p>\n

FullTextCatalogs                           FullTextStopLists<\/p>\n

HonorBrokerPriority                        ID<\/p>\n

IndexSpaceUsage                            IsAccessible<\/p>\n

IsDatabaseSnapshot                         IsDatabaseSnapshotBase<\/p>\n

IsDbAccessAdmin                            IsDbBackupOperator<\/p>\n

IsDbDatareader                             IsDbDatawriter<\/p>\n

IsDbDdlAdmin                               IsDbDenyDatareader<\/p>\n

IsDbDenyDatawriter                         IsDbManager<\/p>\n

IsDbOwner                                  IsDbSecurityAdmin<\/p>\n

IsDesignMode                               IsFederationMember<\/p>\n

IsFullTextEnabled                          IsLoginManager<\/p>\n

IsMailHost                                 IsManagementDataWarehouse<\/p>\n

IsMirroringEnabled                         IsParameterizationForced<\/p>\n

IsReadCommittedSnapshotOn                  IsSystemObject<\/p>\n

IsUpdateable                               IsVarDecimalStorageFormatEnabled<\/p>\n

LastBackupDate                             LastDifferentialBackupDate<\/p>\n

LastLogBackupDate                          LocalCursorsDefault<\/p>\n

LogFiles                                   LogReuseWaitStatus<\/p>\n

MasterKey                                  MirroringFailoverLogSequenceNumber<\/p>\n

MirroringID                                MirroringPartner<\/p>\n

MirroringPartnerInstance                   MirroringRedoQueueMaxSize<\/p>\n

MirroringRoleSequence                      MirroringSafetyLevel<\/p>\n

MirroringSafetySequence                    MirroringStatus<\/p>\n

MirroringTimeout                           MirroringWitness<\/p>\n

MirroringWitnessStatus                     Name<\/p>\n

NestedTriggersEnabled                      NumericRoundAbortEnabled<\/p>\n

Owner                                      PageVerify<\/p>\n

Parent                                     PartitionFunctions<\/p>\n

PartitionSchemes                           PlanGuides<\/p>\n

PrimaryFilePath                            Properties<\/p>\n

QuotedIdentifiersEnabled                   ReadOnly<\/p>\n

RecoveryForkGuid                           RecoveryModel<\/p>\n

RecursiveTriggersEnabled                   ReplicationOptions<\/p>\n

Roles                                      Rules<\/p>\n

Schemas                                    SearchPropertyLists<\/p>\n

Sequences                                  ServiceBroker<\/p>\n

ServiceBrokerGuid                          Size<\/p>\n

SnapshotIsolationState                     SpaceAvailable<\/p>\n

State                                      Status<\/p>\n

StoredProcedures                           SymmetricKeys<\/p>\n

Synonyms                                   Tables<\/p>\n

TargetRecoveryTime                         TransformNoiseWords<\/p>\n

Triggers                                   Trustworthy<\/p>\n

TwoDigitYearCutoff                         Urn<\/p>\n

UserAccess                                 UserData<\/p>\n

UserDefinedAggregates                      UserDefinedDataTypes<\/p>\n

UserDefinedFunctions                       UserDefinedTableTypes<\/p>\n

UserDefinedTypes                           UserName<\/p>\n

Users                                      Version<\/p>\n

Views                                      XmlSchemaCollections<\/p>\n

To work with specific properties, I use the Select-Object<\/b> 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.<\/p>\n

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

, auto*, col*, logfiles<\/p>\n

Name                        : pubs<\/p>\n

PrimaryFilePath             : C:\\Program Files\\Microsoft SQL<\/p>\n

                              Server\\MSSQL11.INSTANCE_A\\MSSQL\\DATA<\/p>\n

FileGroups                  : {PRIMARY}<\/p>\n

Size                        : 4.125<\/p>\n

SpaceAvailable              : 464<\/p>\n

IsAccessible                : True<\/p>\n

IsUpdateable                : True<\/p>\n

AutoClose                   : False<\/p>\n

AutoCreateStatisticsEnabled : True<\/p>\n

AutoShrink                  : False<\/p>\n

AutoUpdateStatisticsAsync   : False<\/p>\n

AutoUpdateStatisticsEnabled : True<\/p>\n

Collation                   : SQL_Latin1_General_CP1_CI_AS<\/p>\n

LogFiles                    : {pubs_log}<\/p>\n

That’s it for now. Join me tomorrow when I will play around with backing up the databases.<\/p>\n

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

Ed Wilson, Microsoft Scripting Guy<\/b><\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":596,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[146,3,176,375,61,45],"class_list":["post-4799","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-databases","tag-scripting-guy","tag-sql-server","tag-sql-server-2012","tag-weekend-scripter","tag-windows-powershell"],"acf":[],"blog_post_summary":"

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 […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/4799","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/596"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=4799"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/4799\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=4799"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=4799"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=4799"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}