Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Links

Wiki
Blog

Forum Statistics

Users
Members:
1878
Members Online:
2
Guests Online:
41

Total Post History
Posts:
81448
Topics:
18714

7-Day Post History
New Posts:
1
New Topics:
0
Active Topics:
1

Our newest member
kowilmar1990z

Other

FAQ
All times are UTC [ DST ]

Google Ads

SQL Server backup to URL

Please wait...

SQL Server backup to URL

Postby SQLDenis on Fri Jan 25, 2013 1:26 pm

Here is what it looks like

With the SQL Server 2012 SP1 CU2 release a new important feature was added: ability to back up and restore a database straight from Azure Blob storage:

  1.  
  2. CREATE CREDENTIAL mycredential WITH IDENTITY = 'mystorageaccount'
  3.        ,SECRET = '' ;
  4.  
  5. BACKUP DATABASE AdventureWorks2012
  6.       TO URL = 'https://mystorageaccount.blob.core.windows.net/mycontainer/db.bak'
  7.       WITH CREDENTIAL = 'mycredential'
  8.      ,STATS = 5;
  9.  
  10. RESTORE DATABASE AdventureWorks2012
  11.      FROM URL = 'https://mystorageaccount.blob.core.windows.net/mycontainer/db.bak'
  12.      WITH CREDENTIAL = 'mycredential'
  13.  
  14.  


See post here: http://rusanu.com/2013/01/25/sql-server-backup-to-url/
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: SQL Server backup to URL

Postby AlexCuse on Fri Jan 25, 2013 3:30 pm

That's pretty cool. I wonder if there's any way to get access to the binary data for a backup in a CLR proc to back up to a non-azure location in a similar fashion, or if you'd need to back up to a file and then move that.
Say what you like about the tenets of National Socialism Dude, at least it's an ethos
User avatar
AlexCuse
LTD Admin
LTD Admin
LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031
LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031
LTD Gold - Rating: 1031
 
Posts: 5523
Joined: Tue Oct 09, 2007 5:26 pm
Location: Pennsylvania, US
Unrated

Re: SQL Server backup to URL

Postby Thirster42 on Mon Jan 28, 2013 4:30 pm

AlexCuse wrote:That's pretty cool. I wonder if there's any way to get access to the binary data for a backup in a CLR proc to back up to a non-azure location in a similar fashion, or if you'd need to back up to a file and then move that.


O_O
Ask three DBAs a question, and you’ll get four different answers.

-Brent Ozar
User avatar
Thirster42
Guru
Guru
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
 
Posts: 4646
Joined: Tue Nov 11, 2008 5:49 pm
Location: Jefferson City, Mo
Unrated

Re: SQL Server backup to URL

Postby tarwn on Tue Jan 29, 2013 12:19 pm

Actually, I suspect you could implement the blob service API and then give it a URL to your own "blob service", but I'm not sure what it would buy you.

Now if only they could do this in SQL Azure, instead of bacpac (esp since I recently had a case where the bacpac had errors when 'restoring' a db)

Um, yeah...I'm going to need you to come in on Saturday -- Bill Lumbergh, Office Space
User avatar
tarwn
LTD Admin
LTD Admin
LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897
LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897
LTD Gold - Rating: 897
 
Posts: 3838
Joined: Fri Oct 12, 2007 11:10 am
Location: Raleigh, NC, USA
Unrated

Re: SQL Server backup to URL

Postby AlexCuse on Tue Jan 29, 2013 8:41 pm

WTH is BACPAC? Some Azure backed thing?

Seems like what you are saying is that backup to URL - designed to make working in 'the cloud' easier - doesn't even work with Microsoft's own cloud storage provider? #-o

to the cloud!
Say what you like about the tenets of National Socialism Dude, at least it's an ethos
User avatar
AlexCuse
LTD Admin
LTD Admin
LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031
LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031
LTD Gold - Rating: 1031
 
Posts: 5523
Joined: Tue Oct 09, 2007 5:26 pm
Location: Pennsylvania, US
Unrated

Re: SQL Server backup to URL

Postby SQLDenis on Tue Jan 29, 2013 9:02 pm

AlexCuse wrote:WTH is BACPAC? Some Azure backed thing?



It is the feature that seasoned SQL Server people hate the most. Data-Tier Application Package
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: SQL Server backup to URL

Postby tarwn on Wed Jan 30, 2013 12:19 am

Think of it as scripting your entire database out, schema and data (I think MySQl does/did backups like this?). Because of how they did the architecture for SQL in Azure, they can't do a standard SQL backup, so instead they used this method to provide backups. Unfortunately I have personally seen cases where the BAC file could not be restored due to constraint/key violations that did not exist in the actual database. And there is no guarantee of transactional consistency (which is why the redGate service makes a copy of the database first, to ensure it's in a stable state before kicking off the backup).

The backup to a URL may not be related to this. If I had done it, it would do a standard SQL backup but write chunks to blob storage via streams. Basically it would operate just like uploading a large file in chunks, calling the Azure REST API endpoint to incrementally stream up a new block of data to append to the blob (as well as update the list of blocks included in the blob).

I was just sidetracking the conversation with the SQL Azure not doing real backups topic, sorry about that :)

Um, yeah...I'm going to need you to come in on Saturday -- Bill Lumbergh, Office Space
User avatar
tarwn
LTD Admin
LTD Admin
LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897
LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897
LTD Gold - Rating: 897
 
Posts: 3838
Joined: Fri Oct 12, 2007 11:10 am
Location: Raleigh, NC, USA
Unrated