Rajendra Gupta
Get a database having a specific recovery model using DBATools Get-DbaDdatabase command

Get SQL Database details using DBATools

June 3, 2019 by

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

The Syntax for Get-DbaDatabase in DBATools

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

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.

Get all databases details in a specified SQL instance

We get useful information such as Database name, status, recovery model, LogReuseWaitStatus, database size in MB, compatibility model, collation, backup details.

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.

Get all databases details in a specified SQL instance in Grid format

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.

Get all databases details in a specified SQL instance and filter results

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).

Get only system databases details in a specified SQL instance

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.

Get only user databases details in a specified SQL instance

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.

In the following screenshot, we can see last_user_scan and last_user_update values.

Output of DMV to get use details

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.

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.

IncludeLastUsed paramters in DBATools command Let’s execute an update command on AdventureWorks204 database.

Rerun the DBATools command, and you can see updated values because it gets the real-time values from the DMV.

Get output of DBATools with IncludeLastUsed

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.

Get Read-only database details a specified SQL instance

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.

SSMS option to modify DB properties

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.

Warning message of open connections

Once database state changes to Read-only, you can see a suffix Read-only in SSMS for that particular database.

Database status as Read-only

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. -Access ReadOnly parameter to get read-only database details

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.

Tail-log backup

Alternatively, you can execute the following backup log command.

Once the tail-log backup completes, you can see the database in the restoring mode.

Database status

Execute the following DBATools command to include only databases having Normal status.

In the following output, we do not have SQLDB database that is in Restoring database state.

Command to get databases information having status Normal using DBATools Get-DbaDdatabase command

If we want to get detailed about database having database state Restoring, we can execute the following command.

Now, we only get one database SQLDB in the output because it is in Restoring mode as shown below.

Command to get databases information having status Restoring using DBATools Get-DbaDdatabase command

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.

In the following screenshot, we can see the database having a FULL recovery model.

Get a database having a specific recovery model using  DBATools Get-DbaDdatabase command

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.

Get a database having a specific recovery model

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.

In the output, we can verify that the tempdb database is not present in the output.

Exclude specific database 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.

In the output, you can see that we do not have TempDB, SQLDB and SQLTemp1 databases in the output of Get-DbaDatabase command.

Exclude specific database in DBATools

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

DBATools PowerShell Module for SQL Server
PowerShell SQL Server Validation Utility – DBAChecks
SQL Database Backups using PowerShell Module – DBATools
IDENTITY columns threshold using PowerShell SQL Server DBATools
DBATools PowerShell SQL Server Database Backups commands
SQL Restore Database using DBATools
Validate backups with SQL restore database operations using DBATools
Fix Orphan users in SQL Server using DBATools PowerShell
Creating a SQL Server Database using DBATools
Get SQL Database details using DBATools

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
490 Views