dbatools demo

break

#从PowerShell Gallery 安装dbatools模组
Install-Module dbatools

<#
    01.查找SQL实例
    02.连接SQL实例
    03.检查备份
    04.检查磁盘空间
    05.备份数据库
    06.安装欧拉维护工具
    07.运行Job
    08.测试备份
    09.查看fail的job
    10.检查最大内存设置
    11.检查SQL版本
    12.数据库迁移和账号迁移

#>


#开始， 查找SQL实例
#使用tcp,udp,spns,wmi等方式探查
Find-DbaInstance -ComputerName 192.168.1.51


#把登陆信息存起来
$securePassword = ('yourpassword' | ConvertTo-SecureString -asPlainText -Force)
$credential = New-Object System.Management.Automation.PSCredential('sa', $securePassword)

#然后，尝试连接SQL
Connect-DbaInstance -SqlInstance 192.168.1.51 -SqlCredential $credential


#弹框，输入账号密码
$cred=get-credential sa
Connect-DbaInstance -SqlInstance 192.168.1.52 -SqlCredential $cred

#可以使用SSMS中的注册服务器，利用里面存的账号信息登陆
Get-DbaRegisteredServer -Name 192.168.1.52

#使用管道，连续操作
Get-DbaRegisteredServer -Name 192.168.1.52 | get-dbalastbackup| Out-GridView

#检查你的备份情况
get-dbalastbackup -SqlInstance 192.168.1.52 -SqlCredential $cred |Select-Object * | Out-GridView

#备份有缺失? 检查磁盘空间是否足够，使用windows账号
get-dbadiskspace -ComputerName 192.168.1.52 -Credential fp-qsmc\administrator

#OK，空间足够，快速备份数据库，作为测试可以设定网络共享路径

backup-dbadatabase -SqlInstance 192.168.1.52 -SqlCredential $cred -Path \\192.168.1.52\backup  

#确认备份时间
Get-DbaLastBackup -SqlInstance 192.168.1.52 -SqlCredential $cred | Out-GridView


#安装欧拉维护工具
$params=@{
    sqlinstance="192.168.1.52"
    installjobs=$true
    replaceexisting=$true
    backuplocation="\\192.168.1.52\backup"
    sqlcredential=$cred
}
Install-DbaMaintenanceSolution @params

#运行一些Job

get-dbaagentjob -SqlInstance 192.168.1.52 -SqlCredential $cred |Out-GridView -PassThru |Start-DbaAgentJob  
invoke-item -Path "\\192.168.1.52\backup"

#如果你好奇，可以查看一下正在运行的job
Get-DbaRunningJob  -SqlInstance 192.168.1.52 -SqlCredential $cred |Out-GridView

#测试备份，并运行dbcc checkdb

Test-DbaLastBackup  -SqlInstance 192.168.1.52 -SqlCredential  $cred -Database AdventureWorks2019 | Out-GridView

#查看fail的job列表 
Find-DbaAgentJob  -SqlInstance 192.168.1.51 -SqlCredential $cred -IsFailed | Get-DbaAgentJobHistory |Out-GridView

#检查最大内存设置
Test-DbaMaxMemory  -SqlInstance 192.168.1.52 -SqlCredential $cred | Set-DbaMaxMemory -WhatIf

$instanceSplat = @{
    SqlInstance   = $source, $target
}
#检查dbowner设置
Test-DbaDbOwner @instanceSplat |
Select-Object SqlInstance, Database, DBState, CurrentOwner, TargetOwner, OwnerMatch |
Format-Table
#检查恢复模式
Test-DbaDbRecoveryModel @instanceSplat |
Select-Object SqlInstance, Database, ConfiguredRecoveryModel, ActualRecoveryModel |
Format-Table

#检查tempdb文件个数配置
Test-DbaTempDbConfig @instanceSplat|Format-Table

#检查MAXDOP设置 Calculator (https://blogs.msdn.microsoft.com/sqlsakthi/p/maxdop-calculator),
Test-DbaMaxDop @instanceSplat|Format-Table

#检查SQL版本，是否打最新补丁
#更新补丁信息
Update-DbaBuildReference

#检查SQL版本
Test-DbaBuild  -SqlInstance 172.26.40.91 -SqlCredential $cred -Latest

Test-DbaBuild  -SqlInstance 192.168.1.52 -SqlCredential $cred -Latest

#运行数据库迁移和账号迁移
$securePassword = ('yourpassword' | ConvertTo-SecureString -asPlainText -Force)
$credential = New-Object System.Management.Automation.PSCredential('sa', $securePassword)
$source = connect-dbainstance -sqlinstance 192.168.1.51 -SqlCredential $credential
$target = connect-dbainstance -sqlinstance 192.168.1.52 -SqlCredential $credential 


$migrateDbSplat = @{
    Source        = $source
    Destination   = $target
    Database      = 'TSQL'
    BackupRestore = $true
    SharedPath    = '\\192.168.1.51\backup'
    #SetSourceOffline        = $true
    #Verbose       = $true
  }
Copy-DbaDatabase @migrateDbSplat 

#账号迁移
$migrateLoginSplat = @{
    Source      = $source
    Destination = $target
    #Verbose     = $true
}
Copy-DbaLogin @migrateLoginSplat