featuredimage

شش روش مختلف برای کپی جداول بین پایگاه های داده در SQL Server

در این مقاله ، مهارت های کلیدی مورد نیاز برای کپی جداول بین اینستنس های SQL Server از جمله پایگاه های داده داخلی و SQL ابری را خواهید آموخت. در این مقاله ، من چندین روش کپی کردن جدول (های) بین پایگاه های داده SQL را بررسی می کنم ، که به شما کمک می کند مزایا و معایب هر گزینه را مشاهده کنید.

معرفی

اما قبل از شروع مقاله ، بیایید اهداف مقاله را مرور کنیم. سپس به مرور کلی هر ماژول یا روش می پردازیم. در این راهنما ، ما به طور مختصر چندین جنبه از گزینه های داخلی موجود در SQL Server را مورد بحث قرار می دهیم ، و همچنین به شما نشان می دهیم که می توانید از چند ابزار PowerShell و 3rd party برای کپی جداول SQL بین پایگاه های داده و همچنین اینستنس ها استفاده کنید. در ابتدای هر روش ، اطلاعات کافی در مورد ماژول های زیر را در اختیار شما قرار داده ام.

موارد مورد بررسی:

1- معرفی

2- روشهای مختلف کپی جداول را مورد بحث قرار دهید

  • استفاده از کتابخانه کلاس دات نت برای کپی جداول با PowerShell
  • استفاده از ویزارد import , Export
  • استفاده از sqlpackage.exe – روش extract و publish
  • استفاده از ویزارد Generate Scripts در SSMS (SQL Server Management Studio)
  • با استفاده از دستور INSERT INTO SQL

3- موارد بیشتر

شروع کنید

در SQL Server ، کپی جداول بین پایگاه داده های اینستنس SQL مشابه نسبت به کپی داده ها بین سرورهای راه دور آسان تر است. برای به حداقل رساندن بار کاری در پایگاه داده تولید ، همیشه توصیه می شود که پایگاه داده را از فایل پشتیبان به پایگاه داده جدید بازگردانید و سپس از بهترین روش ها برای کپی داده ها در پایگاه داده مورد نظر استفاده کنید. باز هم ، این بستگی به تعداد جداول ، اندازه و فضای موجود دارد. اگر اندازه جدول (ها) بیش از 50 درصد از کل حجم پایگاه داده باشد ، روش پشتیبان گیری و بازیابی یک گزینه توصیه شده است.

در برخی موارد ، ممکن است مجبور شوید چند جدول  بسیار بزرگ را کپی کنید ، و سپس احتمالاً در پی  جدا کردن filegroupها و انجام یک روش پشتیبان گیری و بازیابی partial برای کپی کردن داده باشید.

کلاس شی SqlBulkCopy برای کپی داده با PowerShell

PowerShell همیشه اولین انتخاب من برای هر کار مدیریتی است. Net. یک کتابخانه کلاس SqlBulkCopy برای بارگذاری گسترده جدول (ها) در پایگاه داده فراهم می کند.

اسکریپت PowerShell

اسکریپت PoSH زیر تابعی به نام Get-SQLTable ایجاد می کند. این تابع چندین پارامتر اجباری دارد.

