For a SQL server DBA – How to start Azure Learning – Part 2 (SQL Azure DB Backups)

In reference to my previous post , I am going to talk about how backups will be managed in Azure. This is one of the activities which every DBA does almost every day or week. Just to let you know , there is a change in the backups of azure databases. You can no longer take DB/log backups for SQL Azure database(PaaS).

Before you read forward, just be sure of what’s SQL on Azure VMs (IaaS) and SQL azure databases(PaaS). Please check this link : –

Generally, if you want to move the SQL database from test/development to production environment – you take SQL database full backup and restore it. But, this will change with SQL Azure database. In SQL Azure DBs , we have options

1. Bacpac – It consists of schema and data. If you want to move your database from on-premise SQL server to SQL Azure database, then you will be create a Bacpac file of your database. Move this Bacpac file to Azure storage and restore it as a Azure DB. Currently, there is no option of traditional backups for Azure DB.

2. Dacpac -> it consists of schema only. if you want to create database on Azure without data then this option is used.

For information on Bacpac and Dacpac , please check: –

Sometime, DB professionals use DB migration wizard to move the databases from on-premise to SQL Azure.

3. Copy database -> This option is used , if you want to move your existing azure database into some other location in Azure itself. It’s an asynchronous operation and connection to azure is not required after it started. For more information on Copy database , please check –

As above three topics are discussed in detail on the web already therefore, refered the links for deep understanding.

Q:Now, you got how backups in Azure work but at the same time, how will you manage if there is any accidental deletion of the data. In other words, how will you do point in time recovery.

A: You will be glad to know that, SQL Azure automatically takes full, differential and t-log backups in the background. By default one full backup every week , differential backup everyday and t-log backup every 5 minutes is taken. Depending on the tier of your database , the retention time will be set e.g. if you have database deployed in Premium tier then 35 days is the retention time. Even you can restore the deleted database within 35 days/retention time.

Moreover, you will get backup storage by default equivalent to the double of max database size of you performance tier e.g. for premium it’s 500 GB which means you will get 1000 GB of backup storage. if you want increase the size of backup storage, you will need to pay extra money for that (Contact Azure support for that).

For more information , please check –
Backup and Restore for Azure database –

Please note – For Web and Business edition you still need to manage backups (Bacpac and database copy) yourself for point in time recovery.

let’s see how point in time recovery works in Azure database:

Point in time recovery using Azure dashboard
Point in time recovery using Azure dashboard

Once you select the time and click on confirm , you will see:

It's create a database with copy only option till the selected time in the restore wizard
It creates a database with copy only option for the selected time in the restore wizard

Q: How will HADR function for SQL Azure databases.
A:  As, there is no DB/t-log backups so, features like AlwaysON/mirroring/log shipping etc. doesn’t exist in Azure DB world for now.

I will write my next post on HADR for SQL Azure databases and how it’s different from on-premise SQL server HADR.

The intent of this series is to pick some of the features which have changed and will change your way of working with databases.


The views expressed on this website/blog are mine alone and do not reflect the views of my company. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Unable to take tail log backup?

While going through the database forums on MSDN site, I came across a very good question about tail log backups.

As per the article : –, we can take tail log backups even if the database is in offline mode.  Even when my database is corrupt and not able to come online.

The question was why the tail log backup is failing when the database is offline (Essence of the discussion in the forum)

Even I tried myself and found it very interesting. Yes, it was failing for me with the error:
Msg 942, Level 14, State 3, Line 1
Database ‘xxxx’ cannot be opened because it is offline.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.

Did little bit of research and thought of simulating the database crash and check if it makes any difference. Here is what I did:

1. Created a database Test
2. Took a full database backup to make sure we are able to take the t-log backup
3. Set the database to offline mode
4. Rename the mdf file of the database
5. Bring the database online which fails and the database goes to offline mode.
6. ran a query select * from sys.databases command and checked the state_desc column for 2 databases 1. was which was set to offline and 2. was the one after running the above mentioned steps.
7. Took the tail log backup for the second database and it succeeded.

Now we got to know whatever mentioned in the article correct.

Now, you may be thinking why the tail backup succeeded for the second database.

The answer is unless the database is marked as recovery_pending the tail log backup can’t be done.  In the first case the database was marked as offline because there was no crash but was a normal offline.  But in the second scenario, It was a crash and database wasn’t recovered due to missing mdf file.

Another reason of why restore of SQL DB took long time to complete

Issue description
I was working on an issue where the restore of a backup was running fast on a low configuration machine but slower on the huge one.Slow configuration machine was a developement server however the other one was a production server with huge configuration.

Normally when we start troublehsooting on such scenarios we check the no. of Vitual log files(VLF) by running Dbcc loginfo command. But this one was making us contemplate that if restore on the development server is fast then there shouldn’t be the issue of huge no. of vlfs.

I am losing track a little bit here to explain you why no. of vlfs is an issue. when we have huge number of vlfs then while redo phase of the restore it takes long time.Even when the database starts up then also it takes long time to bring the database online due the similar reasons. For the later one there is a bug also which is mentioned in the article:

Anyways, lets move forward and find out what happened. I am going to describe all relevant steps which I took while troubleshooting. It may add more information in the blog and yet more knowledge for you.

As mentioned earlier – first point was checking for the no. of VLFs which came to be alright.
1. Then enabled the trace flags : – dbcc traceon (3213,3605,3004, -1)
3213 : – prints the configuration parameter
3605 : – sends the output of the trace flags to the error logs
3004 : – Prints the progress messages while backup and restore and the output was something like :

On the problem server:
02/28/2011 16:27:08,spid80,Unknown,FileHandleCache: 0 files opened. CacheSize: 10
02/28/2011 16:27:08,spid80,Unknown,Restore: LogZero complete

02/28/2011 16:27:08,spid80,Unknown,Restore: Waiting for log zero on Test –>if you see major time is being spent while zeroing the file.

02/28/2011 16:19:45,spid80,Unknown,Zeroing completed on E:DatabaseLogsDevelopmentTEST_1.ldf
02/28/2011 16:1:26,spid80,Unknown,Restore: Transferring data to Test 02/28/2011 16:19:26,spid80,Unknown,Restore: Restoring backup set 02/28/2011 16:19:26,spid80,Unknown,Zeroing E:DatabaseLogsDevelopmentTEST_1.ldf from page 1 to 16496 (0x2000 to 0x80e0000) 02/28/2011 16:19:26,spid80,Unknown,Restore: Containers are ready 02/28/2011 16:19:26,spid80,Unknown,Restore: PreparingContainers

On the development server:
02/28/2011 16:39:17,spid55,Unknown,Restore: LogZero complete –> here it completed within 2 minutes but on problem server it took 7 minutes.

02/28/2011 16:39:17,spid55,Unknown,Restore: Waiting for log zero on Test
02/28/2011 16:37:51,spid55,Unknown,Zeroing completed on C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLTest_1.ldf
02/28/2011 16:37:50,spid55,Unknown,Restore: Transferring data to Test
02/28/2011 16:37:50,spid55,Unknown,Restore: Restoring backup set
02/28/2011 16:37:50,spid55,Unknown,Zeroing C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLTest_1.ldf from page 1 to 16496 (0x2000 to 0x80e0000)

2. Based on the output above we got to know that it’s the zeroing of file which is causing the trouble. point to be noted : – it’s an ldf file.
3. Enabling instant file initialization clicked my mind. After reading the article : – I came to know that for ldf we can’t enable the instant file intialization.
4. We suspected that the issue may be with the disks. When the disk tests were run, we came to know that the issue was with the disk throughput and worked on the disk to resolve the issue.