Unable to Export SQL Database

Question

I'm trying to export the database and receive the following error:

One or more unsupported elements were found in the schema used as part of a data package. Error SQL71501: Error validating element [jobs_internal]: Schema: [jobs_internal] has an unresolved reference to object [##MS_JobAccount##].

I've tried to delete all that is related to [jobs_internal] schema, but there is one object that exists 'id_list' that I can not delete and locate. Selecting from objects returns no results. Drop schema returns an error.

SELECT *
  FROM [sys].[objects] 
  where name = 'id_list'

drop schema jobs_internal

Answer 1

Thanks for your question.

Here is a possible solution for the issue you are facing.

The way to ensure that the backup file is consistent is to export a database that has no write activity during the export or create a copy of the database and do the export from that copy.

1.     Create a database copy.
You can check how to create a copy of the database using the portal at https://docs.microsoft.com/en-us/azure/sql-database/sql-database-copy-portal 

2.     Remove the Job related objects from the copy. Please make sure you are connected to the copy.

3.     Export the database from the copy.

4.     Delete the database copy.

Let us know if this works.

Answer 2

Drop all objects in [jobs] and [jobs_internal] schemas in the following order: stored procedures, functions, views, foreign keys, tables, custom types, schemas, and users.

After that you will be able to export the database.

Answer 3

That is what helped me to restore db without deleting jobs_internal schema:

  ALTER AUTHORIZATION ON Schema::[jobs_internal] TO [dbo]