How to take Managed Backup in SQL Server Azure

Managed Backup in SQL Server Azure

Credential Creation in Azure portal For SQL URL backup

Link for reference:- https://msdn.microsoft.com/en-IN/library/ms190703.aspx

Using SQL Server Management Studio

1.     In Object Explorer, expand the Security folder.

2.     Right-click the Credentials folder and select New Credential….

3.     In the New Credential dialog box, in the Credential Name box, type a name for the credential.

4.     In the Identity box, type the name of the account used for outgoing connections (when leaving the context of SQL Server). Typically, this will be a Windows user account, but the identity can be an account of another type.

Alternately, click the ellipsis (…) to open the Select User or Group dialog box.

5.     In the Password and Confirm password boxes, type the password of the account specified in the Identity box. If Identity is a Windows user account, this is the Windows password. The Password can be blank, if no password is required.

6.     Click OK.

1. Credential creation Using GUI.

1. Using Transact-SQL

To create a credential

1. In Object Explorer, connect to an instance of Database Engine.
2. On the Standard bar, click New Query.
3. Copy and paste the following example into the query window and click Execute.

 -- Creates the credential called "AlterEgo.".  

 -- The credential contains the Windows user "Mary5" and a password.   CREATE CREDENTIAL AlterEgo WITH IDENTITY = 'Mary5',  

SECRET = '<EnterStrongPasswordHere>';

GO

1.       Script –

CREATE CREDENTIAL rahul  WITH IDENTITY = 'nresourcegroupdiag929',  

   SECRET = 'YTweRUSFwydfopwd6M6t707g/D3WLFopOULICiNRz7PEnITSSgwkoRBzlsArvR+y+8tv++8xABGpA6Oxz2CpWQ==';

GO

2.       Insert Storage Name

Nresoursegroup929

3.       Storage Key

'YTweRUSFwydfopwd6M6t707g/D3WLFopOULICiNRz7PEnITSSgwkoRBzlsArvR+y+8tv++8xABGpA6Oxz2CpWQ==';

Below is the snap for Storage name and Storage Key

How to add container in Sql server management studio

1. Click on Connect tab
2. Click on Azure Storage tab

Backup Query

Go to through below process.

Take Full Backup of Required DB using below Query.

BACKUP DATABASE [HR] TO  URL = N'https://nresourcegroupdiag929.blob.core.windows.net/Test/HR1_backup_2016_12_14_190723.bak'

WITH  CREDENTIAL = N'Rahul'

, NOFORMAT, NOINIT,  NAME = N'HR-Full Database Backup', NOSKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

Test container name

HR-Full Database Backup your Backup name

Note:- Change Highlited Information.

Backup Restoration Query

In below query With Move Steps are needed which we are performing in Point in Time while restoring backup.

--Restore Database

USE [master]

RESTORE DATABASE [Test1] FROM  URL = N'https://nresourcegroupdiag929.blob.core.windows.net/sqlbackup/Test_backup_2016_12_13_184333.bak'

WITH

move 'Test' to     'F:\Test1\Test.mdf',

move 'Test_log' to       'F:\Test1\Test_log.ldf' ,CREDENTIAL = N'rahul' ,  FILE = 1,  NOUNLOAD,  STATS = 5

GO

Note:- Change Highlited Information.

Note:- If in case while backing up database if it goes in Single User mode then use below quey to get database in Multi user mode

--Get database SINGAL USER mode to MULTI USER mode

use master

go

alter database Test set multi_user with rollback immediate

--To check Physical and Logical Files

restore filelistonly from url='https://nresourcegroupdiag929.blob.core.windows.net/sqlbackup/Test_backup_2016_12_13_184333.bak'

WITH

CREDENTIAL = N'rahul'

Hence We have seen that Managed Backup is much easier if done step by step.

Categories: Backup to URL, Managed Backup SQL 2014, SQL AZURE Backup