Import Tab Delimited Text File with bcp

 USE [myDB]
CREATE TABLE [dbo].[mySourceTable](
	[col1] [nvarchar](max) NULL,
	[Zip] [nvarchar](max) NULL,
	[col3] [nvarchar](max) NULL,
	[col4] [nvarchar](max) NULL,
	[col5] [nvarchar](max) NULL,
	[col6] [nvarchar](max) NULL,
	[col7] [nvarchar](max) NULL,
	[col8] [nvarchar](max) NULL,
	[col9] [nvarchar](max) NULL,
	[Latitude] [nvarchar](max) NULL,
	[Logitude] [nvarchar](max) NULL,
	[col12] [nvarchar](max) NULL


 --1.Generate XML format file 
 exec master..xp_cmdshell  'bcp  [myDB].[dbo].[mySourceTable] format nul -c -x -f  C:\temp\myFormatFileZip.xml -T  -t\t -r0x0A -S MC0555\SQL2014DEV';
 --Tab delimited and Row terminator -r0x0A

---2. bcp in with all data 
exec master..xp_cmdshell  'bcp  [myDB].[dbo].[mySourceTable] IN "C:\temp\US.txt" -f "C:\temp\myFormatFileZip.xml" -T -S MC0555\SQL2014DEV';



Import Tab Delimited Text File with dbaTool

$DataTable = Import-Csv C:\temp\mysourceZip.txt -Header "col1","Zip","col3","col4","col5","col6","col7","col8","col9","Latitude","Logitude","col12" -Delimiter  "`t"
Write-DbaDbTableData -SqlInstance MC04\myDEVserver -InputObject $DataTable -Table mydb.dbo.myStagingTable -AutoCreateTable

How to Change Primary Key Column Name in a Table with Spatial Index

Here is a list of steps for how modify a column (primary key) name with spatial index for geography data type.

--step 0  Optional
--drop  foreign keys if pointing to the primary key column in question

---Step 1 drop spatial index
 --Step 2 drop primary key constraint

 --Step 4:  add primary key constraint
--Step 3 rename column name
 sp_rename @objname = N'[dbo].[myTablewithSpatialIndexForDatatype_geography_Column].[myOldID]', @newname = N'myNewId'  

  ---Step 5: add back SPATIAL INDEX

  ---Step 6:  add foreign key back to point to the primary key after name change 


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:

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

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\

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.


 ## or



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