Setting Up and Using Azure VM SQL Automated Backup (and Restore)

This weekend I was creating some Azure VMs to test and was required to use the GUI for some screenshots. I have always used my Powershell scripts described here to create my test systems and with a new job taking up a lot of my time had missed the announcement about Azure SQL Automated Backup and Azure SQL Automated Patching so was surprised to see this screen


I read the announcement and also the details on MSDN which show that this requires the SQL Server IaaS Agent. This is a default option on new virtual machines.

There are some other considerations too. It is only supported for SQL Server 2014 and Windows Server 2012 and 2012R2 at present and you can set a retention period to a maximum of 30 days but it is automated. You do not have to decide upon the backup strategy Azure will decide the frequency and type of backups dependent upon the workload of the database and some other factors such as

A full backup is taken
○ when an instance is added to use Managed backup
○ When transaction log growth is 1Gb or more
○ At least once a week
○ If the log chain is broken
○ When a database is created

A transaction log backup is taken
– If no log backup is found
– Transaction log space used is 5Mb or larger
– At least once every two hours
– Any time the transaction log backup is lagging behind a full database backup. The goal is to keep the log chain ahead of full backup.

From <>

There are some restrictions
– Only database backups are supported
– System databases are not supported so you need to back those up yourself
– You can only back up to Azure storage
– Maximum backup size is 1Tb as this is the maximum size for a blob in Azure storage
– Simple recovery is not supported
– Maximum retention is 30 days – if you are required to keep your backups for longer than 30 days for regulatory or other reasons you could simply use Azure Automation to copy the files to another storage account in Azure)

How to set it up.

If you are using the GUI then you will find SQL Automated Backup in the optional config blade of the set up. You can follow the steps here to set it up. If (like me) you want to use Powershell then use the following code after you have created your Virtual Machine

$storageaccount = "<storageaccountname>"
$storageaccountkey = (Get-AzureStorageKey -StorageAccountName $storageaccount).Primary
$storagecontext = New-AzureStorageContext -StorageAccountName $storageaccount -StorageAccountKey $storageaccountkey

$encryptionpassword = (Get-Credential -message 'Backup Encryption Password' -User 'IGNOREUSER').password
$autobackupconfig = New-AzureVMSqlServerAutoBackupConfig -StorageContext $storagecontext -Enable -RetentionPeriod 10 -EnableEncryption -CertificatePassword $encryptionpassword
Get-AzureVM -ServiceName <vmservicename> -Name <vmname> | Set-AzureVMSqlServerExtension -AutoBackupSettings $autobackupconfig | Update-AzureVM

Once you have run the code, Azure will take care of the rest. Add a couple of databases to your instance and look in the storage account and you will see this



And in the automaticbackup container you will find the Certificates and master key backups


It will also create a credential


You can use the same credential to back up your system databases. If like me you use Ola Hallengrens excellent Maintenance Solution then simply change your systems backup job as follows

USE [msdb]
EXEC msdb.dbo.sp_update_jobstep @job_name = 'DatabaseBackup - SYSTEM_DATABASES - FULL', @step_id=1 ,
		@command=N'sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = ''SYSTEM_DATABASES'', ""
		,  @Credential = ''AutoBackup_Credential'', @BackupType = ''FULL'', @Verify = ''Y'', @CleanupTime = NULL, @CheckSum = ''Y'', @LogToTable = ''Y''" -b'

If you need to restore your database then you can use the GUI and when you choose restore you will see this screen


Enter your storage account and the key which you can get from the Azure portal. You will notice that the credential has already been selected, click connect and


There are all of your backups ready to restore to any point in time that you choose. By clicking script the T-SQL is generated which looks like this

USE [master]
WITH  CREDENTIAL = N'AutoBackup_Credential' ,
NOFORMAT, NOINIT,  NAME = N'Test_LogBackup_2015-07-16_06-21-26',
WITH  CREDENTIAL = N'AutoBackup_Credential' ,  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
WITH  CREDENTIAL = N'AutoBackup_Credential' ,  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
WITH  CREDENTIAL = N'AutoBackup_Credential' ,  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
WITH  CREDENTIAL = N'AutoBackup_Credential' ,  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
WITH  CREDENTIAL = N'AutoBackup_Credential' ,  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
WITH  CREDENTIAL = N'AutoBackup_Credential' ,  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
WITH  CREDENTIAL = N'AutoBackup_Credential' ,  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
WITH  CREDENTIAL = N'AutoBackup_Credential' ,  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
WITH  CREDENTIAL = N'AutoBackup_Credential' ,  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
WITH  CREDENTIAL = N'AutoBackup_Credential' ,  FILE = 1,  NOUNLOAD,  STATS = 5

There is an important note. Remember this when you have just set it up so that you don’t think that you have done it wrong (which is what I did!)

When you enable Automated Patching for the first time, Azure configures the SQL Server IaaS Agent in the background. During this time, the portal will not show that Automated Patching is configured. Wait several minutes for the agent to be installed, configured. After that the portal will reflect the new settings.

From <>

And also look out for this


