In this post we will see how to implement Off-Site/Cloud backup in SQL Server 2017 using Azure Blog Storage
1. SQL Server 2017
2. Azure Account
3. Azure Blog Storage with Container provisioned and Shared Access Signature generated
4. SQL Server Management Studio (Optional, if you prefer to run using SQLCMD)
Step 1: Create Credential – Please use below query to create Credential to access Azure Blob Storage using Shared Access Signature
CREATE CREDENTIAL [https://sqlxpertise.blob.core.windows.net/sqlbackups] WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 'sv=INSERT YOUR SHARED ACCESS SIGNATURE'; GO
Step 2: Backup Database to Cloud – Run below query to backup the database to Azure Blob Storage, Please change the query to meet your needs
BACKUP DATABASE SQL2017_DEMO TO URL = 'https://XYZ.blob.core.windows.net/sqlbackups/BackupFileName.bak' WITH STATS = 10, SKIP, NOREWIND, NOUNLOAD
If we login to Azure Portal and Navigate to the Storage account and sqlbackups container, we can see the backup file
You can also use SQL Server Management studio 2017 to connect to Azure Storage as well to see the backup file
Having an offsite backup is always important and helpful.
Hope you find this post helpful !!!