Questions tagged [dbatools]
For questions about dbatools, a PowerShell module to help SQL Server Pros be more productive.
38
questions
0
votes
1
answer
23
views
Can we change the parameter of Find-DbaAgentJob?
Can we change the parameter of Find-DbaAgentJob?
Hi there,
I got "[Find-DbaAgentJob] Failure | Error connecting to [xxx.yyy.zzz]: The certificate chain was issued by an authori
ty that is not ...
- 419
1
vote
0
answers
79
views
dbatools, DbcCheck Does not use Saved credential
Last week I deployed dbccheck on the production environment. I found strange thing and have no idea what the problem is.
We have 40 db vm's which are not domain joined. Each of db vm's have same ...
- 11
0
votes
0
answers
49
views
Rollback a write-dbadatatable with the truncate flag?
I'm using the dbatools for our SQL Server 2019 server. When I try to import an Excel sheet, I would like to truncate the table before importing the new rows. This is possible if I add the -Truncate ...
- 2,153
0
votes
1
answer
179
views
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-...
- 1,353
0
votes
2
answers
160
views
dbatools import csv help!
Is there a way I can import csv files with the proper data types into MSSQL table using PS dba tools?
I'm using this below command but all I get is a table and all the columns are nvarchar(max):
...
- 90
-1
votes
2
answers
193
views
Get total size of all databases with DBATools
Using DBATools, is there a recommended way to get the summed total size of all DBs for a given server?
- 1,036
0
votes
1
answer
167
views
Copy users to other database with the same permissions
We use dbatools to migrate/copy databases to other servers.
This sometimes means that we restore a database with a different name (ex: database => database_Test).
When I use the Copy-DbaLogin ...
0
votes
1
answer
697
views
MySQL Trigger to prevent update in one table if particular data is available in another table
I need a MySQL trigger that can prevent update of a particular column data, if some specific data is available in another table.
I have two tables product and sales.
Product
Sale
I need to prevent ...
- 13
0
votes
0
answers
193
views
Exporting multiple TSQL result sets into one spreadsheet using Powershell
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 ...
- 75
0
votes
0
answers
63
views
Can a SSMS job which runs an SSIS package be resource limited within a virtual server?
I have a SQL Agent job which runs weekly and executes an SSIS package. The job takes over two hours to complete and in that time it consumes over 90% of the server CPU, this means the replication ...
1
vote
0
answers
67
views
Get Queries History from SQL Server Database
I started a new job as DBA, and one of the tasks is to run backups and stuff but since it's my first experience I would like to get a general idea about the databases I'll be managing, for that I want ...
1
vote
3
answers
244
views
How to restore automatically the latest of 10 .bak files from a blob container?
I am trying to have a copy of a transactional database on another server restored once a day for reporting purposes. SQL Server is the instance.
Original database is a member of an Availability Group. ...
- 361
0
votes
1
answer
2k
views
Invoke-DbaQuery not working on new server, Cannot find an overload for "SqlCommand"
I've ran the install script both without and with the -Force parameter from the Getting Started doc on github
Install-Module dbatools -Scope CurrentUser
Install-Module dbatools -Scope CurrentUser -...
- 3,018
0
votes
1
answer
233
views
Run T-SQL Script Using Powershell via SQL Agent Job
Tried to think of the best subject title; however having a problem getting this done. I'll give my preferred way, but open to any best method to get this done.
I have a tsql script that is done, ...
- 21
1
vote
0
answers
169
views
Unable to execute dbachecks command
i installed dbachecks module in a server. but when i am trying to execute get-dbccheck, it is throwing the below error
Populating RepositorySourceLocation property for module Pester.
VERBOSE: Loading ...
0
votes
1
answer
565
views
Passing in multiple instances into the dbatools command GET-DBADBFILE
I am attempting to gather all of the dbfile information on all of our sql instances in one of our environments. It is a fairly large environment and would be tedious to perform one instance at a time. ...
- 1,083
0
votes
1
answer
55
views
Migrate database to Azure
I have a 600GB database that I must transfer from an on-premises machine to an on Azure VM machine (IaaS)
I would like to use the dbatools copy-database tool for this
for this I will have to know what ...
0
votes
1
answer
108
views
How can I add timestamp into my table when I use Write-DbaDbTableData?
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 -...
- 419
1
vote
1
answer
519
views
how can I get only the SQL server logins, when we use Export-dbalogin
when we run the export-dbalogin, it will will generate a script(.sql) file, which we will run the in the destination server, then the logins will be migrated, but this command will give both windows ...
1
vote
1
answer
391
views
How do I pass a variable into -job on Copy-DbaAgentJob?
I'm trying to query one the source instance and copy across any missing jobs into the destination instance. Everything seems to work except passing the name into -job. Can anyone suggest a fix or ...
0
votes
1
answer
447
views
Powershell dbatools combining result of multiple Get-dbaxxx commands to generate a report
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 ...
- 195
0
votes
1
answer
2k
views
Encrypt connection when using sqlcmd
Trying to connect database with an encrypted connection, tried below sql queries:
sqlcmd -N -E -S tcp:SQLSERVER,1234 -U username -P password -d Database -Q "INSERT INTO table1 VALUES(1,2,2)
ERROR:...
- 3
0
votes
1
answer
365
views
How to find port using Get-DbaTcpPort
How to find whether the port used by SQL Server is static or dynamic using Get-DbaTcpPort dbatools command?
Below command doesn't show whether it's static/dynamic:
Get-DbaTcpPort -sqlinstance TEST
...
- 1,810
4
votes
1
answer
310
views
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 ...
- 359
0
votes
1
answer
319
views
Importing dbatools module into Azure Automation Account
Has anyone managed to import dbatools module into Azure Automation Account? I tried to do it in several ways, but every time the installation fails:
different versions checked from 0.9.* to the ...
- 348
1
vote
1
answer
158
views
Sync Server Objects when having two or more Availability Groups
Lets imagine that i have two servers/nodes with TWO availability groups Group01 and Group02. I will need to ensure that the server objects are all the time recreated over the secondary replica.
If i ...
0
votes
1
answer
72
views
Can Set-DbaAgReplica filter any SqlInstances out
I have been working with an Availability Group with an extra asyncronous secondary, that is always set to manual failover.
I am worried that if I ran:
Get-DbaAgReplica -SqlInstance x -...
- 101
6
votes
2
answers
2k
views
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 ...
- 7,869
0
votes
1
answer
374
views
dbatools start-dbamigration running very slowly
I am trying to use the powershell module dbatools to migrate one SQL Server instance to a new instance on a VM. The new VM is on a different physical server than the source. I am not migrating the ...
- 563
3
votes
2
answers
193
views
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 ...
- 4,408
2
votes
1
answer
3k
views
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 ...
- 93
0
votes
1
answer
558
views
dbatools - Copy all jobs to the other node - selected jobs only
Is it possible to copy all sql server agent jobs using dbatools from one node to another, except a particular job? or perhaps just copy a set of jobs?
- 103
2
votes
3
answers
1k
views
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"]....
- 115
1
vote
2
answers
289
views
dbatools, DbcCheck LastBackup
Last week I started testing dbccheck on my test system. I found one strange thing, and I have no idea what is the problem, last fullbackup is few hours old, but dbccheck print an error.
Following you ...
- 11
1
vote
2
answers
105
views
Test-DbaDiskAlignment Not Working
PS H:\> Test-DbaDiskAlignment -ComputerName Test_Server
WARNING: [Test-DbaDiskAlignment][22:09:54] Can't connect to Test_Server
WARNING: [Test-DbaDiskAlignment][22:09:54] CIM query to ...
7
votes
4
answers
5k
views
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 $...
- 2,153
6
votes
3
answers
2k
views
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 ...
- 1,083
2
votes
1
answer
1k
views
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 ...
- 23.5k