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…

dbatools commands used in this post: Backup-DbaDatabase Restore-DbaDatabase Get-DbaDbBackupHistory Get-DbaBackupInformation Get-DbaLastBackup Get-DbaDbRestoreHistory Measure-DbaBackupThroughput New-DbaDbSnapshot Get-DbaDbSnapshot Restore-DbaDbSnapshot Remove-DbaDbSnapshot Backup Taking backup is an essential task for everyone working with any kind of data. SSMS offers a wide range of options for taking backups, and at least the same you can achieve using dbatools. SSMS dbatools: Backup-DbaDatabase…

dbatools commands used in this post: Rename-DbaDatabase Set-DbaDbOwner Set-DbaDbCompatibility Set-DbaDbRecoveryModel Set-DbaDbQueryStoreOption Set-DbaTempDbConfig Rename-DbaLogin Set-DbaLogin Set-DbaAgentJob Set-DbaAgentJobStep Set-DbaAgentJobOutputFile Set-DbaAgentJobOwner Invoke-DbaCycleErrorLog Set-DbaErrorLogConfig Change database The reasons to alter the database might be as many as different users. Some want to rename the old database, or maybe change the owner or recovery model after restoring the database. Enabling…

dbatools commands used in this post: Remove-DbaDatabase Remove-DbaLogin Remove-DbaDbUser Remove-DbaDbRoleMember Remove-DbaDbRole Remove-DbaServerRole Remove-DbaAgentJobStep Remove-DbaAgentJob One note before we start dropping objects on the SQL Server. The dbatools commands support a -WhatIf switch (more about that here). I would encourage you to test it out, especially with commands that are removing things. Drop database Who does…

dbatools commands used in this post: New-DbaDatabase New-DbaLogin New-DbaDbRole New-DbaDbUser New-DbaAgentJob New-DbaAgentJobStep New-DbaAgentSchedule New-DbaDbMailAccount New-DbaDbMailProfile New database Request for a new database is a pretty standard and repeatable task. It can be either a simple database based on the model database or more complex with multiple data files and filegroups. SSMS dbatools: New-DbaDatabase New login/user/role…