SQL Server Cloud Backup

Simple guide to restore SQL Server Backup from Azure Blog Storage

In previous post, we saw How to implement Off-site/Cloud backup in SQL Server 2017 using Azure Blob Storage, in this post we will see how to restore SQL Server Backup from Azure Blog Storage

Step 1: Create Credential – Please use below query to create Credential to access Azure Blog Storage using Shared Access Signature, This step is optional if you already have created Credential to create backup

CREATE CREDENTIAL [https://XYZ.blob.core.windows.net/YourContainerName]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET =
 'sv=Replace your SAS Key Here';
GO

Step 2: Restore Database from Cloud (Azure Blog Storage) – Please run below query to restore the database using SQL Server Backup stored in Azure Blog Storage, Please change the query to meet your needs

USE [master]

RESTORE DATABASE [SQL2017_DEMO]
FROM
     URL = N'https://XYZ.blob.core.windows.net/YourContainerName/BackupFile.bak'
WITH  FILE = 1,
MOVE N'SQL2017_DEMO' TO N'E:\SQLDATA\SQL2017_DEMO.mdf',
MOVE N'SQL2017_DEMO_log' TO N'E:\SQLDATA\SQL2017_DEMO_log.ldf',
NOUNLOAD,
STATS = 5

GO

Output:

We can also see the database restored in Object Explorer

Hope you find this post helpful !!!

Simple Guide to implement Off-Site/Cloud backup in SQL Server 2017 using Azure Blob Storage in 2 Steps

In this post we will see how to implement Off-Site/Cloud backup in SQL Server 2017 using Azure Blog Storage

Requirements

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

Output:

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 !!!

How to backup your SQL Database and Upload to FTP or Cloud Automatically ?

Its always a better idea to Keep Offsite backup of databases, “SQL Backup and FTP” allows you to do that freely

SQL Backup and FTP is available for download for free at http://sqlbackupandftp.com

It supports direct backup to

1. FTP

2. Local or Network Folder

3. Drop Box

4. Box

5. Amazon S3

6. SkyDrive

7. Google Drive

Professional Version also supports SFTP and FTPS and AES Encryption of Backup files

Please follow the below steps for installation

The installation has completed successfully, Let us launch SQL Backup and FTP and create a Backup

Select the databases you want to Backup

Click on “Add backup destination” to specify the backup destination

For our demo, Let us select Drop box option

Click on “Authorize…” and Login to your Drop Box account

Click “Allow” to approve the Drop Box access

Once Drop Box access is authorized, Please click on “Finish” to proceed. You can configure automatic cleanup of backups as well.

Specify the Folder to which Backup has to be done

Click “Test” to make sure it works

Once Test is completed, Click “Ok” to proceed

Let us now connect to SQL Server and do a backup, By default, if you have a SQL Server default instance running, it will automatically connect and display the databases, Please select the database which you want to backup