function Get–SQLTable
{
[CmdletBinding()]
param(
[Parameter(Mandatory=$true)]
[string] $SourceSQLInstance,
 
[Parameter(Mandatory=$true)]
[string] $SourceDatabase,
[Parameter(Mandatory=$true)]
[string] $TargetSQLInstance,
[Parameter(Mandatory=$true)]
[string] $TargetDatabase,
[Parameter(Mandatory=$true)]
[string[]] $Tables,
 
[Parameter(Mandatory=$false)]
[int] $BulkCopyBatchSize = 10000,
 
[Parameter(Mandatory=$false)]
[int] $BulkCopyTimeout = 600
)
$sourceConnStr = “Data Source=$SourceSQLInstance;Initial Catalog=$SourceDatabase;Integrated Security=True;”
$TargetConnStr = “Data Source=$TargetSQLInstance;Initial Catalog=$TargetDatabase;Integrated Security=True;”
try
{
Import–Module –Name SQLServer
write–host ‘module loaded’
$sourceSQLServer = New–Object Microsoft.SqlServer.Management.Smo.Server $SourceSQLInstance
$sourceDB = $sourceSQLServer.Databases[$SourceDatabase]
$sourceConn= New–Object System.Data.SqlClient.SQLConnection($sourceConnStr)
$sourceConn.Open()
 
foreach($table in $sourceDB.Tables)
{
$tableName = $table.Name
$schemaName = $table.Schema
$tableAndSchema = “$schemaName.$tableName”
 
if ($Tables.Contains($tableAndSchema))
{
$Tablescript = ($table.Script() | Out–String)
$Tablescript
 
Invoke–Sqlcmd `
–ServerInstance $TargetSQLInstance `
–Database $TargetDatabase `
–Query $Tablescript
$sql = “SELECT * FROM $tableAndSchema”
$sqlCommand = New–Object system.Data.SqlClient.SqlCommand($sql, $sourceConn)
[System.Data.SqlClient.SqlDataReader] $sqlReader = $sqlCommand.ExecuteReader()
$bulkCopy = New–Object Data.SqlClient.SqlBulkCopy($TargetConnStr, [System.Data.SqlClient.SqlBulkCopyOptions]::KeepIdentity)
$bulkCopy.DestinationTableName = $tableAndSchema
$bulkCopy.BulkCopyTimeOut = $BulkCopyTimeout
$bulkCopy.BatchSize = $BulkCopyBatchSize
$bulkCopy.WriteToServer($sqlReader)
$sqlReader.Close()
$bulkCopy.Close()
}
}
 
$sourceConn.Close()
 
}
catch
{
[Exception]$ex = $_.Exception
write–host $ex.Message
}
finally
{
#Return value if any
}
}
متغیر آرایه ای tables$ برای اختصاص لیست جدول (ها) برای کپی به پایگاه داده هدف استفاده می شود.

اجازه دهید از تابع Get-SQLTable با پارامترهای ذکر شده در زیر استفاده کنیم تا جداول را از پایگاه داده Adventureworks2016 در “HQDBT01” در پایگاه داده Adentureworks2016 دراینستنس hqdbt01/sql2017 “کپی کنیم.

خروجی جداول OPERATION و OPERATION_DETAIL را که در اینستنس مورد نظر کپی شده اند را میتوانید مشاهده کنید.

ویزارد import , export در SSMS

بیایید نگاهی به ویزارد بیندازیم. رابط کاربری بسیار شبیه به سایر ویزاردهای دیگر است ، به شما این امکان را می دهد که به راحتی در یک فرآیند گام بردارید و فرایند کپی داده را با نوشتن کد بسیار کم یا بدون کد اجرا کنید. برای انجام این کار ، ما گزینه های بسیار کمی داریم که می توانیم در داخل ویزاردانجام دهیم. با این حال ، برای وارد کردن و انتقال داده ها از یک منبع به منبع دیگر ، این واقعا یک ابزار عالی است. اگر می خواهید تقریباً هر نوع تبدیلی (transformaation) را انجام دهید اما نمی خواهید از این ابزار استفاده کنید ، ممکن است لازم باشد از Visual Studio Data Tools (VSDT) استفاده کنید و جریان داده را انجام دهید.

پس بیایید شروع کنیم. اولین مورد این است که Microsoft SQL Server Management Studio (SSMS) را باز کنید. و ما از پایگاه داده AdventureWorks2016 استفاده می کنیم و آن را به اینستنس دیگری از SQL منتقل می کنیم.

Object Explorer را باز کنید ، پایگاه داده را پیدا کنید ، راست کلیک کرده و Tasks را انتخاب کرده و گزینه Export Data را انتخاب کنید.

در Data Source ، اگر  کمبوباکس را باز کنیم، منابع مختلفی را می بینید که می توانیم استفاده کنیم. ما از SQL Native Client 11.0 ، پروایدر SQL استفاده می کنیم.

در مرحله بعد ، نام سرور ، توصیه می شود نام سرور و پایگاه داده را در ویزارد import and export با استفاده از لیست کشویی انتخاب کنید. حال next.

حالا  برای انتخاب مقصد (destinaion)، دوباره پروایدر SQL ، نام سرور و پایگاه داده را به جای تایپ کردن ، از لیست کشویی انتخاب کنید، و بعد Next.

در Select source Tables and Views ، آبچکتها را برای کپی در مقصد انتخاب کنید ، در غیر این صورت می توانید یک کوئری بنویسید. اما در اینجا ما فقط می خواهیم داده ها را کپی کنیم. در این مورد ، بیایید dbo.Cities و Person.Address را وارد کنیم.

Next را کلیک کنید

دراین مرحله ما آماده اجرای کار کپی هستیم. اجازه دهید ما Run immediately را  انتخاب کرده و Next را بزنیم

در این مرحله ما می توانیم خلاصه ای از عملی که قرار است با استفاده از ویزارد انجام دهیم را مشاهده کنیم

برای انجام کار روی Finish کلیک کنید.

پس از اجرای موفقیت آمیز کار ، می توانیم خروجی را اعتبارسنجی و بررسی کنیم.

استفاده از sqlpackage.exe – روش استخراج و انتشار (Extract and Publish)

Sqlpackage یک ابزار خط فرمان است که فرآیند استخراج “Schema-and-data” را خودکار کرده و فایل تولید شده را در پایگاه داده هدف منتشر می کند. ابزار خط فرمان SqlPackage.exe کامپوننت داخلی SQL Server Data Tools (SSDT) است.

با استفاده از Sqlpackage.exe ، می توانید اسکیما و داده ها را استخراج کرده و سپس فقط داده های جدول (ها) فهرست شده را پابلیش کنید. در این فرایند ، اشیاء مانند Stored Procedures ، Functions و غیره در فایل .dacpac استخراج می شوند ، اما هنگام انتشار محتوا در پایگاه داده هدف، آنها مشمول نخواهند شد.

امکان فهرست بندی جداول وجود دارد. در تعیین جداول جداگانه ، ابتدا باید /p: ExtractAllTableData = False و سپس ویژگی /p: TableData را اضافه کنید تا هر جدول را در قالب Schema.Table مشخص کنید.

مثال زیر از ویژگی /p:TableData برای سه جدول استفاده می کند. مشاهده می کنید که جداول به صورت dbo.orders ارجاع داده شده اند.

/p:TableData=dbo.Orders

/p:TableData=Orders.Orders

/p:TableData=Person.Address

SqlPackage /Action: Extract /SourceDatabaseName: Adventureworks2016 /SourceServerName: HQDBT01 /TargetFile:F:\PowerSQL\smartbackup\AdventureWorks2016.dacpac /p: IgnoreExtendedPropertata = True /p: = dbo.citiesDemo

بیایید اسکریپت را آماده کنیم تا فرآیند استخراج و انتشار را خودکار کند

متغیر ENVIRONMENT را تنظیم کنید. بسته به نصب SSDT ممکن است فایل در سایر دایرکتوریها یافت شود. در این مورد ، SqlPackage.exe در پوشه C: \ Program Files (x86) \ Microsoft SQL Server \ 140 \ DAC \ bin \ یافت می شود.

مقادیر ورودی را آماده کنید

  1. Backup Directory
  2. Source Database
  3. Source SQL Server instance
  4. Target database
  5. Target SQL Server instance

SqlPackage.exe را با یکعمل extract در اینستنس SQL  سورس اجرا کنید

برای یافتن پایگاه داده مورد نظر ، T-SQL را اجرا کنید

SqlPackage.exe را با یک عمل publish در اینستنس SQL هدف اجرا کنید

# Environment PATH variable
$Variables=$env:PATH
 
#Check the path existence of the SqlPackage.exe and print its status
 
IF (–not $Variables.Contains( “C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin”))
{
write–host “SQLPackage.exe path is not found, Update the environment variable”
$ENV:PATH = $ENV:PATH + “;C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin;”  
}
 
#the input parameters
 
$BackupDirectory=“F:\PowerSQL\smartbackup\“
$DatabaseName=“AdventureWorks2016”
#Source SQL Server Instance
$SourceServerName=“HQDBT01”
#target SQL Instance
$TargetserverName=“HQDBT01\SQL2017”
 
#Prepare the target filename
$dir  = [io.path]::GetDirectoryName($BackupDirectory)
 
#set the filename, the database should be a part of the filename
$filename = “AdventureWorks2016_Rpt”
#extension must be dacpac
$ext = “dacpac”
# Prepare FULL PATH
$TargetFilePath  = “$dirName\$filename-$(get-date -f yyyyMMddhhmmss).$ext”
#print FULL file path
$TargetFilePath
 
#Run the SqlPackage tool to extract the data
 
SqlPackage /Action:Extract /SourceDatabaseName:$DatabaseName /SourceServerName:$SourceServerName /TargetFile:$TargetFilePath /p:IgnoreExtendedProperties=True  /p:ExtractAllTableData=FALSE  /p:TableData=dbo.Orders /p:TableData=dbo.Address
 
 
#Get the latest file in a given directory
 
 
$NewestBacPacFile = Get–ChildItem –Path $dirName\$filename*.$ext | Sort–Object LastAccessTime –Descending | Select–Object –First 1
#print the latest bacfile name depending the name of the database
$file=“$NewestBacPacFile”
 
$FILE
 
#If exists then drop the database
 
$dropTSQL=
@“
IF EXISTS (SELECT * FROM [sys].[databases] WHERE [name] = ‘$DatabaseName’) DROP DATABASE $DatabaseName
“@
#Using sqlcmd, execute the DropTSQL on the target instance.
 
SQLCMD –S $TargetserverName –U SA –P Api1401$ –Q $dropTSQL
 
 
#Publish the data in the target database using sqlpackage.exe
 
 
SqlPackage.exe /a:publish /sf:$file /tsn:$TargetserverName /tdn:$DatabaseName /tu:SA /tp:Api1401$

خروجی:

در خروجی ، می بینید که جدول dbo.orders و جداول dbo.address پردازش می شوند.

ایجاد اسکریپت با استفاده از SQL Server Management Studio

در این بخش ، ما روش دیگری را برای ایجاد “schema and data” برای آبجکتهای پایگاه داده SQL Server مورد بحث قرار می دهیم.

بیایید مراحل ایجاد یک اسکریپت SQL را که شامل “schema and data” است مشاهده کنیم.

به اینستنس SQL Server متصل شوید

Object Explorer را باز کرده و پایگاه داده را پیدا کنید

روی پایگاه داده راست کلیک کنید ، Tasks را انتخاب کنید و سپس روی Generate Scripts… کلیک کنید. پس از آن ، Script Wizard باز می شود. روی “next” کلیک کنید.

در صفحه Select Object ، گزینه Select specific database objects را فعال کنید. اشیاء مورد نظر را انتخاب کرده و روی Next کلیک کنید.

در Set Scripting Options ، نوع خروجی را انتخاب کرده و روی دکمه Advanced کلیک کنید. در این حالت ، نوع خروجی دوباره به پنجره کوئری هدایت می شود.

در Advanced Scripting Options   Schema and Data را از لیست کشویی انتخاب کرده و روی okکلیک کنید.

در مرحله بعد ، صفحه خلاصه به شرح کلی کل فرایند می پردازد. Next را کلیک کنید

اکنون ، صفحه Save or Publish Scripts پیشرفت کل فرایند را نشان می دهد. می توانید وضعیت کل اسکیما و فرآیند تولید داده را زیر نظر داشته باشید.

INSERT INTO SQL

این نیز یک گزینه برای کلون کردن جدول از پایگاه داده به دیگری است که میتوانید از آن استفاده نمایید. 

برچسب ها: بدون برچسب

نظر بگذارید

آدرس ایمیل شما منتشر نخواهد شد. قسمتهای مورد نیاز علامت گذاری شده اند *