Create database on Azure SQL Managed Instance using Azure.Rm PowerShell library

First published on MSDN on Jun 04, 2018

Managed Instance is a fully managed SQL Server instance hosted in Microsoft Azure cloud that enables you to work with your databases using standard TSQL or PowerShell. In this post you will see create your database using PowerShell.

Prerequisites

In Managed Instance, you can use PowerShell to create a new database without connecting directly to your instance and executing

CREATE DATABASE

T-Sql statement. This is useful if you need to create some script that will create databases when they are needed.

You just need to

install Azure RM PowerShell

to manage your databases. In most of the cases the following  commands might install everything that you need:

Install-Module PowerShellGet -Force

Install-Module -Name AzureRM -AllowClobber

Then, you just need an access to the subscription where you have some Managed Instances and you are ready to create databases.

Create new database

Once you install PowerShell libraries, you can create a new database using

New-AzureRmResurce

command, as shown in the following example:

$subscriptionId = “a8cm4923-06c1-6bde-8758-e7c13a56e9m1”

$location = “West Central US”

$resourceGroup = “my-resource-group”

$managedInstance = “my-managed-instance”

$database = “my-db”

Select-AzureRmSubscription -SubscriptionId $subscriptionId

New-AzureRmResource -Location $location `

-ResourceId “/subscriptions/$subscriptionId/resourceGroups/$resourceGroup/providers/Microsoft.Sql/managedInstances/$managedInstance/databases/$database” `

-ApiVersion “2017-03-01-preview” `

-AsJob -Force

In the first section you need to set the parameters such as your subscription id, data center location, and the resource group where your instance is placed. Then you need set the name of the managed instance (without domain suffix) where you want to create a new database, and a database name.


New-AzureRmResurce

command will create a new resource on the specified location using the formatted resource id. Optional parameter

-AsJob

specifies that the command should run asynchronously so it will complete immediately even if database creation time might be longer.

As a result, you will see a new empty database in your managed instance.

Create database as copy

You can also create a database as a copy of the existing database on managed instance. In addition to copy, you can specify a point in time in the past of the original database if you don't want to create a copy of the latest state.

The following script will create a copy of existing database:

$subscriptionId = “a8cm4923-06c1-6bde-8758-e7c13a56e9m1”

$location = “West Central US”

$resourceGroup = “my-resource-group”

$managedInstance = “my-managed-instance”

$database = “my-db”

$pointInTime = Get-Date # or “2018-06-01T08:51:39.3882806Z”

$targetDatabase = “my-db-copy”

$properties = New-Object System.Object

$properties | Add-Member -type NoteProperty -name CreateMode -Value “PointInTimeRestore”

$properties | Add-Member -type NoteProperty -name SourceDatabaseId -Value “/subscriptions/$subscriptionId/resourceGroups/$resourceGroup/providers/Microsoft.Sql/managedInstances/$managedInstance/databases/$database”

$properties | Add-Member -type NoteProperty -name RestorePointInTime -Value $pointInTime

Select-AzureRmSubscription -SubscriptionId $subscriptionId

New-AzureRmResource -Location $location `

-Properties $properties `

-ResourceId “/subscriptions/$subscriptionId/resourceGroups/$resourceGroup/providers/Microsoft.Sql/managedInstances/$managedInstance/databases/$targetDatabase” `

-ApiVersion “2017-03-01-preview” `

-AsJob -Force

The script is similar to the previous one with a difference in

$properties

object where you can specify that you want to create a database using

PointInTimeRestore

method from the source database.

When this job finishes, you will get a new database as a copy of source database at some point in time in the past.

 

This article was originally published by Microsoft's Azure SQL Database Blog. You can find the original article here.