CODEWITHSUNDEEP
sql-serverazure-sql-databasebackup
Khalid
Khalid

Reputation: 33

Error: Statement 'BACKUP DATABASE' in not supported in this version of SQL Server

While writing this question on this site, many similar questions were suggested on the same topic by others, but I did not find the desired answer. I'm trying to write code in WPF C# to create a database management application But the following statement does not work with SQL:

BACKUP DATABASE MyDatabase TO DISK = 'C:\\databases\\MyTestData-21-07-2022.bak';

The SQL version I'm working on is:

Microsoft SQL Azure (RTM) - 12.0.2000.8 May 12 2022 23:11:24 Copyright (C) 2022 Microsoft Corporation.

I got it through the following statement: "SELECT @@VERSION"

Can anyone give me a unique solution? Or is this not possible at all?

Thank you in advance

Upvotes: 1

Views: 6391

Answers (1)

Alberto Morillo
Alberto Morillo

Reputation: 15608

You cannot use BACKUP DATABASE or BACKUP LOG on Azure SQL Database. Native SQL Server backups are not possible on Azure SQL. Instead of Native SQL Server Backups you create bacpacs, and you can save bacpacs to a local drive on your computer or to an Azure Storage account.

On this documentation you will find how to create a bacpac of your database and save it to an Azure Storage Account. You can later download that bacpac (backup) to your local computer using Azure Storage Explorer if you want. Below and example of how to create a bacpac using PowerShell and save it to Azure Storage.

$exportRequest = New-AzSqlDatabaseExport -ResourceGroupName $ResourceGroupName -ServerName $ServerName `
  -DatabaseName $DatabaseName -StorageKeytype $StorageKeytype -StorageKey $StorageKey -StorageUri $BacpacUri `
  -AdministratorLogin $creds.UserName -AdministratorLoginPassword $creds.Password

You can also create a bacpac of your Azure SQL Database and save it to your local F: drive using sqlpackage tool

SqlPackage /Action:Export /SourceServerName:SampleSQLServer.sample.net,1433

/SourceDatabaseName:SampleDatabase /TargetFile:"F:\Temp\SampleDatabase.bacpac"

Upvotes: 2

Related Questions