Showing posts with label Backup to URL. Show all posts
Showing posts with label Backup to URL. Show all posts

Monday, 14 August 2017

Migrating on-premise SQL Database to Windows Azure SQL Database

Migrating on-premise SQL Database to Windows Azure SQL Database


Migrating SQL database(s) from one server to another is one of the common tasks for SQL DBAs. There are several ways to migrate a SQL database(s). I have already covered how to migrate SQL DB on premise here. Today we will see how do we migrate an on-premise SQL Database to Azure SQL Database. Please follow the below step by step procedure to migrate your on-premise SQL Database to Azure-

1.       You need to login to the Source Server first. This is the Server where your Database, which needs to be migrated to Azure, resides. Login with your account and go to the Source database-> Click on Tasks-> Deploy Database to Windows Azure SQL Database.
Migrate your database to Windows azure SQL Database


2.       Upon clicking on Deploy Database to Windows Azure SQL Database you will be presented with the Deploy Database screen which will be an Introductory screen to provide you some information regarding the Wizard. Click Next

Migrating on-premise SQL Database to Windows Azure SQL Database


3.       Next screen will be Deployment Setting Screen where you need to provide the details for the target Server. Under the Specify Target Connection pane you will see a tab to enter Server connection. Click on the connect button which is situated at the right side of the server connection box. A new Connect to Server window will be popped up and there you should furnish the details of the target Server name and Authentication details. After entering the details click on connect.
Migrate your SQL Database to Windows Azure SQL Database, specify target server name
4.       Upon Clicking next, you will be presented with the below window where you can see the connected server name. you need to furnish some details like the new database name and other seeing details like Edition of windows Azure SQL Database, Maximum database size in GB and Service Objective. Here in our example I have selected “Standard” as the edition of Windows Azure SQL database and 250 GB is what I have mentioned as the Max size of the database and S2 as the Service Objective.

Migrate your SQL Database on Azure

Also, you need to specify a backpac file path in the Deployment Settings window. Once you are done with all these things, click next.

5.       Upon clicking next, a summary page will be shown which will have all the details about Source Server and database, target server and database and environment details.
Step by Step document for Migrating SQL Database to Azure


6.       Validate if all the above details are correct and proceed to click finish. Upon finishing the wizard you will see Operation complete message on the screen once all the operations such as Exporting database, Validating database, processing objects, importing database are complete.

Migrate SQL databases to Windows Azure


7.       To verify whether your newly migrated database has been migrated successfully or not you can go to Portal and check it. Login to portal.azure.com with your credentials and see if the newly created database has been created under SQL Databases Pane. In our example, I logged into Azure portal and verified that the database with the name “test” has been created successfully under the SQL Databases pane.
Migrating on-premise SQL Database to Windows Azure SQL Database

Hence We have seen that Migrating an on-premise SQL server database to Windows Azure SQL Database is very easy and straight forward.

Tuesday, 23 May 2017

How to take Managed Backup in SQL Server Azure

Managed Backup in SQL Server Azure


Credential Creation in Azure portal For SQL URL backup


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.


Managed Backup in SQL Server Azure




  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

Managed Backup in SQL Server Azure







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

Managed Backup in SQL Server Azure


How to add container in Sql server management studio

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


Managed Backup in SQL Server Azure

Managed Backup in SQL Server Azure



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.


Managed Backup in SQL Server Azure

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