The password I had chosen was not complex enough but the Powershell script had succeeded and not given me the warning

To set up SQL Automated Patching you follow a similar steps. The setting is again on the OS Config blade and click enable and then you can choose the frequency and duration of the patching.

It is important to remember to choose your maintenance window correctly. If you have set up your SQL VMs correctly you will have them in an availability set and be using either mirroring or Availability Groups and have the VMs set up in the same availability set to ensure availability during the underlying host patching but I had it confirmed by Principal Software Engineering Manager Sethu Srinivasan t via Microsoft PFE Arvind Shyamsundar b | t that the SQL Automated Patching is not HA aware so you will need to ensure that you set the maintenance windows on each VM to ensure that they do not overlap

Find Out Which Indexes are on which Filegroups using PowerShell And How To Find Other Information


A short post today to pass on a script I wrote to fulfil a requirement I had.

Which indexes are on which filegroups. I found a blog post showing how to do it with T-SQL but as is my wont I decided to see how easy it would be with PowerShell. I also thought that it would make a good post to show how I approach this sort of challenge.

I generally start by creating a SQL Server SMO Object You can use the SMO Object Model Diagram or Get-Member to work out what you need. As we are talking indexes and filegroups I will also create a Database object

Then by piping the database object to Get-Member I can see the properties


Lets take a look at the table object in the same way



I can see the indexes object so I pipe that to Get-Member as well


Now I have enough to information to create the report. I will select the Name, Table, Type and Space Used of the Indexes and format them nicely

and here are the results


However, you may want the results to be displayed in a different manner, maybe CSV,HTML or text file and you can do this as follows





Hopefully this has shown you how easy it can be to use PowerShell to get all of the information that you need from your SQL Server and how to approach getting that information as well as several ways to display it

List Databases (and Properties) on SQL Server with PowerShell

Another post in the PowerShell Box of Tricks series. Here is another script which I use to save me time and effort during my daily workload enabling me to spend more time on more important (to me) things!

Todays question which I often get asked is What databases are on that server?

This is often a follow up to a question that requires the Find-Database script. It is often asked by support teams investigating issues. It can also be asked by developers checking the impact of other services on their DEV/UAT environments, by change managers investigating impact of changes, by service managers investigating the impact of downtime, when capacity planning for a new service and numerous other situations.

A simple quick and easy question made simpler with this function which can also be called when creating documentation


Simply call it with Show-DatabasesOnServer SERVERNAME and use the results as you need


This only shows you the name but if you need more information about your databases then have a look and see what you require.

Use Get-Member to see what is there. I ran the following code to count the number of Properties available for Databases (Using PowerShell V3 on SQL Server 2012 SP1 11.0.3350.0 )


154 Properties that you can examine and that is just for databases:-)

Picking out a few properties you could do something like this


If you want aliases for your column headings you will need to add a bit of code to the select.

For Example, maybe you want to Database Name as a heading and the Size in Gb (Its in Mb in the example above) You would need to create a hash table with a Label element and an Expression element. The Label is the column heading and the Expression can just be the data or a calculation on data.

So select Name becomes

The Column Heading is Database Name and the data is the Name property

and select Size becomes

The Column Heading is Size GB and the data is the Size property divided by 1024 to 3 decimal places

then your code would look like this

and the results


and here is the full code


Creating SQL Server Database with PowerShell


This morning I have been setting up my Azure Servers in preparation for my presentation to the Cardiff SQL User Group this month.

I used my scripts from My Post on Spinning Up Azure SQL Boxes to create two servers and then I wanted to create some databases

I decided it was time to write a Create-Database function using a number of scripts that I have used to create individual databases.


Whilst finalising the function I didn’t quite get it right sometimes and was faced with an error.


Not the most useful of errors to troubleshoot. The issue could be anywhere in the script

You can view the last errors PowerShell has shown using $Errors. This gives you the last 500 errors but you can see the last error by using $Error[0] if you pipe it to Format-List you can get a more detailed error message so I added a try catch to the function which gave me an error message I could resolve.


Much better. The problem was

Cannot create file ‘C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\.LDF’ because it already exists.

Mistyping a variable has caused this. Creating an empty file name variable which then threw the error the second(and third,fourth fifth) times I ran the script but this error pointed me to it.

Creating Database

There are a vast number of variables you can set when creating a database. I decided to set File Sizes, File Growth Sizes, Max File Sizes and Recovery Model. I only set Server and Database Name as mandatory parameters and gave the other parameters default values


We take the parameters for file sizes in MB and set them to KB


Then set the default file locations. Create a database object, a Primary file group object and add the file group object to the database object


Add a User File Group for User objects


Create a database file on the primary file group using the variables set earlier


Do the same for the user file and then create a Log File


Set the Recovery Model and create the database and then set the user file group as the default


Finally catch the errors


It can then be called as follows Create-Database SERVERNAME DATABASENAME


or by setting all the parameters Create-Database -Server Fade2black -DBName DatabaseTest -SysFileSize 10 -UserFileSize 15 -LogFileSize 20 -UserFileGrowth 7 -UserFileMaxSize 150 -LogFileGrowth 8 -LogFileMaxSize 250 -DBRecModel FULL


