dbaTools sample Inventory Code

Andreas Schubert posted a blog about how to use dbaTools to get SQL Server information inventory in your organization.
Here is the link to the post:

building an inventory view of SQL Servers with dbatools

And the code download:
https://github.com/AndreasESchubert/Automation

You just need to create a name list for your servers.
The scan is pretty quick and generate an HTML view.

Advertisements

Script Table and Export Data with dbaTools

  


## Get-DbaDbTable
## Export-DbaScript
## Export-DbaDbTableData
  
  #script table: accounttype and addresstype
Get-DbaDbTable -SqlInstance myserver -Database mydb -Table 'dbo.accounttype', 'dbo.addresstype' | Export-DbaScript -FilePath C:\temp\myexport\myexportscript.sql 
# export data from accounttype
Get-DbaDbTable -SqlInstance myserver -Database mydb -Table 'dbo.accounttype' | Export-DbaDbTableData -Path C:\temp\myexport\ 
# export data from addresstype
Get-DbaDbTable -SqlInstance myserver -Database mydb -Table 'dbo.addresstype' | Export-DbaDbTableData -Path C:\temp\myexport\
 
  

 

https://docs.dbatools.io/#Export-DbaScript


dbatools: Backup-DbaDatabase and Restore-DbaDatabase samples

I have played around with Backup-DbaDatabase and Restore-DbaDatabase cmdlets from dbatools. The following snippets are: backup selected databases and restore them with a different name on the same server.

  
  
  
#create a shared folder
$Network_Folder = '\\myfileshare\backups\'


#clean up backup folder --shared
Get-ChildItem -Path $Network_Folder -Include *.bak -File -Recurse | foreach { $_.Delete() }

#backup selected databases
Backup-DbaDatabase -SqlInstance my2019Machine -Database myDB1,myDB2,myDB3 -Type Full -CompressBackup -CopyOnly -BackupDirectory $Network_Folder

# restore a copy with new name
Restore-DbaDatabase -SqlInstance my2019Machine -Path $Network_Folder -RestoredDatabaseNamePrefix 'daily_' -DestinationDataDirectory d:\restoreedDB -WithReplace

# clean up
Remove-DbaDatabase -SqlInstance my2019Machine -Database daily_myDB1,daily_myDB2,daily_myDB3 -Confirm:$false

 
 

 

The term ‘Install-Module’ is not recognized When you try to Install dbatools

If you are not on a machine with Powershell 5.1 or above, you will run into this error.
You can run command below to see the installed version.

  
  
  
 
 $PSVersionTable.PSVersion

 ## or

$PSVersionTable.PSVersion
 
 
 

 

You can try to download Windows Management Framework 5.1 to get Powershell 5.1.

https://www.microsoft.com/en-us/download/details.aspx?id=54616


dbatools 1.0 released Today!

Let’s start with Powershell console with administrator rights.

  

Install-Module -Name dbatools

 

You will have dbatools 1.0 installed from Powershell console with a few clicks.
You can check to see how many versions before 1.0 installed on your machine:

  

Get-InstalledModule -Name dbatools -AllVersions

 

You can clean up installed old versions by running:

  

$module = Get-InstalledModule -Name dbatools
Get-Module -Name $module.Name | Remove-Module
Get-InstalledModule $module.Name -AllVersions | Where-Object {$_.Version -ne $module.Version} | Uninstall-Module -Verbose

 

You can check again:

  

Get-InstalledModule -Name dbatools -AllVersions

 

It is time to dive into the dbatools 1.0 now.

dbatools 1.0 has arrived ūüéČ


dbatools v1.0? It’s available – Check it out!

Cleanup/Remove old PowerShell modules

getting started


COUNT Distinct Over() Sample

  
 create table #Tab (Eset varchar(5),Pid varchar(5), opt varchar(9), Erule varchar(5), effdate datetime)
