More Group Sites
Education Books
School Rankings
Jobless Net
Better Home
Enviro++
更好教育论坛
Welcome Guest! To enable all features please Login or Register.

Notification

Icon
Error

Share
Options
View
Go to last post Go to first unread
hong  
#1 Posted : Thursday, 11 March 2021 6:51:53 AM(UTC)
hong

Rank: Administration

Reputation:

Groups: AcademicCoachingSchool, admin, Administration, BookSeller, CatholicSchool, CoachingAdult, CoachingProfessional, CoachingSports, ExtraCurriculumCoaching, IndependentSchool, Moderator, MusicTeacher, PrivateSchool, PublicSchool, SelectiveSchool, tutor
Joined: 23/11/2008(UTC)
Posts: 523

Copy Azure database to local machine - can export backpac file from SSMS or Azure portal

In SSMS,
export data tier application - export your database to a local .bacpac file.
extract data tier application - export your definitions to a local .dacpac file. "Export data-tier" includes data, "Extract data-tier" gives you definitions only

1. within SSMS, select the target database, Tasks, export data-tier application, save it to a local disk. It may not have an extension because it is actually a zip file. 2. Connect to a local database (express), from Databases, import data-tier application...

Prefer export data tier application from SSMS, so I don't need to save it to a blob storage which has a cost, even though it's cheap. 

No need to copy to a new instance in Azure portal because of the cost.

In Azure portal, copy database allows you to easily move a database from an instance of SQL server to another one. Export database save the database to a bacpac file in the OS file system (like a backup). Then you can copy those files and import them whenever and wherever you want.

Notes:

  1. You can import the exported bacpac file into an Azure SQL server.
  2. On local db, back up to *.bak and restore later.

There are differences between .bacpac and .bak (backup copy). They are not the same and are not interchangeable.

A BACPAC file is a ZIP file with an extension of BACPAC containing the metadata and data from a SQL Server database. A BACPAC file can be stored in Azure blob storage or in local storage in an on-premises location and later imported back into Azure SQL Database or into a SQL Server on-premises installation"

A backup is a page by page copy of your database that takes into account the fact that active transactions are occurring so that it has a recovery phase when a restore is run that allows for full ACID compliance of your data at the end of a restore. As Beatrix has already said, the BACPAC is just an export of your data and structures stuck into a zip file. That export of the data is not, in any way, aware of the fact that transactions could be occurring at the time the BACPAC is created. Therefore, when restoring a BACPAC, you could be getting data that is corrupted (deletes half completed, stuff like that) and not ACID compliant. Because of this, it's strongly recommended that you only, ever, take a BACPAC from a database without connections. The best way to deal with this is to create a copy of your database (presumably within Azure) and then create the bacpac from the copy. [Diff between .bak and .backpac files?]

Bear in mind, your server might be busy while exporting. the best practice is to copy first and then export data-tier application. This can be done through Azure portal or T_SQL. Drop the copy / backup database when the job is done to save the cost of the copy instance.

Backup and Restore Options for Azure SQL DB

It really isn’t possible to create traditional backups for Azure SQL Database, the closet thing we have is an export of the database into a BACPAC file. This sort of file contains the metadata and data for the database. Now, this is definitely not a replacement for the built-in backup system more so a supplement. For an export to be transactionally consistent, you must ensure either that no write activity is occurring during the export, or that you are exporting from a transactionally consistent copy of your Azure SQL database. Quite simply best practice should be followed which is summarised by the screen shot below.

mybacpac

Step 1: Create the database copy via T-SQL.

CREATE DATABASE db2 AS COPY OF db1

Step 2: Export Copy command example syntax. (Note the Administrator Login and password are the credentials for the SQL Server). This is best done via PowerShell.

...

Step 3: Simply remove the copy once the bacpac has been produced.

DROP DATABASE db2

Copying Azure database to local machine is a lot easier now. See traditional ways:

How do I copy SQL Azure database to my local development server?

Edited by user Friday, 12 March 2021 5:38:06 AM(UTC)  | Reason: Not specified

Sponsor
Rss Feed  Atom Feed
Users browsing this topic
Guest
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.