SQL Server Backup / Restore from URL

I think many have covered how you should backup your SQL Server database to Azure storage (also known as backup to URL) but what about restoring? Lets assume you have setup backups and they are working, this is what I usually do.

First, I issue the following command to get a general understanding of the configuration settings applied to a database (this was done via managed backups feature).

USE msdb
GO
SELECT * FROM managed_backup.fn_backup_db_config ('yourdb')

generaldbmanaged

Great, the next code block is about getting a list of the backups that are located within my storage container.

USE msdb
SELECT *
FROM managed_backup.fn_available_backups ('yourdb') 

From here you can build a chain for your restore point.
backuplist

what will your code look like?


 USE [master]

RESTORE DATABASE [TestDBCopy]
 FROM  URL =
  N'https://yourcontainer.blob.core.windows.net/backupcontainer/TestDBfull.bak'
   WITH  FILE = 1,  MOVE N'TestDB' TO N'L:\Data\TestDBcopy.mdf',  MOVE N'TestDB_log' TO N'L:\Log\TestDB_logcopy.ldf',
    NORECOVERY,  NOUNLOAD,  STATS = 5

RESTORE LOG [TestDBCopy]
FROM  URL = N'https://yourcontainer.blob.core.windows.net/backupcontainer/TestDBtrans1.log'
 WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5

RESTORE LOG [TestDBCopy] FROM  URL
= N'https://yourcontainer.blob.core.windows.net/backupcontainer/TestDBtrans2.log'
 WITH  FILE = 1,  RECOVERY,  NOUNLOAD,  STATS = 5

It is similar to what you are used to, such as the FROM DISK commands. The only extra steps you need is the SAS, secret and credential setup, which you obviously have already if you have successfully setup the backups in the first place.

 

Advertisements

1 thought on “SQL Server Backup / Restore from URL

  1. Pingback: Restoring Databases from URL – 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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s