Users: 30k; Pages Per Session: 2.5p; Avg. Session Duration: 1:35mi
TechDevOps.com
Explore Tools
{Featuring Microsoft Technologies + Enterprise Data + Cloud Computing}



Automate Azure SQL Database Backup to Azure Virtual Machine running SQL Server
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
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