How to manually backup Azure SQL Database.

Question

How to manually backup Azure SQL Database.

Ayush Shrivastava 120

I am trying to look into possible options to manually backup specific database and I found this option

https://learn.microsoft.com/en-us/azure/azure-sql/database/database-export?view=azuresql&source=docs

but this says that this is not a preferred method.

Is there a better method to do manual backup and restore ?

NIKHILA NETHIKUNTA 4,600 Reputation points Microsoft External Staff

2024-11-14T20:22:32.1133333+00:00

@Ayush Shrivastava
Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

1 answer

Your answer

NIKHILA NETHIKUNTA 4,600 Reputation points Microsoft External Staff

2024-11-14T20:22:32.1133333+00:00

@Ayush Shrivastava
Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 1

Alberto Morillo 34,671 MVP Volunteer Moderator

Exporting the database as bacpac using the Portal, SSMS, Azure Data Studio, PowerShell is the only option you have to perform a manual backup at the time you want without the need to pay extra for a backup service.

Azure prefers you rely on Automated backups or long-term backups. You cannot manage automated backups, and long-term backups are based on an Azure service named Azure Backups.

NIKHILA NETHIKUNTA 4,600 Reputation points Microsoft External Staff

2024-11-15T18:49:34.54+00:00

@Ayush Shrivastava
Just checking in to see if the above answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Ayush Shrivastava 0 Reputation points

2024-12-09T10:19:59.2633333+00:00

I tried exporting a database using service principal and it's throwing error because of elastic job schemas (jobs and jobs_internal) is there a way I can skip exporting specific schemas ?
Ayush Shrivastava 0 Reputation points

2024-12-09T11:52:44.4733333+00:00

Here is the error

*** Error exporting database:One or more unsupported elements were found in the schema used as part of a data package.

Error SQL71501: Error validating element [jobs_internal].[visible_targets_formatted]: View: [jobs_internal].[visible_targets_formatted] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [jobs_internal].[database_credentials].[C], [jobs_internal].[database_credentials].[name] or [jobs_internal].[targets].[C].

Error SQL71501: Error validating element [jobs_internal]: Schema: [jobs_internal] has an unresolved reference to object [##MS_JobAccount##].

Error SQL71501: Error validating element [jobs]: Schema: [jobs] has an unresolved reference to object [##MS_JobAccount##].
Ayush Shrivastava 0 Reputation points

2024-12-09T15:52:42.4833333+00:00
I tried to resolve it by disabling schema validation but now I am getting this error and I don't know what's happening.

@Alberto Morillo

Extracting schema

Extracting schema from database

Resolving references in schema model

Validating schema model for data package

Validating schema

Exporting data from database

Exporting data

Processing Export.

Processing Table '[jobs_internal].[script_batches]'.

Processing Table '[jobs_internal].[job_cancellations]'.

Processing Table '[jobs_internal].[jobsteps]'.

Processing Table '[jobs_internal].[targets]'.

Processing Table '[jobs_internal].[target_group_memberships]'.

Processing Table '[jobs_internal].[jobs]'.

Processing Table '[pt_1477312193346142208].[t1477312193346142208_P_1477325340178120704]'.

Processing Table '[jobs_internal].[job_versions]'.

Processing Table '[jobs_internal].[__MigrationHistory]'.

Processing Table '[jobs_internal].[job_executions]'.

Processing Table '[jobs_internal].[jobstep_data]'.

Processing Table '[jobs_internal].[target_associations]'.

Processing Table '[jobs_internal].[command_data]'.

Processing Table '[jobs_internal].[job_task_executions]'.

*** Error exporting database:Could not export schema and data from database.

One or more errors occurred. (One or more errors occurred. (One or more errors occurred. (One or more errors occurred. (One or more errors occurred. (Invalid argument)))))

One or more errors occurred. (One or more errors occurred. (One or more errors occurred. (One or more errors occurred. (Invalid argument))))

One or more errors occurred. (One or more errors occurred. (One or more errors occurred. (Invalid argument)))

One or more errors occurred. (One or more errors occurred. (Invalid argument))

One or more errors occurred. (Invalid argument)

Invalid argument
Alberto Morillo 34,671 Reputation points MVP Volunteer Moderator

2024-12-10T03:34:35.8+00:00

Hi Ayush. Please read get rid of the jobs using the steps provided on this forum thread.

Hope this helps.
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Ayush Shrivastava 0 Reputation points

2024-12-10T06:27:43.54+00:00

The bottom line is I want to back up the database with jobs and job_internal schema. I am okay if the backpack does not have the jobs and jobs internal schema but I cannot delete these from the source database for sure.
Ayush Shrivastava 0 Reputation points

2024-12-10T11:08:45.4566667+00:00

It's working now.
Alberto Morillo 34,671 Reputation points MVP Volunteer Moderator

2024-12-10T15:13:20.61+00:00

You can always copy the database and the remove objects from the newly created database. You can then export the database.

I am glad it is working now.

Share via

How to manually backup Azure SQL Database.

1 answer

Your answer