Archive

Archive for the ‘SQL Server Backup’ Category

Simple Guide to restore SQL Server Backup from Azure Blog Storage using Management Studio

November 6, 2017 Leave a comment

In previous post, we saw

1. How to implement Off-site/Cloud backup in SQL Server 2017 using Azure Blob Storage

2. How to restore SQL Server Backup from Azure Blog Storage using queries

In this post we will see how to restore SQL Server Backup from Azure Blog Storage using SQL Server Management Studio

Step 1: Open SQL Server Management Studio 2017 and connect to SQL Server on which you would like restore the database and right click on “Databases” node and click on “Restore Database…

capture20171030134441904

Step 2: Select Backup media type as “URL” and click on “Add

capture20171030134506048

Step 3: Click “Add” to add the new Azure Storage container

capture20171030134514751

If you are not already signed in, sign in to Azure account and then select the Subscription, Storage account and blob container

Based on you requirement, set up the Expiration date for Shared Access policy, by default, its setup for 1 year from date of setup

Click on “Create Credential” to generate the Shared Access Signature (SAS), once SAS is generated successfully, click “Ok” to continue

capture20171030141847722

Step 4: Please review the selected Azure Storage Container and Shared Access Signature and click “Ok” to continue

capture20171030142950818

Please select the backup file, you wish to restore

capture20171030143011020

Please review the selected backup media and click “Ok” to continue

capture20171030143016568

Step 5: Based on requirement change other options in Files tab or Options tab and click “Ok” to start the restore process

capture20171030143029023

Once restore is completed, we should see this restored successfully message

capture20171030143049123

We should also be able to see the database restored in Object Explorer as well

capture20171031191639786

Hope you find this post helpful !!!

Advertisements

Simple guide to restore SQL Server Backup from Azure Blog Storage

November 1, 2017 1 comment

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:

capture20171031191608492

We can also see the database restored in Object Explorer

capture20171031191639786

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

October 31, 2017 2 comments

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:

capture20171030101644076

If we login to Azure Portal and Navigate to the Storage account and sqlbackups container, we can see the backup file

capture20171030104711661

You can also use SQL Server Management studio 2017 to connect to Azure Storage as well to see the backup file

capture20171030104822250

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 ?

March 12, 2013 Leave a comment

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

image

image

image

image

image

image

image

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

image

Select the databases you want to Backup

image

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

image

For our demo, Let us select Drop box option

image

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

image

Click “Allow” to approve the Drop Box access

image

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

 

image

Specify the Folder to which Backup has to be done

image

Click “Test” to make sure it works

image

Once Test is completed, Click “Ok” to proceed

image

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

image

If you want to enable Email notifications, Please select and specify the email address

image

Click on “Email Settings” icon to Test and configure, You can you use your own SMTP Server, if you prefer

image

Click “Send Test e-mail” to make sure the notifications are working and click “Ok” to proceed

image

Scheduling the Backups

If you want the backup to run automatically at a specific time, Please enable the “Schedule this job” option

image

Please click on “Schedule Settings” to customize the schedule based on your requirement, once finished, Please click on “Save & Close” to proceed

image

Please click “Run Now” to test the job manually

image

image

image

Once test is completed, Now click on “Save” button on the tool bar to save the job.

Hope this helps !!!

%d bloggers like this: