Restoring Azure SQL Database

A major benefit of Azure SQL Database (PaaS) is the fact that Microsoft manages the backups – it’s great because recovering to a point in time is straightforward.

With all editions of the database full backups are taken every week, differential backups are taken hourly, and transaction log backups are taken every 5 minutes. When you create a database a full backup is scheduled straightaway.

Let’s take a look.

An issue has occurred in SQLDB6 and I want to go back to XYZ AM/PM.

pp1.JPG

Click on your database and look for the RESTORE ICON.

pp2

Enter the database name to restore to and select the restore point.

pp3

You won’t have the ability to use the same name of the restoring database and the database that you want to replace; if you try you get the screen shot below: To get around this I think you would need to drop the old one once the new one has restored then do a rename.

pp5

SQL Logins

If you created a SQL Login using the contained database user model then fixing broken links after a restore is not an issue.

I have created logins using both the contained model and the traditional model as shown below to show the difference.

pp6

Contained database user

You can login straightaway (once the restore completes) with the same username and password.

pp7

Traditional Model

However if you used the older / classic approach you will need to fix the orphaned logins.

pp9

Under the context of master run:

SELECT *
FROM sys.sql_logins
WHERE type = 'S' and name = 'LuckyLemon'

pp10

So we know it exists in master, so run the following in the user database.

ALTER USER LuckyLemon WITH Login = LuckyLemon;

pp11.JPG

If you want to change the PW at this time then you can if you want via:

ALTER LOGIN LuckyLemon WITH PASSWORD = '<enterStrongPasswordHere>';

The morale of the story is… Try to use contained database users where possible.

One thought on “Restoring Azure SQL Database

  1. Pingback: Restoring An Azure SQL Database – Curated SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s