Insert into #Tab values ('004A','ET','opt1rc150','A','2017-01-01')
Insert into #Tab values ('004A','ET','opt2rc170','A','2017-01-01')
Insert into #Tab values ('004A','ET','opt3rc150','A','2017-01-01')
Insert into #Tab values ('004A','ET','opt4rc150','A','2017-01-01')

Insert into #Tab values ('004A','ET','opt1rc200',NULL,'2018-01-01')
Insert into #Tab values ('004A','ET','opt2rc200',NULL,'2018-01-01')
Insert into #Tab values ('004A','ET','opt3rc200',NULL,'2018-01-01')
Insert into #Tab values ('004A','ET','opt4rc200',NULL,'2018-01-01')


Insert into #Tab values ('005A','ET','opt1rc175',NULL,'2018-01-01')
Insert into #Tab values ('005A','ET','opt1rc200',NULL,'2018-01-01')
Insert into #Tab values ('005A','ET','opt1rc200',NULL,'2018-01-01')
Insert into #Tab values ('005A','ET','opt1rc200',NULL,'2018-01-01')

Insert into #Tab values ('007A','ET','opt1rc140',NULL,'2019-01-01')
Insert into #Tab values ('007A','ET','opt1rc140',NULL,'2019-01-01')
Insert into #Tab values ('007A','ET','opt1rc140',NULL,'2019-01-01')
Insert into #Tab values ('007A','ET','opt1rc140',NULL,'2019-01-01')

 


;with mycte as (
SELECT  Eset,Pid  , opt ,Erule,effdate,
dense_rank() over( Partition by Eset,Pid,effdate Order by  right(opt,3)) 
+ dense_rank() over(Partition by Eset,Pid,effdate Order by  right(opt,3) desc) - 1 as dr FROM #Tab  
 )
 

 select Eset,Pid,opt ,Erule,effdate
 from mycte
 where dr=1




drop table  #Tab 
 

https://stackoverflow.com/questions/11202878/partition-function-count-over-possible-using-distinct


Remove last Line from a File Powershell

Since version 5, we can use Select skiplast . for earlier versions, we can use search or array count to skip the last row.


$q = @"

SELECT  [CustomerID],[CompanyName]
  FROM [Northwind].[dbo].[Customers]
 where CustomerID='XXXXXX'
 UNION ALL
Select '' CustomerID, ''CompanyName

"@
Invoke-SQLCmd -ServerInstance $svr.Name  -Database $db -Query $q |  Export-Csv -NoTypeInformation -delimiter "`t" -Path $myPath0
gc $myPath0 | % {$_ -replace '"', ""} | Select -SkipLast 1 | out-file $myPath -Fo -En ascii


Use -notlike:

 
 $q = @" SELECT  [CustomerID],[CompanyName] FROM [Northwind].[dbo].[Customers] where CustomerID='XXXXXX' UNION ALL Select 'dummy' CustomerID, ''CompanyName "@ Invoke-SQLCmd -ServerInstance $svr.Name  -Database $db -Query $q |  Export-Csv -NoTypeInformation -delimiter "`t" -Path $myPath0 gc $myPath0 | % {$_ -replace '"', ""} | Where-Object {$_ -notlike "*dummy*"}| out-file $myPath -Fo -En ascii

–Or use array count:

 
 $q = @" SELECT [CustomerID],[CompanyName] FROM [Northwind].[dbo].[Customers] where CustomerID='XXXXXX' UNION ALL Select 'dummy' CustomerID, ''CompanyName "@ Invoke-SQLCmd -ServerInstance $svr.Name -Database $db -Query $q | Export-Csv -NoTypeInformation -delimiter "`t" -Path $myPath0 $file=gc $myPath0 $file[0..(($file).count - 2)] | % {$_ -replace '"', ""} | out-file $myPath -Fo -En ascii

https://www.poshcodebear.com/blog/2014/2/1/removing-the-first-or-last-lines-from-a-text-file

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/06598ae8-cfc1-4c82-9718-88fd47f91f8b/powershell-converttocsv-how-do-you-print-headers-only-when-no-results-returned-from?forum=transactsql