script to backup azure sql database to local machine Edit

You would have used ms sql export data-tier application in ssms to create a bacpac backup to your local machine. Here is the sample script to backup azure sql database to local machine.

Solution

Microsoft has provided a command-line tool that can import/export DAC: sqlpackage.exe. You can download the same from the following link https://www.microsoft.com/en-us/download/details.aspx?id=58207. This would be installed to the C:\Program Files\Microsoft SQL Server\150\DAC\bin location.

To export a data-tier application, specify 3 parameters:

/Action:{Extract|DeployReport|DriftReport|Publish|Script|Export|Import} Specifies the action to be performed. (short form /a), we will only use Export in this example.
/SourceConnectionString:Specifies a valid SQL Server/Azure connection string to the source database. If this parameter is specified it shall be used exclusively of all other source parameters. (short form /scs)
/TargetFile: Specifies a target file (i.e., a .dacpac files) to be used as the target of action instead of a database. If this parameter is used, no other target parameter shall be valid. This parameter shall be invalid for actions that only support database targets. (short form /tf)

Run the script in the command prompt

SqlPackage.exe /a:export /scs:"Server=yourserver;Database=dbname;User ID=username;Password=yourpassword;Trusted_Connection=True;" /tf:"c:\works\beginner.bacpac"