Export BACPAC file of SQL database

When you need to create an archive of an Azure SQL database, you can export the database schema and data to a BACPAC file. 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.

Let\’s learn some of the ways to export BACPAC file.

Export BACPAC using Azure Portal

1. Open your SQL Database and select Export.
2. Fill the parameters as shown below.
3. Select your storage account container & enter your SQL Server admin login.
4. To check the status of your database export. Open your SQL Database server containing the database being exported. Go to Settings and then click Import/Export history

Export BACPAC using SSMS

1. Login Azure SQL Database by SSMS.
2. Right-click the database -> Tasks -> Export Data-tier Application
3. Save the .bacpac file into local disk.

Export BACPAC using SQLPackage

There is a command line tool that you can also choose to perform the import/export. The latest version is here. Sqlpackage will be installed to “C:\\Program Files\\Microsoft SQL Server\\150\\DAC\\bin” directory.

Example import/export commands

sqlpackage.exe /Action:Export /ssn:[server].database.windows.net /sdn:[database] /su:[user] /sp:[password] /tf:\”C:\\Temp\\database.bacpac\”

sqlpackage.exe /Action:Import /tsn:[server].database.windows.net /tdn:[database] /tu:[user] /tp:[password] /sf:\”C:\\Temp\\database.bacpac\”

The public documentation introduce SQLPackage utility:

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-export#export-to-a-bacpac-file-using-the-sqlpackage-utility

Export BACPAC using Powershell

You can use New-AzSqlDatabaseExport cmdlet to export database bacpac file.

Example:

$exportRequest = New-AzSqlDatabaseExport -ResourceGroupName \"RG01\" -ServerName \"Server01\" -DatabaseName \"Database01\" -StorageKeyType \"StorageAccessKey\" -StorageKey \"StorageKey01\" -StorageUri \"http://account01.blob.core.contoso.net/bacpacs/database01.bacpac\" -AdministratorLogin \"User\" -AdministratorLoginPassword \"secure password\"

Check the status of Database export using the below command:

$exportStatus = Get-AzSqlDatabaseImportExportStatus -OperationStatusLink $exportRequest.OperationStatusLink [Console]::Write(\"Exporting\") while ($exportStatus.Status -eq \"InProgress\") { Start-Sleep -s 10 $exportStatus = Get-AzSqlDatabaseImportExportStatus -OperationStatusLink $exportRequest.OperationStatusLink [Console]::Write(\".\") } [Console]::WriteLine(\"\") $exportStatus

Reset Sitecore Admin Password

Refer the below steps to update Sitecore admin password from SQL database:

1. Go to Core database.
2. Run the SQL command to update password to default Sitecore credentials (admin/b).

UPDATE 
[aspnet_Membership] 
SET 
[Password]=\'qOvF8m8F2IcWMvfOBjJYHmfLABc=\', 
[PasswordSalt]=\'OM5gu45RQuJ76itRvkSPFw==\', 
[IsApproved] = \'1\', 
[IsLockedOut] = \'0\'
WHERE 
UserId IN (
SELECT UserId FROM dbo.aspnet_Users WHERE UserName = \'sitecore\\Admin\'
)