bacpac | Project archived as I no longer use Arch

I want to deploy a NAV 2013 database on Azure, with near real time capabilities (if the data is refreshed once a day, that is enough).

I am using this guide to connect to a test NAV sql server from an Azure VM, and will export data tier application (bacpac) file from NAV and import into Azure SQL.

My understanding is, that exporting this bacpac file will create a copy of the database in Azure. Does it also refresh the data when NAV data is refreshed?

If not, how can I set up automatic refresh of data?

I have an SQL Azure database and connect to it in SQL Server Management Studio. I do Export Data Tier Application and then Import Data Tier Application for the .bacpac file to get it into my localdb. Or I use Tasks - Deploy Database.

Either way, it worked up until recently and now I get an error

Online index operations can only be performed in Enterprise edition of SQL Server

I am using SQL Server Management Studio versions below (from Help - About). Any ideas?

I want to export my SQL Azure database to a file test.bacpac, but I failed:

One or more unsupported elements were found in the schema used as part of a data package.
Error SQL71564: Error validating element [dbo].[IsMyUserExisted]: The element [dbo].[IsMyUserExisted] cannot be deployed as the script body is encrypted.

The question is, why can't I back up my database like in SQL Server 2008, 2017 etc (just backup database, and then restore database).

I know Azure does its own backups in the cloud. However, due to company policy I need to generate a local backup copy of the database and be date-time stamped.

I've read this, and it has allowed me to create a .bacpac file and import it into our on-prem SQL server (2019). What I want is a way to save the bacpac file on a network folder, on a regular basis.

UPDATE - no I don't have to, store the bacpac file in an on-prem database. I only mentioned it to say, yes I can do this extra step. What I really want is to simply save the bacpac file, date-stamped in the filename, and in a network folder on-prem.

I need to convert all varchar columns in about 40 tables (filled with the data) to nvarchar columns. It is planned to happen in a dedicated MS SQL server used only for the purpose. The result should be moved to Azure SQL.

Where should be the conversion done: on the old SQL, or after moving it on Azure SQL Server?

According to Remus Rusanu's answer https://stackoverflow.com/a/8157951/1346705, new nvarchar columns are created in the process, and the old varchar columns are dropped. The space can be reclaimed by DBCC CLEANTABLE or using ALTER TABLE ... REBUILD. Are the dropped varchar columns packed into the backup table, or does the backup/restore also remove the dropped columns?

Can the process be somehow automated using a universal SQL script? Or is it necessary to write the script for each individual table?

Context: We are the 3rd party with respect to the enterprise information system. Our product reads from the information system SQL database and presents the data the way that would otherwise be expensive to implement in the IS. The enterprise information system is now migrated to the new version and is to be run on Azure SQL. The database of the IS have been changed heavily, and one of the changes was to abandon the old 8-bit text encoding (varchar) and to use Unicode instead (nvarchar). Our system was used also for collecting data typed manually -- using the same encoding that the old IS used.

Migration is to be done via doing old version of backup (SqlCmd that produces xxx.bak files), restoring on another good old SQL server. Then we run the script that removes all the tables, views, and stored procedures that can be reconstructed from the IS. One of the main reasons is that the SQL code uses features that are not accepted by the new backup tool SqlPackage.exe to produce xxx.bacpac file. Then the bacpac file is restored in Azure SQL.

Hello folks first post in stack, btw wonderful community and helps out a lot.

like mentioned in the title what is the best way to copy such a large database? we got an ~ 500 GB Database and im currently moving this database from managed instance to a azure single database using smss:smss copy via deploy to microsoft azure sql database and it takes me right now 22 hours. i feel like im back in early 20s.

it's all in the same subscription and also in the same network configuration. afaik the process of that is that smss creates a bacpac file and then import it back to the single database. but 16 hours is just too long. so do you know any better option to do this quicker because i've a hell of more and partly larger databases to copy.

I'm trying to create a new SQL Azure database from a bacpac file I exported locally and uploaded as a BLOB to a storage account container.

When I choose Import Database on the SQL server and choose to use the uploaded bacpac as the source for the import, the import fails with the error "The storage URI is not valid".

What's wrong?

TLDR;

How do I add AD users to an Azure database created in a DevOps pipeline?

Our DBA has a process to create daily bacpacs from production and store them in an azure blob container, I then provided the developers with a DevOps pipeline that restores a specified bacpac into the development server. The issue is that the developers can't connect to these databases using their domain account. I don't know how to give them access because

1. AFAIK I can't connect with a domain user to the database from the pipeline
2. Azure requires that the connected user is an AD user to be able to create other AD users

I could work around it by creating an sql user and grant that user permissions in the pipeline, however the company is actually moving away from sql users and relying more on AD security and MFA, so this isn't really a solution for me.

What are the best ways to Back up and restore Azure SQL Database schema in Azure cloud?

I have tried creating bacpac files, but problem with that is, it will be imported as a new database. I want to back up and restore specific schema only within the same database.

Another way i am looking at is creating a sql script file which contains data and schema using SSMS. But here size of the sql script is huge.

Any help is greatly appreciated