您现在的位置 :

首页  >  资讯 >  > 正文

全球时讯：dbatools demo

时间 :2023-04-18 09:22:04   来源 : 博客园


(相关资料图)

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)#然后，尝试连接SQLConnect-DbaInstance -SqlInstance 192.168.1.51 -SqlCredential $credential#弹框，输入账号密码$cred=get-credential saConnect-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#运行一些Jobget-dbaagentjob -SqlInstance 192.168.1.52 -SqlCredential $cred |Out-GridView -PassThru |Start-DbaAgentJob  invoke-item -Path "\\192.168.1.52\backup"#如果你好奇，可以查看一下正在运行的jobGet-DbaRunningJob  -SqlInstance 192.168.1.52 -SqlCredential $cred |Out-GridView#测试备份，并运行dbcc checkdbTest-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 -LatestTest-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

标签：

推荐文章

X 关闭

资讯

X 关闭

行业动态