Automate SQL Server Database Migration To Azure With PowerShell | SQL Server Database to Azure
662 بار بازدید -
2 سال پیش
-
In this video, you will
In this video, you will learn how to migrate SQL Databases to Azure SQL Server in a few steps.
Private Database Mastery Level-Up Coaching: https://cutt.ly/5wukGpf3
Download my 6-Figure template for free: https://subscribepage.io/resume-template
Connect with me on Discord: Discord: discord
LinkedIn: LinkedIn: emarmorrison
** Advance your career or learn some new skills with one of these courses **
SQL Server 2022 Essential Training: https://cutt.ly/March2023
Kick Start Your PostgreSQL DBA Training: https://cutt.ly/postgres
AWS Oracle RDS Database Administration Essential Training: https://cutt.ly/Oracle_RDS
Mastering SQL Server on AWS: https://cutt.ly/L6AC8fX
Azure SQL Database Administration (DP300): https://cutt.ly/azure-sql-dp300
==========Powershell Script===============
#Source SQL Server
$SourceUser = "yourdb_dev"
$SourcePassword = "******"
$SourceServer = "testsvr"
$BackupDirectory ="D:\data\MSSQL\BACKUP"
$DatabaseName ="RDSTest"
$SqlPackageLocation = "C:\Program Files\Microsoft SQL Server\160\DAC\bin"
#Destination SQL Server
$TargetPassword = "********"
$TargetServer = "xxxxx.database.windows.net"
$TargetDBname = $DatabaseName+"_n"
$targetUser = "youruser"
$ext = "bacpac"
#target filepath is a combination of the directory and filename appended with year month and day hour minutes and seconds
$TargetFilePath = "$BackupDirectory\$DatabaseName-$(get-date -f yyyy-MM-dd).$ext"
#print the full path of the target file path
$TargetFilePath
try
{
This is a simple user/pass connection string.
Feel free to substitute "Integrated Security=True" for system logins.
$connString = "Data Source=$SourceServer; Database=$DatabaseName;User ID=$User;Password=$Password"
#Create a SQL connection object
$conn = New-Object System.Data.SqlClient.SqlConnection $connString
#Attempt to open the connection
$conn.Open()
if($conn.State -eq "Open")
{
#excute SQL Script on database
Invoke-Sqlcmd -InputFile "D:\Google Drive\scrips\Scripts\Azure\drop_table.sql" -ServerInstance $SourceServer -Database $DatabaseName
#exports source database
$cmd = "& '$SqlPackageLocation\sqlpackage.exe' /a:Export /tf:$TargetFilePath /ssn:$SourceServer /sdn:$DatabaseName /su:$SourceUser /sp:$SourcePassword"
Invoke-Expression $cmd
Invoke-Sqlcmd -InputFile "D:\Google Drive\scrips\Scripts\Azure\create_table.sql" -ServerInstance $SourceServer -Database $DatabaseName
$conn.Close()
#imports database to target host
$cmd = "& '$SqlPackageLocation\sqlpackage.exe' /a:Import /sf:$TargetFilePath /tsn:$TargetServer /tdn:$TargetDBname /tu:$TargetUser /tp:$TargetPassword"
Invoke-Expression $cmd
}
}
catch
{
Write-Host($error)
}
Private Database Mastery Level-Up Coaching: https://cutt.ly/5wukGpf3
Download my 6-Figure template for free: https://subscribepage.io/resume-template
Connect with me on Discord: Discord: discord
LinkedIn: LinkedIn: emarmorrison
** Advance your career or learn some new skills with one of these courses **
SQL Server 2022 Essential Training: https://cutt.ly/March2023
Kick Start Your PostgreSQL DBA Training: https://cutt.ly/postgres
AWS Oracle RDS Database Administration Essential Training: https://cutt.ly/Oracle_RDS
Mastering SQL Server on AWS: https://cutt.ly/L6AC8fX
Azure SQL Database Administration (DP300): https://cutt.ly/azure-sql-dp300
==========Powershell Script===============
#Source SQL Server
$SourceUser = "yourdb_dev"
$SourcePassword = "******"
$SourceServer = "testsvr"
$BackupDirectory ="D:\data\MSSQL\BACKUP"
$DatabaseName ="RDSTest"
$SqlPackageLocation = "C:\Program Files\Microsoft SQL Server\160\DAC\bin"
#Destination SQL Server
$TargetPassword = "********"
$TargetServer = "xxxxx.database.windows.net"
$TargetDBname = $DatabaseName+"_n"
$targetUser = "youruser"
$ext = "bacpac"
#target filepath is a combination of the directory and filename appended with year month and day hour minutes and seconds
$TargetFilePath = "$BackupDirectory\$DatabaseName-$(get-date -f yyyy-MM-dd).$ext"
#print the full path of the target file path
$TargetFilePath
try
{
This is a simple user/pass connection string.
Feel free to substitute "Integrated Security=True" for system logins.
$connString = "Data Source=$SourceServer; Database=$DatabaseName;User ID=$User;Password=$Password"
#Create a SQL connection object
$conn = New-Object System.Data.SqlClient.SqlConnection $connString
#Attempt to open the connection
$conn.Open()
if($conn.State -eq "Open")
{
#excute SQL Script on database
Invoke-Sqlcmd -InputFile "D:\Google Drive\scrips\Scripts\Azure\drop_table.sql" -ServerInstance $SourceServer -Database $DatabaseName
#exports source database
$cmd = "& '$SqlPackageLocation\sqlpackage.exe' /a:Export /tf:$TargetFilePath /ssn:$SourceServer /sdn:$DatabaseName /su:$SourceUser /sp:$SourcePassword"
Invoke-Expression $cmd
Invoke-Sqlcmd -InputFile "D:\Google Drive\scrips\Scripts\Azure\create_table.sql" -ServerInstance $SourceServer -Database $DatabaseName
$conn.Close()
#imports database to target host
$cmd = "& '$SqlPackageLocation\sqlpackage.exe' /a:Import /sf:$TargetFilePath /tsn:$TargetServer /tdn:$TargetDBname /tu:$TargetUser /tp:$TargetPassword"
Invoke-Expression $cmd
}
}
catch
{
Write-Host($error)
}
2 سال پیش
در تاریخ 1401/04/19 منتشر شده
است.
662
بـار بازدید شده