Issue:-
Msg 45207, Level 17, State 6, Procedure managed_backup.sp_add_task_command, Line 102 [Batch Start Line 23]
SQL Server Managed Backup to Microsoft Azure cannot configure the default backup settings for the SQLServer instance because the container URL was invalid. It is also possible that your SAS credential is invalid.
while executing below command sometime you get below error, actually this command disable managed backup.
EXEC msdb.managed_backup.sp_backup_config_basic
@database_name = NULL
,@enable_backup = 0;
Msg 45207, Level 17, State 6, Procedure managed_backup.sp_add_task_command, Line 102 [Batch Start Line 23]
SQL Server Managed Backup to Microsoft Azure cannot configure the default backup settings for the SQLServer instance because the container URL was invalid. It is also possible that your SAS credential is invalid.
execute this command
you will get to know your Endpoint URL for the container
Use msdb;
GO
SELECT * FROM managed_backup.fn_backup_instance_config ();
https://rakctlstore.blob.core.windows.net/homepccontainer
now check your credentials are there in the server or not..
open SSMS --> security --> Credentials -->
you will find there is no credential or you have invalid credential
create a credential for that homepccontainer storage container
execute the script
now execute the command
EXEC msdb.managed_backup.sp_backup_config_basic
@database_name = NULL
,@enable_backup = 0;
and see it execute successfully.
Lets start to create a credential in SQL Server..
RGName=rakctlrg
storageAccountName=rakctlstore
containerName=homepccontainer
$context = New-AzureStorageContext -StorageAccountName rakctlstore -StorageAccountKey (Get-AzureRMStorageAccountKey -StorageAccountName rakctlstore -ResourceGroupName rakctlrg).Value[0]
New-AzureStorageContainerSASToken -Name homepccontainer -Permission rwdl -ExpiryTime (Get-Date).AddYears(1) -FullUri -Context $context
https://rakctlstore.blob.core.windows.net/homepccontainer
sv=2017-07-29&sr=c&sig=r02vk7iGjSW4rDh6WRXXBnAoHiEAm3k%2FJ8kxeOoIGhw%3D&se=2019-06-25T04%3A21%3A34Z&sp=rwdl
execute this command in the SSMS
CREATE CREDENTIAL [https://rakctlstore.blob.core.windows.net/homepccontainer]
WITH IDENTITY = 'Shared Access Signature',
SECRET = 'sv=2017-07-29&sr=c&sig=r02vk7iGjSW4rDh6WRXXBnAoHiEAm3k%2FJ8kxeOoIGhw%3D&se=2019-06-25T04%3A21%3A34Z&sp=rwdl'
Your credential will get created..
check again..
Now execute the command to see the status of managed backup configuration at instance level
Use msdb;
GO
SELECT * FROM managed_backup.fn_backup_instance_config ();
go
you will find --
is_managed_backup_enabled is 1
Now execute the command
EXEC msdb.managed_backup.sp_backup_config_basic
@database_name = NULL
,@enable_backup = 0;
above command will execute successfully and then you execute the below command to see the status of is_managed_backup_enabled, it should show 0
EXEC msdb.managed_backup.sp_backup_config_basic
@database_name = NULL
,@enable_backup = 0;
Now you will find that your is_managed_backup_enabled is 0.
Disable Default SQL Server Managed Backup to Microsoft Azure settings for the Instance
Default settings at the instance level apply to all new databases created on that instance. If you no longer need or require default settings, you can disable this configuration by using the managed_backup.sp_backup_config_basic system stored procedure with the @database_name parameter set to NULL.
Disabling does not remove the other configuration settings like the storage URL, retention setting, or the SQL Credential name. These settings will be used if SQL Server Managed Backup to Microsoft Azure is enabled for the instance at a later time.
Also check status at database level..
Use msdb;
GO
SELECT * FROM managed_backup.fn_backup_db_config (NULL);
GO
you need to ensure that is_managed_backup_enabled = 0
Thanks for Reading..