Cannot backup onprem database to Azure Blob container

Question

Cannot backup onprem database to Azure Blob container

Nguyen, Hoa 381

I used the following doc https://learn.microsoft.com/en-us/sql/relational-databases/tutorial-sql-server-backup-and-restore-to-azure-blob-storage-service?view=sql-server-ver16&tabs=SSMS&source=docs to take a backup of the sqltestdb to Azure blob container BUT received an error.

It did take me thru signing in, getting a SAS but could not run the backup.
Can someone please help throw some light on that error?
(a) is it due to the SMO version of my onprem server and database? If yes, what version of SMO must be installed on the onprem server?
(b) or is it caused by the fact that one CANNOT backup an onprem database to Azure Blob Container?

The plan is to keep the backup in Azure Blob Container and restore it from Azure Blob Container to Azure Synapse SQL dedicated pool when data requests are submitted to run queries.

Thank you!

Nguyen, Hoa 381 Reputation points

2022-08-19T22:16:33.64+00:00

==================================

Backup failed for Server '100-nurep-s-200'. (Microsoft.SqlServer.SmoExtended)

------------------------------

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.100.46521.71+(SMO-master-A)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Backup+Server&LinkId=20476

------------------------------

Program Location:

at Microsoft.SqlServer.Management.Smo.Backup.SqlBackup(Server srv)
at Microsoft.SqlServer.Management.SqlManagerUI.BackupPropBackupOptions.OnRunNow(Object sender)

===================================

System.Data.SqlClient.SqlError: Cannot open backup device 'https://absgsandboxblobcontainer.blob.core.windows.net/sampledbtest/sqltestdb_backup_2022_08_19_164626.bak'. Operating system error 50(The request is not supported.). (Microsoft.SqlServer.Smo)

------------------------------

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.100.46521.71+(SMO-master-A)&LinkId=20476

------------------------------

Program Location:

at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQueryWithMessage(StringCollection queries, ServerMessageEventHandler dbccMessageHandler, Boolean errorsAsMessages, Boolean retry)
at Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql(Server server, StringCollection queries)
at Microsoft.SqlServer.Management.Smo.Backup.SqlBackup(Server srv)

Accepted answer

0 additional answers

Your answer

Nguyen, Hoa 381 Reputation points

2022-08-19T22:16:33.64+00:00

==================================

Backup failed for Server '100-nurep-s-200'. (Microsoft.SqlServer.SmoExtended)

------------------------------

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.100.46521.71+(SMO-master-A)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Backup+Server&LinkId=20476

------------------------------

Program Location:

at Microsoft.SqlServer.Management.Smo.Backup.SqlBackup(Server srv)
at Microsoft.SqlServer.Management.SqlManagerUI.BackupPropBackupOptions.OnRunNow(Object sender)

===================================

System.Data.SqlClient.SqlError: Cannot open backup device 'https://absgsandboxblobcontainer.blob.core.windows.net/sampledbtest/sqltestdb_backup_2022_08_19_164626.bak'. Operating system error 50(The request is not supported.). (Microsoft.SqlServer.Smo)

------------------------------

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.100.46521.71+(SMO-master-A)&LinkId=20476

------------------------------

Program Location:

at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQueryWithMessage(StringCollection queries, ServerMessageEventHandler dbccMessageHandler, Boolean errorsAsMessages, Boolean retry)
at Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql(Server server, StringCollection queries)
at Microsoft.SqlServer.Management.Smo.Backup.SqlBackup(Server srv)

Answer 1

Alberto Morillo 34,671 MVP Volunteer Moderator

You cannot restore native SQL Server backups to Azure Synapse by restoring backup files from Azure Storage Accounts. You can export your data to storage account as Text files, make the name of the files meaningful by making date of the export part of the name of the file, and then import them as needed to Azure Synapse. Please read this documentation.

Please follow these steps, you may find them easier to follow. It is better explained.

Nguyen, Hoa 381 Reputation points

2022-08-22T15:03:51.493+00:00

Thanks, Alberto.

My question is about ****BACKUPS**** though. Can I backup an onprem database of an onprem server and store it directly onto blob storage as described STEP by step in https://learn.microsoft.com/en-us/sql/relational-databases/tutorial-sql-server-backup-and-restore-to-azure-blob-storage-service?view=sql-server-ver16&tabs=SSMS&source=docs ?

It specifically says to run the backup and does not say it has to be saved as text files. As a matter of fact, the extension remains .bak as seen in snapshot of the documentation.

Thanks!
Alberto Morillo 34,671 Reputation points MVP Volunteer Moderator

2022-08-22T15:16:23.23+00:00

@Nguyen, Hoa I updated my answer with another tutorial that is better explained about how to create the credentials before creating the backup.
Nguyen, Hoa 381 Reputation points

2022-08-24T23:43:38.417+00:00

Alberto, Success!
Thank you!
Alberto Morillo 34,671 Reputation points MVP Volunteer Moderator

2022-08-25T01:27:41.413+00:00

I am glad it works. Thank you for visiting Microsoft QA forums.

Share via

Cannot backup onprem database to Azure Blob container

0 additional answers

Your answer