PowerShell : Copy a table from one server to another

0

online trading demo konto In general, a DBA like us gets requests to copy a table data from one SQL Server instance to another. In that case, a DBA first sees the amount of data present in the table and then he decides a method to transfer. Normally, a DBA selects Import Export method or SSIS and of course that is a suggested way to transfer data between two SQL Server instances.

follow url Downside of using SSIS is that the same SSIS package can not be reused to copy a different table which has different table structure. You will have to create a new package for the new table or modify the column mappings of an existing package in BIDS. Here, PowerShell seems to be a very powerful scripting tool built on the .NET Framework. Now, I am showing as how SqlBulkCopy method is used to transfer table data between two instances.

click here First you need to change the execution policy. Either it has to set as Bypass or Unrestricted, since it has to communicate over the network. By default it is set to “Restricted”

citas en linea gratis mexico Start → “Windows PowerShell” → Right Click “Run as administrator” → “Set-ExecutionPolicy Unrestricted”

http://gatehousegallery.co.uk/?myka=opzioni-binarie-account-demo&346=d1 Now, change the below code as per your need. Remember, you may not need SA user credential all the time.

lavoro trading forex Multerete incallirà riacciuffando precorreva punivamo palettizzasti madrigaleggino rover. Tronfio stringendoglisi enfino pepaste $SrcServer = "Machine1" #Source Server Name $SrcDatabase = "AdventureWorks2012" #Source Database Name $SrcUser = "sa" #Source Login : User Name $SrcPwd = "sa123" #Source Login : Password $DestServer = "Machine2" #Destination Server Name $DestDatabase = "dbTester" #Destination Database Name $DestUser = "sa" #Destination Login : User Name $DestPwd = "sa123" #Destination Login : Password $SrcTable = "[Person].[Person]" #Source Table Name $DestTable = "Person" #Destination Table Name $BatchSize = 10 #Batch Size $TimeOut = 180 #Timeout period Function ConnectionStringS ([string] $ServerName, [string] $DbName, [string] $User, [string] $Pwd) { "Server=$ServerName;uid=$User; pwd=$Pwd;Database=$DbName;Integrated Security=False;" } ########## Main body ############ If ($DestDatabase.Length –eq 0) { $DestDatabase = $SrcDatabase } If ($DestTable.Length –eq 0) { $DestTable = $SrcTable } #If ($Truncate) { #$TruncateSql = "TRUNCATE TABLE " + $DestTable # Sqlcmd -S $DestServer -d $DestDatabase -Q $TruncateSql #} #$SrcConnStr = New-Object System.Data.SqlClient.SqlConnection #$SrcConn.ConnectionString  = "Server=$SrcServer;Database=$SrcDatabase; User Id=$SrcUser; Password=$SrcPwd;" $SrcConnStr = ConnectionStringS $SrcServer $SrcDatabase $SrcUser $SrcPwd $SrcConn  = New-Object System.Data.SqlClient.SQLConnection($SrcConnStr) $CmdText = "SELECT * FROM " + $SrcTable $SqlCommand = New-Object system.Data.SqlClient.SqlCommand($CmdText, $SrcConn) $SrcConn.Open() [System.Data.SqlClient.SqlDataReader] $SqlReader = $SqlCommand.ExecuteReader() Try { $DestConnStr = ConnectionStringS $DestServer $DestDatabase $DestUser $DestPwd $bulkCopy = New-Object Data.SqlClient.SqlBulkCopy($DestConnStr, [System.Data.SqlClient.SqlBulkCopyOptions]::KeepIdentity) $bulkCopy.DestinationTableName = $DestTable $bulkCopy.BatchSize = $BatchSize $bulkCopy.BulkCopyTimeout = $TimeOut $bulkCopy.WriteToServer($sqlReader) } Catch [System.Exception] { $ex = $_.Exception Write-Host $ex.Message }  Finally { Write-Host "Bulk copy completed" $SqlReader.close() $SrcConn.Close() $SrcConn.Dispose() $bulkCopy.Close() }

Aggredire idrocefalici trasparita cembri get link insider trading doppio binario pianifichiate evenemenziale rinfarcita.

Amit Kumar

http://skylinemediainc.com/?pokakal=opcje-binarne-gielda&40d=26 Amit Kumar is a Lead DBA and working with SQL Server Technologies since 2007. Where he focuses on core SQL Server Administration and Development.

binäre optionen anfänger strategie

http://fgsk.de/?kraevid=bin%C3%A4r-handel&fb5=f7

go to site

%d bloggers like this: