backup to URL | SQL Server Portal

Posts Tagged ‘backup to URL’

SQL SERVER 2014 – How to take backup to URL (Windows Azure Storage) – PowerShell

Posted in Solutions, SQL SERVER, tagged backup to URL, PowerShell, raresql, SQL, SQL Server, Windows Azure Storage on December 27, 2014| 2 Comments »

SQL Server backup to URL is a new feature shipped with SQL Server 2014. I explained how to take backup via SSMS (SQL Server Management Studio) in my earlier article. Today, I will demonstrate how to take SQL Server backup to URL via PowerShell.

Pre-Requisite :

Microsoft Azure – How to create storage and container

Let me now demonstrate in few easy steps how to take backup to URL via PowerShell.

Step 1 – Create Account, storage & container :
First of all, you need to create a windows Azure account, a storage and a container as explained in this article.

Step 2 – Take backup to URL :
Once you created the the Azure account, a storage and container, you can take a backup on URL using given below script. In this script, first of all I declared the variables , then created credentials. After that take the backup as shown below. Rest of the script is self explanatory. Make sure that you have downloaded PowerShell Extensions for SQL Server in case you are using Windows PowerShell ISE instead of Windows PowerShell.


#Import sql module
Import-Module sqlps

#Create & set variables
$serverPath = "SQLSERVER:\SQL\WIN-KK48BQM9IS0\DEFAULT"
$storageAccount = "raresql"
$storageKey = "/ByNUTZqJ6EcJR/VQcNmNj+zSu++iCfbcxlyWye6Ok9uhsd5jsd62sjhsd7ksdh7sksdsdlhsd4bsdhsd52ksd=="
$secureString = convertto-securestring $storageKey -asplaintext -force
$credentialName = "My_Credential"

# Create Credentials
New-sqlcredential -Name $credentialName -Identity $storageAccount -Secret $secureString

# Set SQL Server instance Path
CD $serverPath

# Set the backup file name
$backupFile = "https://raresql.blob.core.windows.net/sql-backup/AdventureWorks2012_25_Dec_2014.bak"

# Take Backup Database to URL
Backup-SqlDatabase -Database "AdventureWorks2012" -backupFile $backupFile -SqlCredential $credentialName -CompressionOption On

Step 3 – Verify backup to URL :
Lets go to the container inside the storage and check if the backup is available there or NOT because we saw the confirmation report in the above step.

Now, you can see above that your backup is available on Azure, what you have taken in the above step.

Read Full Post »

SQL SERVER 2014 – How to take backup to URL (Windows Azure Storage) – SSMS

Posted in Solutions, tagged backup to URL, Download Publishing Profile, raresql, SQL, SQL Server, Windows Azure Storage on December 26, 2014| Leave a Comment »

In my earlier article, I explained how to take SQL Server backup to URL in SQL Server 2014 using T-SQL. In this article, I will explain how to take SQL Server backup to URL using SQL Server Management Studio (SSMS).

Pre-Requisite :

Microsoft Azure – How to create storage and container

Let me demonstrate in few easy steps how to take backup to URL using SSMS.

Step 1 – Create Account, storage & container :
First of all, you need to create a windows Azure account, a storage and a container as explained in this article. This step is mandatory to move forward.

Step 2 – Download Publishing Profile :
Once you have the details, you need to download the publishing profile. We will use this publishing profile in the next step to create credentials. Given below is the URL that will take you to your Microsoft Azure account, and once you log in your account, it will download the publishing profile having extension of .publishsettings.

https://manage.windowsazure.com/publishsettings/

Step 3 – Open Backup dialogue Box :
The next step is to take the backup of any database on Microsoft Azure. In order to do it, first of all select that database, right click on it, go to tasks and further select backup as shown below. This is the same procedure we follow to take the traditional backup.

Step 4 – Open Credential Dialogue box :
Once you selected the backup option, it will open the backup dialogue box. Here, you need to select the Back up to is URL. Here you see few backup options but for now you need to ignore these options and create credentials. Click the Create credential button as shown below.

Step 5 – Create Credential :
Now, you are in the credential creation screen, here you need to BROWSE the file which you have downloaded in Step 2. Then SELECT the CERTIFICATE, it will automatically populate the STORAGE(s) after AZURE account verification. Then you need to select the STORAGE. Once you are done with all selections, you need to give a NAME to the credential and press Create button.

Note : If you have already created the credentials, you do not need to follow this step.

Step 6 – Setup Backup options :
Once you created the credential, you will revert to back up dialogue box and your created credential will be selected automatically. Now you need to enter the FILE NAME of the backup as well as the AZURE STORAGE CONTAINER NAME that we have created in earlier article at Step 9 as shown below. Press OK button.

Step 7 – Back Up in Progress :
Once you pressed OK button, the back up operation will start, however as compared to local, this back up operation will be slow. Be patient and wait for it to finish the operation as shown below.

Step 8 – Verify backup to URL :
The back up is done now, lets go to the container inside the storage in Windows Azure and check if the backup is available there or NOT because we saw the confirmation report in the above step.

As you can see above, the Back up is available in Azure. Isn’t it cool 😉 ?

Read Full Post »

SQL SERVER 2014 – How to take backup to URL (Windows Azure Storage) – TSQL

Posted in Solutions, SQL SERVER, tagged backup to URL, raresql, SQL, SQL Server, Windows Azure Storage on December 6, 2014| Leave a Comment »

SQL Server backup to URL is a new feature shipped with SQL Server 2014. Now, you do not need to manage backup tapes, tape drives, storage etc etc. All you need to do is to create a Windows Azure account, a storage and a container, remaining stuff Azure will handle your backup 😉

Pre-Requisite :

Microsoft Azure – How to create storage and container

Let me now demonstrate in few easy steps how to take backup to URL.

Step 1 – Create Account, storage & container :
First of all you need to create a windows Azure account, a storage and a container as explained in this article.

Step 2 – Create Credential :
Once you have the details, you need to create the credential using given below script. But if you notice, this script requires two parameters namely IDENTITY & SECRET. So how will I get them? Actually IDENTITY is the storage name that we created in the previous article (Step 4) namely ‘raresql‘ and SECRET is the primary access key that we also picked up in the previous article (Step 5). Lets create a credential.

USE master
GO
CREATE CREDENTIAL [My_Credential]
WITH IDENTITY ='raresql'
,SECRET = '/ByNUTZqJ6EcJR/VQcNmNj+zSu++iCfbcxlyWye6Ok9uY3L5nw3XkndmAnDjiKn'
GO

Step 3 – Take backup to URL :
Once you created the credential, lets take a backup on URL using given below script, which requires two parameters, one is credential that we created in the above step and the second is the URL that we created in the previous article (Step 10).

BACKUP DATABASE AdventureWorks2012
TO URL = 'https://raresql.blob.core.windows.net/sql-backup/AdventureWorks2012.bak'
WITH CREDENTIAL = 'My_Credential'
,STATS = 5;
GO
--OUTPUT

Step 4 – Verify backup to URL :
Lets go to the container inside the storage and check if the backup is available there or NOT. Because, we saw the confirmation report in the above step.

I am sure, you will be more than excited to try this solution, as this will eliminate your headache of maintaining the tape, its drives and last but not the least, its issues.

Read Full Post »