[The documentation](https://docs.dbatools.io/Invoke-DbaQuery.html) for Invoke-DbaQuery mentions many other similar functions > This function is a wrapper command around Invoke-DbaAsync, which in turn is based on Invoke-SqlCmd2. It was designed to be more convenient to use in a pipeline and to behave in a way consistent with the rest of our functions. There are of course other ways to query SQL from PowerShell, e.g. Invoke-Sqlcmd. What are the benefits of using Invoke-DbaQuery instead of Invoke-Sqlcmd, Invoke-Sqlcmd2, and Invoke-DbaAsync?

Trying to export SQL Objects via DBAtools in Powershell. Get-DbaAgentJob -SqlInstance 'TestServer01' -Job 'mydatabase-daily-backup' | Export-DbaScript -FilePath C:\temp\mydatabase-daily-backup.sql The same script working fine and exporting the job details. But, not working on a few servers, and returns the below error/warning > WARNING: [06:05:42][Get-DbaAgentJob] Failure | The value's length for key 'Data Source' exceeds its limit of '128'.

Trying to enable compression for a given database in SQL Server using: Set-DbaDbCompression -SqlInstance myserver -Database MyDB -CompressionType Recommended This command returns network path not found error. Not sure why. Any ideas? DBATools 2.1.30 Powershell 5 for Windows SQL Server 2019

I have to load a lot of CSV files into a DB (3 at a time, resulting in one table per CSV, named as the file name and headers are). I then run about 12 different stored procedures using those table names as parameters. The procedures are for a data validation process to see what is not aligned to our specifications. These all check for different things: the "validity" of specific values, formatting etc. That said they all have a different output, including the number of columns. I am attempting to automate the load, the stored procedure runs, and the placement of the result sets into a spreadsheet. I have written a Powershell script to load the files into the DB using dbatools, and then run the "root" stored procedure that runs the other 12 or so. I have found myself a bit stuck attempting to export this data without doing 12+ separate stored procedure calls into separate CSVs or inserting the results into a "final" table that includes header names in the rows... I will do that if needed, but this output is typically sent to clients for review; forcing them to open up to 12 CSVs for a review is cumbersome and having one table is not very clean given how much the columns differ. TSQL is my jam and I am not super experienced with PS. If I am being dumb, please point me to a relevant resource that I can make myself not dumb. Any suggestions are appreciated. Thanks

I'm following the official DBATools guide regarding how to query Azure SQL Databases. This code is working: $azureCredential = Get-Credential -Message 'Azure Credential' $azureAccount = Connect-AzAccount -Credential $azureCredential $azureToken = Get-AzAccessToken -ResourceUrl https://database.windows.net $azureInstance = "test.database.windows.net" $azureDatabase = "testsource" $server = Connect-DbaInstance -SqlInstance $azureInstance -Database $azureDatabase -AccessToken $azureToken Invoke-DbaQuery -SqlInstance $server -Query "SELECT @@VERSION" | Format-Table -AutoSize Even if it's always showing the yellow errors: WARNING: Unable to acquire token for tenant 'organizations' with error 'UsernamePasswordCredential authentication failed: AADSTS53003: Access has been blocked by Conditional Access policies. The access policy does not allow token issuance. Trace ID: e9cffe3d-xxxx-xxxx-xxxx-310b5365b200 Correlation ID: 99b44f31-xxxx-xxxx-xxxx-dbdfc97482db Timestamp: 2022-09-05 10:36:32Z See the troubleshooting guide for more information. https://aka.ms/azsdk/net/identity/usernamepasswordcredential/troubleshoot ' and the red error: Connect-AzAccount : UsernamePasswordCredential authentication failed: AADSTS53003: Access has been blocked by Conditional Access policies. The access policy does not allow token issuance. Trace ID: e9cffe3d-xxxx-xxxx-xxxx-310b5365b200 Correlation ID: 99b44f31-xxxx-xxxx-xxxx-dbdfc97482db Timestamp: 2022-09-05 10:36:32Z See the troubleshooting guide for more information. https://aka.ms/azsdk/net/identity/usernamepasswordcredential/troubleshoot At C:\Users\FrancescoMantovani\Desktop\test.ps1:2 char:17 + $azureAccount = Connect-AzAccount -Credential $azureCredential + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : CloseError: (:) [Connect-AzAccount], AuthenticationFailedException + FullyQualifiedErrorId : Microsoft.Azure.Commands.Profile.ConnectAzureRmAccountCommand This is the picture: Why is this working even if there is an error? What does this error means and why I'm not kicked out?

I have a SQL VM in Azure that backs up to blob storage. I'd like a script that will give me the most recent restore time for each database backed up to that storage. Get-DbaBackupInformation using the AzureCredential seems the obvious way to do it, but does anyone know the syntax for it? What should the -path parameter be? I read this article https://www.sqlservercentral.com/blogs/restoring-backups-from-azure-blob-with-dbatools Which is fine but if I can just use the Get-DbaBackupInformation using the AzureCredential without having to enumerate the files with Get-AzStorageBlob first, that would be a more graceful solution. Has anyone used this? Cheers Alex

You can run sp_blitz (First Responder Kit by Brent Ozar) on a sql server to output to a table. But it will store the output only on that server. I am using Dbatools.io PowerShell commands currently. I can run the sp_Blitz commands agains all SQL servers. But I don’t know how to capture the output on my report server in a single database table centrally. Is there a way to store output of sp_blitz command against a list of all servers so that I can create a report for all critical issues across all my servers? Here is the code I am using. ```# Import the dbatools module if you haven't already Import-Module dbatools # Set your database connection details $database = "" $table = "" $output = "dbo.BlitzResults" $sqlInstance = "" # The instance where the ServerList table is located $servers = Invoke-DbaQuery -SqlInstance $sqlInstance -Database $database -Query "select * from $table;" $servers # Loop through each server and execute sp_Blitz foreach ($server in $servers.ItemArray) { Write-Host "Running sp_Blitz on $($server)" try { $result = Invoke-DbaQuery -SqlInstance $server -Database master -Query "EXEC dbo.sp_Blitz" # Output the results $result | Format-Table -AutoSize #Following two lines are not working as expected #Write-Host $result.ItemArray #Write-DbaDbTableData -SqlInstance $sqlInstance -DestinationDatabase $database -DestinationTable $output -Data $result.ItemArray -CreateTable $true } catch { Write-Host "Failed to run sp_Blitz on $($server.ServerName). Error: $_" } }

I have a database in full recovery mode without log backups. Don't say it: _I know_. It fortunately isn't in an environment that I care about. Today, disaster struck and I tried to use dbatools to generate a script to restore it. At the time, I failed. I therefore turned to SSMS's GUI and walked away with this:

BACKUP LOG [MyDb] TO DISK = N'D:\SQL\Backups\MyDb_LogBackup_Timestamp.bak'
WITH NOFORMAT, NOINIT, NAME = N'D:\SQL\Backups\MyDb_LogBackup_Timestamp.bak',
NOSKIP, NOREWIND, NOUNLOAD, NORECOVERY,  STATS = 5

RESTORE DATABASE [MyDb] FROM DISK = N'X:\Backups\Full\MyDb_backup_yesterday_timestamp.bak' 
WITH FILE = 1, NORECOVERY , NOUNLOAD, STATS = 5

RESTORE DATABASE [MyDb] FROM DISK = N'X:\Backups\Full\MyDb_backup_this_morning_timestamp.dif' 
WITH FILE = 1, NOUNLOAD, STATS = 5

This worked great. While waiting on the restore, I got dbatools to work

Get-DbaBackupInformation -SqlInstance 'MyBox' -Path 'X:\Backups\Full\' -DirectoryRecruse -DatabaseName 'MyDb' |
Restore-DbaDatabase -OutputScriptOnly -SqlInstance 'MyBox' -WithReplace

and it produced this

RESTORE DATABASE [MyDb] FROM DISK = N'X:\Backups\Full\MyDb_backup_yesterday_timestamp.bak' 
WITH FILE = 1,
MOVE N'MyDb' TO N'D:\SQL\Data\MyDb_1_.mdf',
MOVE N'MyDb_log' TO N'D:\SQL\Logs\MyDb_log1_.ldf',
NORECOVERY, NOUNLOAD, REPLACE, STATS = 10

RESTORE DATABASE [MyDb] FROM DISK = N'X:\Backups\Full\MyDb_backup_this_morning_timestamp.dif' 
WITH FILE = 1,
MOVE N'MyDb' TO N'D:\SQL\Data\MyDb_1_.mdf',
MOVE N'MyDb_log' TO N'D:\SQL\Logs\MyDb_log1_.ldf',
NOUNLOAD, REPLACE, STATS = 10

From this, it is clear that dbatools and SSMS's GUI give very different scripts for completing an identical task. **In terms of the final result produced by running both, how do they differ?** As far as I can tell, STATS = 10 is the only part that gives a meaningful difference. These options are largely explained in [the](https://learn.microsoft.com/en-us/sql/t-sql/statements/restore-statements-arguments-transact-sql?view=sql-server-ver16) [documentation](https://learn.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql?view=sql-server-ver16) , but there is so much information in the RESTORE DATABASE docs that I am sure to have overlooked something important. Also, you can frankly never be too careful when it comes to backups. It also surprises me that SSMS generated an entire extra command that dbatools did not and that dbatools added all of the MOVE stuff.

I am trying to use one table to store the SQL server disk space checking data by dbatools: $allservers_PRD | Get-DbaDiskSpace | Write-DbaDbTableData -SqlInstance MySQL2019 -Database SQL_DBA_TASK -Table SQL_DISK_SPACE -AutoCreateTable How can I add timestamps (what is the checking time) into my table rows as addition column when I use it? Many thanks,

I like to use Export-DbaScript to export to folders that are named C:\TargetFolder\Database\Schema. Often, I don't already have a Database\Schema folder, which will cause Export-DbaScript to fail to write to that folder. Is there any way to make Export-DbaScript create the folder if it doesn't already exist? I'm expecting something like a -Force parameter, but I have not found it in the documentation. I'm doing

Export-DbaScript `
  -InputObject $_ `
  -NoPrefix `
  -NoClobber `
  -FileName "$($MyLocation)\$($_.Database)\$($_.Name).sql

When the path doesn't exist, I get System.IO.DirectonaryNotFoundExceptions. I'm on version 2.1.18

We are wanting to copy all the Logins from one Azure Managed Instance to another. We are using the DBATools.io powershell command "Copy-DbaLogin" to do this. We are using the -Force option to drop & recreate any logins already in place on the target instance. This command works great for SQL auth logins. However for logins that represent Azure Active Directory Groups, it fails part way through (meaning it drops the login from the target server, but then fails to recreate it again based on what is present on the source server). The error message is "Cannot find the login 'MyGroupName', because it does not exist or you do not have permission." I don't recall ever having this problem in the past -- but then again this is the first time I'm using the command against Azure Managed Instances rather than normal SQL Servers. UPDATE: I have now checked and can confirm that the message occurs regardless of whether I am using SQL auth or AAD with MFA to connect to the Managed Instance. So this does seem to be more of a problem with DBATools than an Azure issue. Anyone know the cause?

today I had to check if in-transit Encryption was up and running in a particular server (sql server) and if not then set it up. the way it is done is: > In SQL Server Configuration Manager, expand SQL Server Network > Configuration, right-click Protocols for , and then > select Properties. On the Flags tab, in the ForceEncryption box, > select Yes, and then select OK to close the dialog box. Restart the > SQL Server service. the picture below is after I restarted the sql server service . now, I am one DBA that likes DBA paradise, and all automation and things done through scripts. How could I get this done through scripts? partial solutions also valid. and it could be powershell,T-SQL, or any other scripting language.

I have a query that shows me all the sql server instances in a server:

DECLARE @GetInstances TABLE
( Value nvarchar(100),
 InstanceNames nvarchar(100),
 Data nvarchar(100))

Insert into @GetInstances
EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = 'SOFTWARE\Microsoft\Microsoft SQL Server',
  @value_name = 'InstalledInstances'

Select [ServerName] = @@servername,InstanceNames from @GetInstances

and I also have a query that shows me the memory settings and usage of a particular instance:

SELECT [instance] = @@servername, p.* 
  FROM (SELECT name, [value_in_use] 
          FROM sys.configurations) t 
		  PIVOT (MAX([value_in_use]) FOR name IN ([min server memory (MB)], 
		                                          [min memory per query (KB)], 
												  [max server memory (MB)], 
												  [optimize for ad hoc workloads])) p

is there a way I can have the best of both these worlds - have all info for all my sql server instances, in one query? I would like to avoid having to create a linked server. I would not mind using powershell or any other tool, as long as I don't need to run it as admin.

Using DBATools, is there a recommended way to get the summed total size of all DBs for a given server?

I'm looking to leverage dbatools powershell module for our SQL Server management needs. The install process does not lend itself for installation across a large number of servers. Is it really necessary or even a best practice to install dbatools across all SQL Servers in the environment or leverage a server we use for our database management as a central place to install to and manage from there? Seems like a lot of the commands I've looked at can be used remotely. Thank you.

I have the following code that I use to retrieve some TempDB space details on an AG:

$DataDriveTable = Invoke-DbaQuery -SqlInstance $SQLInstance -Database TempDB -Query $SelectDriveQuery -EnableException

$DataDriveTable | ConvertTo-DbaDataTable | Write-DbaDataTable -SqlInstance $SQLInstance -Database master -Table "TempDBDriveInfo" -EnableException

I started to receive this intermittent error: The following exception occurred while trying to enumerate the collection: > The target database, 'XXX', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online." I can't understand why it is reading other user databases in the AG as I either specify TempDB or master only? I don't touch user databases or their properties. I have since disabled the job from running if the current node is passive (as it is set to non readable) but would like to understand why the code does this. I expected data to be written to the table but instead received an enumeration error. This only happens on the passive node where users databases are not readable.

I have created a script in PowerShell (utilising the PowerShell dbaTools) which collect information about SQ Server Estate in our environment. I need to insert the result from the PowerShell directly from the script into Oracle Database table. Example: #GET DISK INFO: $DiskInfo=Get-DbaDiskSpace -ComputerName $Servers | Select @{label='SERVER_NAME';expression={$_.ComputerName}}, @{label='DISK_DRIVE_NAME';expression={$_.Label}}, @{label='CAPACITY_GB_MSR';expression={$_.SizeInGB}}, @{label='USED_GB_MSR';expression={$_.SizeInGB - $_.FreeInGB}},@{label='FREE_SPACE_GB_MSR';expression={$_.FreeInGB}}, @{label='DISK_DRIVE_LETTER_CODE';expression={$_.Name}}, @{label='LINUX_MOUNT_POINT_PATH';expression={'N/A'}} |Out-GridView Result in $DiskInfo variable: Now I need to update the DiskInfo table in the Oracle database with the info in the PowerShell variable above (If not exist). 1. My first challenge is I can't seem to find any script that provides a good test for connection to Oracle. I have tried several scripts from online but none seems to be working. I have tried the connection script from following links and all seems to be erroring out: https://purple.telstra.com.au/blog/using-powershell-to-query-oracle-dbs-without-using-the-oracle-client-oracle-data-provider-for-net https://forums.oracle.com/ords/apexds/post/powershell-and-odp-net-how-do-run-update-and-commit-command-0848 https://www.experts-exchange.com/questions/29247739/Insert-Update-oracle-DB-tables-in-PowerShell.html 2. How do I take the value from the PowerShell variable and insert it in the right columns accordingly? Thank you in advance for any direction or guidance.

I'm working with a directory of mixed SQL Server backup files and need to restore a specific database using dbatools in PowerShell. Below is the directory structure of my backup folder:

PS C:\Users\administrator> tree \\dc1\backup /F
Folder PATH listing
\\DC1\BACKUP.
│   Hadr_endpoint_cert.cer
│   Hadr_endpoint_cert.pvk
│
└───NODE1
    └───MSSQLSERVER
        ├───AdventureWorks2016
        │   ├───Full
        │   │       Full_AdventureWorks2016_20240219011641.bak
        │   │
        │   └───Log
        │           Log_AdventureWorks2016_20240219011647.trn
        │
        └───Credit
            ├───Full
            │       Full_Credit_20240219012730.bak
            │
            └───Log
                    Log_Credit_20240219012735.trn

PS C:\Users\administrator>

When attempting to restore the Credit database onto node2 with the following command, I encounter issues:

$files = Get-ChildItem -Path \\dc1\backup -Recurse -Include *.trn,*.bak
Restore-DbaDatabase -SqlInstance node2 -Path $files -DatabaseName Credit -NoRecovery -ExecuteAs sa

This results in the error:

WARNING: [01:27:36][Restore-DbaDatabase] Multiple Databases' backups passed in, but only 1 name to restore them under. Stopping as cannot work out how to proceed.

Using this command instead:

Restore-DbaDatabase -SqlInstance node2 -Path \\dc1\backup -DirectoryRecurse -DatabaseName Credit

Leads to a different error:

WARNING: [01:32:00][Get-DbaBackupInformation] Failure on node2 | The media family on device '\\dc1\backup\Hadr_endpoint_cert.cer' is incorrectly formed. SQL Server cannot process this media family. RESTORE HEADERONLY is terminating abnormally.

I understand that I can manually select files that are related to the Credit database by their naming convention, but what if the backup files do not have the database name in their filename? What would be the best approach to automate this restoration while filtering out unrelated files? Any guidance or tips on the proper use of dbatools for this scenario would be greatly appreciated! Thank you in advance.

I don't know if this is the right forum for this question, please let me know if it isn't and kindly point me in the right direction. I have just started learning powershell and dbatools. I am trying to automate uninstalling a SQL Server instance using dbatools. Starting with the following command: $list = Get-WmiObject -Class Win32_product | Select-DbaObject -Property Name $list.name -cmatch "SQL Server 2019 Database Engine Services". It returns two entries. My questions: - There is only one version of SQL Server on the newly built machine, why are two entries returned? - Is the Engine Service the appropriate service to remove in order to remove all the installed features of the instance at once? $list returns a number of SQL Server 2019 components. Thank you

I'm new to powershell and dbatools. It is a great tool but when I try to combine the result of few different commands into an array to be exported to csv, it is not providing the intended result. It is only printing the output of first commands. Can someone please help me with joining the result of all three variables? What I'm trying to do is writing a script to automate generating our SQL Server Inventory and Capacity assessment report. Here's my code so far: $Servers = Get-Content 'C:\Users\temp\ServerList.txt' $reportData = @() foreach ($Server in $Servers) { if (Test-Connection -ComputerName $Server -Count 1 -ErrorAction 'SilentlyContinue') { $OSInfo=Get-DbaOperatingSystem -ComputerName MyServer| Select ComputerName, OSVersion, Version, Architecture $DiskSPace=Get-DbaDiskSpace -ComputerName MyServer| Select ComputerName, Label, Name, Capacity, Free $SQLInstanceInfo=Connect-DbaInstance -SqlInstance MyServer | Select DbaInstanceName, Edition, NetPort, IsClustered, Processors, ProductLevel, ServiceName, $reportData +=$OSInfo $reportData +=$DiskSPace $reportData +=$SQLInstanceInfo } else { write-host $Server "not connected" } } Write-Output $reportData | Format-Table