Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [dbatools]

For questions about dbatools, a PowerShell module to help SQL Server Pros be more productive.

The dbatools module is a PowerShell module that started out as just Start-SqlMigration, the brainchild of PowerShell MVP, Chrissy LeMaire. It has since grown into the SQL Server DBA's best friend.

The module is comprised of hundreds of commands that make the day-to-day task of being a SQL Server DBA easier, and more efficient. The current list of commands can be found on the module's site here.

Requirements

  • PowerShell 3.0 or higher
  • SQL Server SMO (installed via SSMS generally works).
  • The majority of the migration commands (Copy-Dbaxxx) are supported against SQL Server 2000.

Many commands validate the version of SQL Server and will output a warning if you are connecting to an unsupported version.

Getting Started

This module is hosted both in the Microsoft's PSGallery and on GitHub under the Data Platform Community project.

The PSGallery will contain the latest "master" version which is fully tested. You can install that version by simply running:

Install-Module dbatools

Note: using the GitHub method will not support utilizing the PackageManagement module to update the module (e.g. Update-Module). You will have to run the command within dbatools Update-DbaTools.

54 questions
8
votes
4 answers

SQL Agent PowerShell task never finishes

I'm quite new to PowerShell, and now I've found dbatools.io, where I would like to run a PowerShell task in SQL Agent like $ExportPath = $env:TEMP + '\DriveSpace.csv' $datatable = Import-Csv $ExportPath | Out-DbaDataTable Write-DbaDataTable…
Henrik Staun Poulsen
  • 2,249
  • 2
  • 23
  • 41
6
votes
2 answers

Process to change collation on a database

I've been asked to change the collation on a database that has been in production for a few years as it was not set correctly at the time of deployment. So my research shows that in order change collation on all of the columns you have to drop…
Tony Hinkle
  • 8,062
  • 1
  • 24
  • 46
6
votes
3 answers

SQL Agent PowerShell Syntax Error

I am attempting to setup a PowerShell script within a SQL Agent job (SQL Server 2016). I'm using a third party module, DBATOOLS.psd1 which appears to be where I'm getting my syntax error. Currently my SQL Job has one line of code which is…
Geoff Dawdy
  • 1,143
  • 7
  • 23
  • 53
4
votes
1 answer

Should dbatools be installed on all SQL Servers

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…
Russ960
  • 361
  • 4
  • 14
3
votes
2 answers

DBATools - pass through flat file configs to Agent Job Commands

I'm experimenting with source controlling my SQL Agent Jobs & Schedules as .json files and deploying them with the dbatools SQL Agent command suite. Given $sa = Get-Credential and foo.config of the below form... { "Schedule": …
Peter Vandivier
  • 5,485
  • 1
  • 25
  • 49
2
votes
1 answer

how to set in-transit encryption sql server?

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…
Marcello Miorelli
  • 17,274
  • 53
  • 180
  • 320
2
votes
2 answers

getting the memory settings for all instances in a server

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 =…
Marcello Miorelli
  • 17,274
  • 53
  • 180
  • 320
2
votes
1 answer

Insert values in PowerShell Variable into Oracle Database Table

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…
Ali
  • 345
  • 3
  • 17
2
votes
1 answer

DBATools :: how to query Azure SQL Databases with MFA

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 =…
Francesco Mantovani
  • 1,695
  • 14
  • 28
2
votes
1 answer

DBATools offline install - export-dbainstance was not found

I followed the instruction at https://dbatools.io/offline/: download zip & unzip run $env:PSModulePath to find all the paths. copy to C:\Program Files\WindowsPowerShell\Modules. run Get-ChildItem -Recurse "C:\Program…
James
  • 93
  • 1
  • 4
2
votes
3 answers

How can the System databases in SQL Server 2016 be moved using Powershell?

I'm trying to move the system databases using Powershell ie. without using any T-SQL. Using SMO : Install-Module SQL-SMO $smo = New-SMO -ServerName localhost $smo.databases["TempDB"].PrimaryFilePath= "F:\Tempdb\" $smo.databases["TempDB"].Alter() …
Ayan Mullick
  • 115
  • 1
  • 1
  • 11
2
votes
1 answer

Get-MSSQLLinkPasswords Powershell script doesn't seem to work under SQL Server 2016

I've been using the Get-MSSQLLinkPasswords Powershell script for a long time and it has been a huge help. The script decrypts the passwords for all linked servers on a particular Windows server and shows them to you in plain text. This…
Scott Hodgin - Retired
  • 24,062
  • 2
  • 29
  • 52
1
vote
1 answer

How to run sp_blitz against all sql servers and store the output in a single table?

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…
Pat
  • 357
  • 1
  • 5
  • 13
1
vote
1 answer

How to Filter and Restore Specific SQL Database Backups Using dbatools in PowerShell

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…
Just a learner
  • 2,082
  • 7
  • 36
  • 57
1
vote
1 answer

Why should I prefer Invoke-DbaQuery to its alternatives?

The documentation 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…
J. Mini
  • 1,161
  • 8
  • 32
1
2 3 4