0

I'm the DBA of one Azure SQL Managed Instance and one of our clients asked us if is possible automate a backup and restore in order to have a copy of one of the database before one of our ETL procces start. That's because they want to do all insertions and work on the copy database instead of the main production database and after finish everything and if data validations are OK, change database name.

So I thought use a SQL Job just for backup a database to one blob storage and then restore using a backup file from a blob storage, the problem start when I realized that could not use a proxy account (related to the credential with access to blob storage)in the SQL Job and also there are some limitations in TSQL (WITH CREDENTIAL for example)

Now with those restrictions I don't know how can automate this task. If I am not wrong, ELASTIC JOBS are only for Azure SQLDatabase

Thanks in advance

1 Answer 1

0

You can use an SQL Agent Job to backup and restore a database from a storage account, you just need to create a credential in advance:

SELECT * FROM sys.credentials;

CREATE CREDENTIAL [https://StorageAccountName/ContainerName] 
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '/*SAS token*/';  
GO

SELECT * FROM sys.credentials;

With the credential created you can then create a SQL job with two steps, one for backing up and another for restoring the backup:

/* Step 1 */
BACKUP DATABASE [DatabaseName] 
TO  URL = N'https://StorageAccountName/ContainerName/BackupName.BAK' 
WITH  COPY_ONLY;

/* Step 2 */
RESTORE DATABASE [CopyDatabaseName] 
FROM  URL = N'https://StorageAccountName/ContainerName/BackupName.BAK';

On the network side, make sure the virtual network hosting the managed instance allows access to the storage account either trough a private or a service endpoint.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.