SQL

dbatools commands used in this post: New-DbaServiceMasterKeyBackup-DbaServiceMasterKeyNew-DbaDbMasterKeyGet-DbaDbMasterKeyBackup-DbaDbMasterKeyRemove-DbaDbMasterKeyNew-DbaDbAsymmetricKeyGet-DbaDbAsymmetricKeyRemove-DbaDbAsymmetricKeyNew-DbaDbCertificateGet-DbaDbCertificateBackup-DbaDbCertificateRemove-DbaDbCertificateRestore-DbaDbCertificate Service Master Key There are multiple security-related objects that are not easily accessible via SQL Server Management Studio. The first one would be Service Master Key, if exists, can be seen under the master database. Luckily, dbatools can help us to take a backup. SSMS dbatools: New-DbaServiceMasterKey dbatools:…

dbatools commands used in this post: Get-DbaService Stop-DbaService Start-DbaService Restart-DbaService Start-DbaAgentJob Stop-DbaAgentJob New-DbaEndpoint Start-DbaEndpoint Stop-DbaEndpoint Start-DbaTrace Get-DbaTrace Stop-DbaTrace Start-DbaXESession Stop-DbaXESession SQL Services OK, let’s start. While doing some maintenance tasks there is a need to start/stop/restart SQL Services. Using SQL Server Configuration Manager is one way or even straight from SSMS, however, working with multiple…

dbatools commands used in this post: Export-DbaScript Export-DbaUser Export-DbaLogin Export-DbaDacPackage Publish-DbaDacPackage Export-DbaInstance Copy-DbaDbTableData Export-DbaDbTableData Import-DbaCsv Script it out The SSMS offers to script out lots of the SQL Server objects, however it can be limited in some areas. Using Get-Dba* commands and piping them into Export-DbaScript may add few more options. For example SQL Agent…

dbatools commands used in this post: Enable-DbaAgHadr Disable-DbaAgHadr New-DbaAvailabilityGroup Add-DbaAgReplica Add-DbaAgDatabase Add-DbaAgListener Remove-DbaAgListener Get-DbaAgDatabase Set-DbaAgReplica Invoke-DbaAgFailover Suspend-DbaAgDbDataMovement Resume-DbaAgDbDataMovement Sync-DbaAvailabilityGroup AlwaysOn Availability Groups The feature that has been introduced in SQL 2012 is quite an important one. Also, it requires a number of clicks there and there in SSMS. Luckily for us dbatools have set of…

dbatools commands used in this post: Get-DbaSpConfigure Export-DbaSpConfigure Set-DbaSpConfigure Import-DbaSpConfigure Set-DbaMaxDop Set-DbaMaxMemory SQL Server configuration / sp_configure SQL Server allows its users to configure a lot of things. From ‘Ad Hoc Distributed Queries’ through ‘max degree of parallelism’ to ‘xp_cmdshell’. Some of those can be done via SQL instance properties or the famous sp_configure. With…

dbatools commands used in this post: Add-DbaRegServer Add-DbaRegServerGroup Get-DbaRegServer Get-DbaRegServerGroup Move-DbaRegServer Move-DbaRegServerGroup Remove-DbaRegServer Remove-DbaRegServerGroup Export-DbaRegServer Import-DbaRegServer Create registered servers or Central Management Servers The built-in feature of the SSMS allows us to configure a group of SQL instances and run queries against multiple instances at once. With the registered servers you can also build a…