Continuing with my little SQL and PowerShell series, I am going to talk about how you can use PowerShell to explore SQL databases using SMO.
As shown in the previous articles, I will make my initial connection to a SQL server.
## Add Assemblies
add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0,
Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral,
PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
add-type -AssemblyName "Microsoft.SqlServer.SMOExtended, Version=10.0.0.0,
Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
add-type -AssemblyName "Microsoft.SqlServer.SqlEnum, Version=10.0.0.0,
Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
add-type -AssemblyName "Microsoft.SqlServer.Management.Sdk.Sfc, Version=10.0.0.0,
Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
## Connect to SQL Server
$sqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server('Boe-PC\SHIRE')
Viewing all of the databases and their properties is as simple as doing this:
$sqlServer.Databases
Of course, you get EVERYTHING with this. It may be better to filter out only the properties I need.
##Databases $sqlServer.Databases | Format-Table Name,Owner,Status,LastBackupDate, LastDifferentialBackupDate,LastLogBackupDate, RecoveryModel,DataSpaceUsage -AutoSize
Much better. Here we can see some relevant information such as when backups have last taken place as well as the recovery model for each database among other things. Of course, you can choose what you feel like are the most important things to view.
Typically when working with objects and you want to only get a specific item from the collection, you would need to do a Where-Object and filter for a specific property (Name in this case). Luckily, you don’t have to do this at all. You can just run the following command to grab a lone database.
$db = $sqlServer.Databases['AdventureWorks'] $db.name
Now I have access to a single database to work with and view other interesting things on it.
Something that I think is really cool is that you can generate the TSQL code to create the database just by using the Script() method.
#Show Database TSQL Script $db.Script()
Pretty cool stuff if you wanted to create another database somewhere else.
Finding all of the users that have logins added to a specific database can be found on the Users property. Within that object is a method called EnumRoles() which will also show you what roles each user is in.
#Show Users with their roles
$db.Users | ForEach {
[pscustomobject] @{
Name = $_.Name
Login = $_.Login
LoginType = $_.LoginType
UserType = $_.UserType
HasDBAccess = $_.HasDBAcces
CreateDate = $_.CreateDate
Database = $_.Parent
Roles = $_.EnumRoles()
}
} | Format-Table -AutoSize
This provides a nice way to track who has access to what databases on your SQL Server.
In the flip side, you can also look at the available roles on the database.
#Roles
$db.Roles | ForEach {
[pscustomobject] @{
Name = $_.name
Database = $_.Parent
CreateDate = $_.CreateDate
LastModified = $_.DateLastModified
Owner = $_.Owner
Users = $_.EnumMembers()
}
} | Format-Table -AutoSize
If you want to check out the schemas on the database, you can run this.
#Look at schemas $db.Schemas | FT Name,Parent,Owner -AutoSize
Let’s take a look at the permissions on the database.
#Database Permissions $db.EnumDatabasePermissions() | Format-Table Grantee,GranteeType,PermissionType, PermissionState,Grantor -AutoSize
We can also look at any locks that are on the database using the EnumLocks() method.
#Database Locks $db.EnumLocks() | Format-Table -AutoSize
Depending on the database, you may have some database logon mappings.
$sqlServer.Databases.EnumLoginMappings()
You can check to see if any transactions are running and then view those transactions on the database.
If ($db.GetTransactionCount() -gt 0) {
$db.EnumTransactions()
}
Depending on what is happening, you may or may not get anything. In my case, I ran the following command to ‘monitor’ transactions to show what is happening when I run a simple TSQL command to inserta row.
while ($True) {$db.EnumTransactions()}
You can view all of the objects on a database using the EnumObjects() method.
$db.EnumObjects()
Yes, there will be a lot of objects returned when you run this command.
If you want to know the size of your data files (both .mdf and .ldf) as well as what their growth rate might be, then the following lines of code will be pretty useful for you.
First off is the database file/s. First we can check out the FileGroups property to see how many file groups we have associated with this database.
$db.FileGroups
Only one file group to work with here. We can dig deeper by looking at the Files property to see more information about the database file.
$db.FileGroups.Files
Here you can see the actual size of the file (Size) and the size actually being used by the database (UsedSpace). Also, you are able to see more information like its growth rate. Lets do some formatting to make this a little easier to read.
$db.FileGroups.Files | Select FileName,Name,
@{L='FileSizeMB';E={[math]::Round(($_.Size * 1024)/1MB,2)}},
@{L='DatabaseSizeMB';E={[math]::Round(($_.UsedSpace * 1024)/1MB,2)}},
MaxSize,@{L='PhysicalDiskFreeSpaceMB';E={[math]::Round(($_.VolumeFreeSpace * 1024)/1MB,2)}},
Growth,GrowthType | Format-Table -AutoSize
Here you can see where the file is located at, the size of the file (4MB) as well as how much of that size is actually being used by the database (2.88MB). The max size is –1, basically meaning that this thing is a goldfish and will grow to the size of the bowl (or drive in this case) if allowed. We can also see that this will grow by 10% each time its DatabaseSizeMB out-grows the FileSizeMB. So it may be a good idea to not only give it a maxsize but to also change the growth rate on this database to prevent a potential disaster.
Not to be left out, we can also check out the transaction log size in a similar way by looking at the LogFiless property of the database.
$db.LogFiles
Much like the database files, lets format it a little.
$db.LogFiles | Select FileName,Name,
@{L='FileSizeMB';E={[math]::Round(($_.Size * 1024)/1MB,2)}},
@{L='TLogSizeMB';E={[math]::Round(($_.UsedSpace * 1024)/1MB,2)}},
MaxSize,@{L='PhysicalDiskFreeSpaceMB';E={[math]::Round(($_.VolumeFreeSpace * 1024)/1MB,2)}},
Growth,GrowthType | Format-Table -AutoSize
You can even run TSQL using the ExecuteWithResults() method. In this case I want to see all of the tables on this database.
$db.ExecuteWithResults('SELECT * FROM INFORMATION_SCHEMA.TABLES').Tables.Rows
I will be finishing up this article by showing you how you can create a database, make some configuration changes, verify it exists and then removing the database from the SQL server.
First, lets create a database on the SQL server..
#Create a database quickly
([Microsoft.SqlServer.Management.Smo.Database]@{
Name ='ProxTest'
Parent = $sqlServer
}).Create()
Now we can make some configuration changes to this database. But first we need to show the settings prior to making the change.
$newDb | Format-Table Name,Owner,Auto*Statistics* -AutoSize
Now we can make some changes.
$newDB.SetOwner("boe-pc\Administrator")
$newDB.AutoCreateStatisticsEnabled = $True
$newDB.AutoUpdateStatisticsAsync = $True
$newDB.AutoUpdateStatisticsEnabled = $True
$newDB.Alter()
The Alter() is important because if I don’t call it, no changes will be made to the database. With that, lets check out those properties again.
$newDb | Format-Table Name,Owner,Auto*Statistics* -AutoSize
As you can see, the properties have been updated with a new owner as well as some statistics options.
Now, I really don’t need this database, so lets use the Drop() method to remove the database.
$newDB.Drop() $sqlServer.Databases.name
No more database!
That is all for this article on exploring the database using SMO. The next article will talk about backing up and restoring a database using SMO.
Learn Powershell | Achieve More 