Enhancements to SQL Server Backup to Cloud in SQL Server 2012 SP1 CU4

  • Article

Cumulative Update 4 for SQL Server 2012 Service Pack 1 includes enhancements to improve the SQL Server Backup and Restore to Cloud user experience. The enhancements include performance improvements, cleaning up invalid blobs when backups fail or are interrupted, and PowerShell support.  

To request this update, see this knowledgebase article.

Performance Improvements:

Performance improvements include faster return of restore metadata operations. The affected operations include:

  • RESTORE HEADERONLY
  • RESTORE FILELISTONLY
  • RESTORE LABELONLY

Cleaning up invalid blobs after an interrupted or failed backup:

If backup operation fails, it may result in an incomplete or invalid blob.SQL Server Backup to URL process attempts to cleanup cleaning blobs that result from a failed backup. However, if the backup fails due to prolonged or sustained network connectivity failure, backup to URL process may not be able gain access to the blob and the blob may remain orphaned. In such cases, you have to manually release the lease and delete the blob. This topic has the details on how.

 PowerShell Support:

This cumulative update also includes PowerShell support for the backup to cloud feature with changes to existing cmdlets and 4 new cmdlets. Following is a brief overview of the changes and sample syntax. 

For more information and code examples, see the following topics:

 Use PowerShell to Backup Multiple Databases to Windows Azure Blob Storage Service

 SQL Server Backup and Restore to Windows Azure Blob Storage Service

 

Existing cmdlet changes:

The Backup-SqLDatabase, and Restore-SqlDatabase cmdlets have new parameters to support creating a SQL Server backup to or restore from a Windows Azure Storage Container. The following are the new parameters:

BackupContainer:

Using this parameter you can specify the URL of the container as the location for your backup files and let the Backup to URL process generate the file names. Alternatively you can use the BackupFile parameter to specify both the location and the name of the file. You can also use this parameter to set locations for a folder on a disk backup device. This parameter can be useful when backing up multiple databases in a given instance.

SqlCredential:

This parameter allows you to specify the SQL Credential name of object that stores the Windows Azure Storage authentication information.

               Sample Syntax:

  • This command backs up all databases on the server instance 'Computer\Instance' to the Windows Azure Blob storage container using the BackupContainer parameter.

 Get-ChildItem SQLSERVER:\SQL\Computer\Instance\Databases | Backup-SqlDatabase –BackupContainer "https://storageaccountname.blob.core.windows.net/containername" -SqlCredential "SQLCredentialName"

 

  • This command creates a full backup of the database 'MyDB'. It uses the BackupFile parameter to specify the location (URL) and the backup file name. The SqlCredential parameter is used to specify the name of the SQL Server credential.

 Backup-SqlDatabase –ServerInstance "Computer\Instance" –Database –"MyDB" -BackupFile "https://storageaccountname.blob.core.windows.net/containername/MyDB.bak" -SqlCredential "SQLCredentialName"

 

  • This command restores the full database 'MyDB' from the file on the Windows Azure Blob storage service to a SQL Server instance.

 Restore-SqlDatabase -ServerInstance Computer\Instance -Database MyDB -BackupFile "https://mystorageaccountname.blob.core.windows.net/container/MyDB.bak" -SqlCredential "mySqlCredential"

New cmdlets:

New-SQLCredential

The New-SqlCredential cmdlet creates a new SQL Server Credential object. The SQL Server credential is required when backing up to or restoring from the Windows Azure storage service, and is used to store the Windows Azure storage account name and access key information.

Sample Syntax:

  • This command creates SQL Credential “mysqlcredential” on the current instance of SQL Server.

 New-SqlCredential – name "mysqlcredential" –Identity "storageAccount" –secret “storageAccessKey”

 

Set-SqlCredential

You can set the Identity and password properties for a SQL Credential object using this cmdlet. This cmdlet supports the two following modes of operation:

By specifying the path or location of the credential including the credential name using the –path parameter.

By passing a Smo.Credential object to the object using the –InputObject.

Sample Syntax:

  • This command sets the identity of MySqlCredential to ‘mystorageaccount’.

Set-SqlCredential -Path "SQLSERVER:\SQL\Computer\Instance\Credentials\MySqlCredential" –Identity "mystorageaccount"

  • This command retrieves the credential object from the Get-Credential cmdlet and then pipes it to the Set-Sql Credential cmdlet to set the identity of mySqLCrendential to ‘mystorageaccount’.

$cred = Get-SqlCredential -Name MySqlCredential

$cred | Set-SqlCredential –Identity "mystorageaccount"

Get-SqlCredential

The Get-SqlCredential returns the SQL credential object.  This cmdlet supports the following modes of operation: 

By specifying the name of the SQL credential and the path of the instance.

By specifying the name of the SQL Credential and the server object.

 

Sample Syntax:

  • This command returns the credential object.

Get-SqlCredential –Name "mycredential"

 

Remove-SqlCredential

The Remove-SqlCredential cmdlet deletes a SQL Server credential object. This cmdlet supports the following two modes of operation:

By specifying the path or location of the credential and the credential name using the –path parameter.

By passing a Smo.Credential object to the object using the –InputObject parameter.

 

Sample Syntax:

  • The command retrieves the credential object from the Get-Credential cmdlet and then pipes it to the Remove-Sql Credential cmdlet to delete the SQL Credential ‘MySqlCredential’.

$cred = Get-SqlCredential -Name "MySqlCredential"

$cred | Remove-SqlCredential

 

This blog post is about the changes that were made in the Cumulative Update 4 for SQL Server 2012 SP1 release and assumes some amount of familiarity with SQL Server native backup to cloud functionality released in SQL Server 2012 SP1 CU2. For information on SQL Server Backup and Restore to Cloud, see the following articles:

 

SQL Server Backup and Restore to Cloud Simplified

 

Getting Started Tutorial