info@techdevops.com | 437-991-3573 | Data Engineering Services
TechDevOps.com
Resources Tools
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance



Automate Azure SQL Database Backup to Azure Virtual Machine running SQL Server
by BF (Principal Consultant; Architecture; Engineering)
2017-08-24









SqlPackage.exe is a command line utility that automates various database tasks. The two tasks of relevance are Export and Import:

Export: Exports a live database - including database schema and user data - from SQL Server or Microsoft Azure SQL Database to a BACPAC package (.bacpac file).

Import: Imports the schema and table data from a BACPAC package into a new user database in an instance of SQL Server or Microsoft Azure SQL Database.



Solution:

Navigate to the sqlpackage install location to get the path: Program Files (x86)\Microsoft SQL Server\###\DAC\bin

Using Command Prompt:

For Exporting the bacpac:

sqlpackage.exe /Action:Export /ssn:xyx.database.windows.net /sdn:DB /su:user /sp:pwd /tf:"F:\db.bacpac"

For importing the exported bacpac:

sqlPackage.exe /Action:Import /sf: F:\db.bacpac /TargetDatabaseName:DB /TargetServerName:NYCSRV01 /targetuser:user /targetpassword:pwd


These key commands can now be integrated into Batch Program or SQL Job to automate the backup & restore on a scheduled process.




"Successfully exported database and saved it to file 'F:\db.bacpac'."

Monitoring:




Resources:

SqlPackage.exe