PowerShell | SQL Server Portal

Posts Tagged ‘PowerShell’

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

Posted in Backup & Restore, SQL SERVER, tagged PowerShell, raresql, restore from URL, SQL, SQL Server, Windows Azure Storage on December 31, 2014| Leave a Comment »

In my earlier articles, I explained, how to RESTORE the backup from URL using T-SQL, SQL Server Management Studio (SSMS) from windows Azure storage and it was quite simple & easy to understand. In this article, I will explain how to RESTORE the backup from URL using PowerShell.

Pre-Requisite :

Let me now demonstrate in few easy steps how to RESTORE backup from URL using 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 – RESTORE backup to URL :
Once you created the the Azure account, a storage and container and made sure that your backup is available in the windows Azure storage, you can RESTORE the backup from URL using given below script. In this script, first of all I declared the variables , then created credentials. After that RESTORED 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 Credential
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"

# Restore Database and move files
$newDataFilePath = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile ("AdventureWorks2012_Data","C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Data.mdf")
$newLogFilePath = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("AdventureWorks2012_Log","C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_log.ldf")
Restore-SqlDatabase -Database "AdventureWorks2012" -SqlCredential $credentialName -BackupFile $backupFile -RelocateFile @($newDataFilePath,$newLogFilePath)

As you can see, the messages show that your backup has been restored successfully.

Read Full Post »

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.