Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Blogs / DKatson’s Business Central & NAV blog / How to create SQL backup di...

How to create SQL backup directly to Azure storage

dkatson Profile Picture dkatson 2,263

In old times we created backups on external devices, later as files on external disks. 

Now we can create SQL Backups directly in Azure storage. This is useful and secure.

1. Login in your Azure subscription

2. Create/Use your storage account. Be care! It must have -performance = Standard, -Account Kind = General Purpose.

3. Create/Use container in your storage account. It must have -Access Type = Private

SQL-Backup-2-Azure-Scr2.png

4. Go to your SQL Management Studio, with a database which you want to backup. 

5. Create credentials to connect to your storage. 

Create a new query. Execute it.

create credential MyAzureCredential with identity = 'Storage Account Name' , secret = 'Key 1'
SQL-Backup-2-Azure-Scr1.png

6. Create SQL Backup to URL.
Create a new query. Execute it. 
Backup database [DatabaseName] TO
URL = 'https://storageaccountname.blob.core.windows.net/containername/backupname.bak' 
with compression, credential = 'MyAzureCredential'
7. Done!
Also some useful queries below.
8. Check SQL backup status & percentage 
SET NOCOUNT ON ; 
SELECT @@SERVERNAME Servidor, [SPID] = SESSION_ID , percent_complete [%] , [DATABASE] = DB_NAME(SP.DBID) , [STATUS] = ER.STATUS , [WAIT] = WAIT_TYPE , wait_resource , reads , writes , logical_reads , command , [INDIVIDUAL QUERY] = SUBSTRING(QT.TEXT, ER.STATEMENT_START_OFFSET / 2, ( CASE WHEN ER.STATEMENT_END_OFFSET = -1 THEN LEN(CONVERT(NVARCHAR(MAX), QT.TEXT)) * 2 ELSE ER.STATEMENT_END_OFFSET END - ER.STATEMENT_START_OFFSET ) / 2) , [QUERY] = QT.TEXT , PROGRAM = PROGRAM_NAME , [USER] = NT_USERNAME , HOSTNAME , NT_DOMAIN , START_TIME , QP.query_plan AS xml_batch_query_plan FROM sys.dm_exec_requests ER WITH (NOLOCK) INNER JOIN sys.sysprocesses SP WITH (NOLOCK) ON ER.SESSION_ID = SP.SPID CROSS APPLY sys.dm_exec_sql_text(ER.SQL_HANDLE) AS QT CROSS APPLY sys.dm_exec_query_plan(ER.plan_handle) QP WHERE SESSION_ID > 50 AND SESSION_ID NOT IN ( @@SPID ) and command like 'BACKUP%' ORDER BY 1 , 2

9. Check remaining time of backup

select percent_complete as pctcomplete, start_time as starttime, command as command, b.name as databasename, dateadd(ms,estimated_completion_time,getdate()) as estimatedendtime, (estimated_completion_time/1000/60) as estimatedminutestoend from sys.dm_exec_requests a inner join sys.databases b on a.database_id = b.database_id where session_id = 'SessionID' and estimated_completion_time > 0

10. Restore your database
To restore your database you should input next query

Restore database [Database name] From URL = 'https://storageaccountname.blob.core.windows.net/containername/backupname.bak' with credential = 'MyAzureCredential'

You can use MOVE command to restore files in another from original place folder.











                

                 
                    
                  
                

                 
                

                

                    
Comments

                          
                          
                    

                        

                            
                                
*This post is locked for comments