In the series of articles on DBATools, (see TOC at the bottom) we are exploring useful DBATools command to perform administrative SQL tasks. In this article, we will explore useful commands to interact with SQL Server.
We explored installation and performing database backups, restoration, and validation with DBATools.
Get-DbaDatabase
We use the Get-DbaDatabase command to get SQL database information for each database on the specified SQL instance. Usually, we use SSMS to check the databases information. It is useful to learn to fetch this information using PowerShell. DBATools provides a set of commands to do routine work easily.
- Note: I am using Azure Data Studio to execute DBATools PowerShell Commands.
The Syntax for Get-DbaDatabase
|
1 |
> Get-help Get-DbaDatabase |
We need to use parameters to get the required data. Let’s explore Get-DbaDatabase commands with examples.
Example 1: Get all databases details in a specified SQL instance
|
1 |
> Get-DbaDatabase -SqlInstance localhost\SQL2019CTP |
It gives output for all databases in the specified SQL instance. If we have a large number of databases, it is best to use the output in a Grid format.
We get useful information such as Database name, status, recovery model, LogReuseWaitStatus, database size in MB, compatibility model, collation, backup details.
|
1 |
> Get-DbaDatabase -SqlInstance localhost\SQL2019CTP | Out-GridView |
It opens a new result window and displays results in an interactive format. You can drag and drop columns to display results in a particular order.
We can use filters in grid format to get desired results. Click on Add Criteria and select the column to filter the results. For example, in the following image, you can see result filter for the ApexSQLMonitor database.
Example 2: Get only system databases details in a specified SQL instance
We can use the parameter -ExcludeUser to exclude all user databases and give output for only system databases (Master, Model, MSDB and TempDB).
|
1 |
> Get-DbaDatabase -SqlInstance localhost\SQL2019CTP -ExcludeUser | Out-GridView |
Example 3: Get only user databases details in a specified SQL instance
We might want to get details of only user databases. We can use the parameter –ExcludeUser to exclude system databases in the output.
Example 4: Get databases details along with last used information in a specified SQL instance
We use dynamic management view sys.dm_db_index_usage_stats to get overall access details about indexes in SQL Server.
The useful columns in the output of this DMV are as follows:
- Last_user_scan – It is the timestamp of the last user scan for an index
- Last_user_update- It is the timestamp of the last user update for an index
Let’s execute this command in Azure Data Studio for the AdventureWorks2014 database.
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], I.[NAME] AS [INDEX NAME], USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES FROM SYS.DM_DB_INDEX_USAGE_STATS AS S INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID WHERE OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1 |
In the following screenshot, we can see last_user_scan and last_user_update values.
Suppose we want to get database information using DBATools command Get-DbaDatabase along with the last read & write times for each database in the specified SQL instance. We can use the parameter -IncludeLastUsed to include this information.
- Note: We have multiple tables in an SQL database. All tables indexes might have different last scan and update dates. DBATools performs a check for all indexes in all databases and returns the latest timestamp of user access and update activity.
|
1 |
> Get-DbaDatabase -SqlInstance localhost\SQL2019CTP -IncludeLastUsed | Out-GridView |
It includes LastIndexRead and LastIndexWrite columns to give the required information for each database. You can compare the output from DBATools command Get-DbaDatabase and output of the DMV sys.dm_db_index_usage_stats.
Let’s execute an update command on AdventureWorks204 database.
|
1 |
Update [AdventureWorks2017].[dbo].[DemoTable] set product=1111 |
Rerun the DBATools command, and you can see updated values because it gets the real-time values from the DMV.
Example 5: Get Read-only database details a specified SQL instance
We might have set a database to Read-only to disable any further updates. We can filter results for the read-only database using -Access ReadOnly parameter.
I do not have a read-only database in my instance. Due to this, the command did not return any output.
Connect to a database, right-click on it and open properties. In the options page, change the state in column Database Read-only as True.
We need to close all connections to the database to change in a database state. Click on Yes to go ahead with the change and close all other connections.
Once database state changes to Read-only, you can see a suffix Read-only in SSMS for that particular database.
Rerun the command of DBATools Get-DbaDatabase with parameter -Access ReadOnly. We changed the database status to read-only for the SQLDB database. We get the information about it using DBATools as well.
Example 6: Command to get databases information having status Normal using DBATools Get-DbaDdatabase command
We might have different states of databases in SQL Server. You can understand all database states (mentioned below) using this article, Understanding different SQL Server database states.
- Online
- Restoring
- Recovering
- Recovery pending
- Suspect
- Emergency
- Offline
As of now, we have all databases in online status. For the demonstration purpose, let’s change a database status from Online to Restoring.
To change the status of the SQLDB database, I take a tail-log backup, and it changes the state to Restoring.
To take a tail-log backup, right click on a database and go to tasks, backup. In the backup wizard, go to Media and select the option – backup the tail of the log and leave the database in the restoring state.
I have already taken a tail-log backup. Therefore, it shows it greyed out in the following screenshot.
Alternatively, you can execute the following backup log command.
|
1 2 |
BACKUP LOG [SQLDB] TO DISK = N'C:\TEMP\SQLDB.bak' WITH NO_TRUNCATE , NOFORMAT, NOINIT, NAME = N'SQLDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, NORECOVERY , STATS = 10 GO |
Once the tail-log backup completes, you can see the database in the restoring mode.
Execute the following DBATools command to include only databases having Normal status.
|
1 |
> Get-DbaDatabase -SqlInstance localhost\SQL2019CTP -Status Normal | Out-GridView |
In the following output, we do not have SQLDB database that is in Restoring database state.
If we want to get detailed about database having database state Restoring, we can execute the following command.
|
1 |
> Get-DbaDatabase -SqlInstance localhost\SQL2019CTP -Status Restoring | Out-GridView |
Now, we only get one database SQLDB in the output because it is in Restoring mode as shown below.
Example 7: Get a database having a specific recovery model using DBATools Get-DbaDdatabase command
We can specify different recovery models for separate databases in a SQL instance depending upon the recovery objectives, backup configuration and criticality of a database. The available recovery models in SQL Server as follows:
- Full
- Bulk-logged
- Simple
Suppose we want to get details of databases having a Full recovery model. We can use -RecoveryModel parameter to specify a recovery model. DBATools command checks for the specified recovery model and returns only those databases meeting the criteria.
|
1 |
> Get-DbaDatabase -SqlInstance localhost\SQL2019CTP -RecoveryModel FULL | Out-GridView |
In the following screenshot, we can see the database having a FULL recovery model.
We can specify multiple values as well with -RecoveryModel parameter. For example, if we want to get details of databases having FULL and Simple recovery model, execute the following command.
|
1 |
> Get-DbaDatabase -SqlInstance localhost\SQL2019CTP -RecoveryModel FULL,Simple | Out-GridView |
Example 8: Exclude specific database in DBATools Get-DbaDatabase command
In earlier examples 2 and 3, we excluded system and user databases in the Get-DbaDatabase command. We might want to exclude a specific database check. For example, I do not want to check the details of the TempDB database and want to exclude this in the output. We can use the -ExcludeDatabase parameter to specify an excluded database.
|
1 |
> Get-DbaDatabase -SqlInstance localhost\SQL2019CTP -Excludedatabase TempDB | Out-GridView |
In the output, we can verify that the tempdb database is not present in the output.
We can exclude multiple databases at the same time using the parameter –Excludedatabase. Specify database names separated by a comma.
In the following command, we want to exclude three databases TempDB, SQLDB and SQLTemp1.
|
1 |
> Get-DbaDatabase -SqlInstance localhost\SQL2019CTP -Excludedatabase TempDB,SQLDB,SQLTemp1 | Out-GridView |
In the output, you can see that we do not have TempDB, SQLDB and SQLTemp1 databases in the output of Get-DbaDatabase command.
Conclusion
In this article, we learned to fetch SQL Database information using DBATools PowerShell tool. I will cover more DBATools commands in this series of articles. If you had comments or questions, feel free to leave them in the comments below.
Table of contents
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023