This means that I can easily and quickly set up several databases of different types and sizes

The script can be found here


Using PowerShell to find a database amongst hundreds

As you know, I love PowerShell!

I have developed a series of functions over time which save me time and effort whilst still enabling me to provide a good service to my customers. I also have a very simple GUI which I have set up for my colleagues to enable them to easily answer simple questions quickly and easily which I will blog about later. I call it my PowerShell Box of Tricks

I am going to write a short post about each one over the next few weeks as I write my presentation on the same subject which I will be presenting to SQL User Groups.

Todays question which I often get asked is Which server is that database on?

It isn’t always asked like that. Sometimes it is a developer asking where the database for their UAT/SAT/FAT/Dev environment is. Sometimes it is a manager who requires the information for some documentation or report. I wrote it because whilst I am good, I am not good enough to remember the server location for many hundreds of databases and also because I wanted to enable a new starter to be as self sufficient as possible

When I first wrote this I thought it would be a case of simply using $databaseName.Contains($DatabaseNameSearch) but this did not work for MiXed case searches or DatAbaSe names so I had to convert both the search and the database name to lower case first


I create an empty hash table and then populate it with the results


Set a results variable to the names from the hash table and count the number of records


and call it like this


Note that the search uses the contains method so no need for wildcards

Results come out like this


You can find the code here


SQL Server Operators and Notifications with Powershell – Strange Enumerate issue fixed by @napalmgram

Alerting of issues across the SQL Server estate is important and recently I needed to audit the operators and the notifications that they were receiving.

I created a SQL Server Object


One of the important things to remember when investigating SMO is the Get-Member cmdlet. This will show all methods and properties of the object

gave me the JobServer Property

includes the Operator Property


has the EnumJobNotifications and EnumNotifications methods

So it was easy to loop through each server in the servers.txt file and enumerate the notifications for each Operator


and create a simple report

However this does not work as it does not perform the second enumerate. Try it yourself, switch round the EnumJobNotifications and EnumNotifications methods in that script and see what happens.

So I ended up with two functions



and I thought I could do this


But that doesnt work

So I tried this


and that doesnt work either

Now the reports are coming out showing the correct number of lines but not displaying them. I spent a period of time on my Azure boxes trying to work a way around this. I set the outputs to both enums to a variable and noted that they are different type of objects.


Job Notifications are System.Object and Alert Notifications are System.Array

I tried to enumerate through each member of the array and display them but got too tired to finish but I had contacted my friend Stuart Moore Twitter | Blog who had a look and resolved it by simply piping the Enumerates to Format-Table. Thank you Stuart.

So the final script is as follows


and the script is


Those Pesky ‘s

Changing Domain Names in a Column

A quick little post for today. Not particularly SQL related but the points at the end are relevant.

I had a task when moving a service to a new development area to change the domain name within columns in several tables from “DOMAIN1\USER” to “DOMAIN2\USER”

In SQL I was able to do this quite easily as follows


I also had to do the same for some Oracle databases too and this is where the fun started!

I needed to create the update scripts for documentation for the Oracle databases.

I wanted to create


for each userid in the table.I had trouble with the script I found in our DBA area as it kept failing with

ORA-00911: invalid character

at the \

as it wouldn’t add the ‘ ‘ around DOMAIN1\USER

Not being an Oracle DBA but wanting to solve the issue once and for all I tried a whole host of solutions trying to find the escape character. i asked the Oracle DBAs but they were unable to help Checking the IT Pros handbook (also known as Google!) made me more confused but in the end I solved it.

A whole host of ‘s in there!!

I put this in my blog as it is relevant to my situation and an experience I have had that I couldn’t easily solve. Maybe it will help another person searching for the same thing.

It raises some interesting points

The script provided ( I use that term loosely, it had the right name and was in the right place to use for this process) had obviously not been run as it didn’t work or someone had manually added the ‘s. I wasn’t go to do that for the number of users required.

If it no good, if it doesn’t do what i expected or is still in development then mark it as so, so that everyone knows. In the name of the script, in the comments in the script or by keeping live tested scripts in one place. Which ever method you choose is fine as long as it is appropriate to your environment and everyone knows about it

I probably say a dozen times a day to my new colleague

“In case you/I get run over by a bus”

It is all very well being the one who knows everything but it is pointless if you aren’t there SPOF’s (Single Points of Failure) apply to people as well as hardware.

Enable your service to be supported by preparing proper documentation.

This doesn’t have to be reams of paperwork. It can sometimes be as simple as placing things in a recognised place or a single comment in the script.

I hold my hands up. I am guilty of this too. I have been so busy I haven’t done this as much as I should have over the last few months of last year. I have tried but not done as well as I should have. In my defence, I have spent plenty of time recently rectifying this, which is why this situation was so memorable.

Some links I have read in the past related to this by  people who know more than me.

Documentation It Doesn’t Suck – Brent Ozar

Your Lack Of Documentation is Costing you More than you Think – John Samson

Do You Document Your SQL Server Instances? – Brad McGhee