SQL Server Backup and Restore with Windows Azure Blob Storage Service
This topic introduces concepts, considerations, and sample code that is central to configuring and writing SQL Server backups to and restoring from the Windows Azure Blob storage service. It also provides a summary of the benefits of using the Windows Azure Blob service to store SQL Server backups.
This feature released in SQL Server 2012 SP1 CU2, enables SQL Server backup and restore directly to the Windows Azure Blob service. This feature can be used to backup SQL Server databases on an on-premises instance or an instance of SQL Server running a hosted environment such as Windows Azure Virtual Machine. Backup to cloud offers benefits such as availability, limitless geo-replicated off-site storage, and ease of migration of data to and from the cloud. In this release, you can issue BACKUP or RESTORE statements by using tsql or SMO. Back up to or restore from the Windows Azure Blob storage service by using SQL Server Management Studio Backup or Restore Wizard is not available in this release.
In SQL Server 2012 SP1 CU4, backup to and restore from Windows Azure Blob storage service using PowerShell is supported. To request this update, see this knowledgebase article. This topic includes PowerShell scripts in the example section. For sample PowerShell scripts to manage multiple backups, see Use PowerShell to Backup Multiple Databases to Windows Azure Blob Storage Service.
Benefits of Using the Windows Azure Blob Service for SQL Server Backups
Flexible, reliable, and limitless off-site storage: Storing your backups on Windows Azure Blob service can be a convenient, flexible, and easy to access off-site option. Creating off-site storage for your SQL Server backups can be as easy as modifying your existing scripts/jobs. Off-site storage should typically be far enough from the production database location to prevent a single disaster that might impact both the off-site and production database locations. By choosing to geo replicate the Blob storage you have an extra layer of protection in the event of a disaster that could affect the whole region. In addition, backups are available from anywhere and at any time and can easily be accessed for restores.
Backup Archive: The Windows Azure Blob Storage service offers a better alternative to the often used tape option to archive backups. Tape storage might require physical transportation to an off-site facility and measures to protect the media. Storing your backups in Windows Azure Blob Storage provides an instant, highly available, and a durable archiving option.
No overhead of hardware management:There is no overhead of hardware management with Windows Azure services. Windows Azure services manage the hardware and provide geo-replication for redundancy and protection against hardware failures.
Currently for instances of SQL Server running in a Windows Azure Virtual Machine, backing up to Windows Azure Blob storage services can be done by creating attached disks. However, there is a limit to the number of disks you can attach to a Windows Azure Virtual Machine. This limit is 16 disks for an extra large instance and fewer for smaller instances. By enabling a direct backup to Windows Azure Blob Storage, you can bypass the 16 disk limit.
In addition, the backup file which now is stored in the Windows Azure Blob storage service is directly available to either an on-premises SQL Server or another SQL Server running in a Windows Azure Virtual Machine, without the need for database attach/detach or downloading and attaching the VHD.
Cost Benefits: Pay only for the service that is used. Can be cost-effective as an off-site and backup archive option. See the Windows Azure Billing Considerations section for more information and links.
Windows Azure Billing Considerations:
Understanding Windows Azure storage costs enables you to forecast the cost of creating and storing backups in Windows Azure.
The Windows Azure pricing calculator can help estimate your costs.
Storage: Charges are based on the space used and are calculated on a graduated scale and the level of redundancy. For more details, and up-to-date information, see the Data Management section of the Pricing Details article.
Data Transfers: Inbound data transfers to Windows Azure are free. Outbound transfers are charged for the bandwidth use and calculated based on a graduated region-specific scale. For more details, see the Data Transfers section of the Pricing Details article.
Requirements, Components, and Concepts
In this section:
Security
Introduction to Key Components and Concepts
Windows Azure Blob Storage Service
SQL Server Components
Limitations
Support for Backup/Restore Statements
Security
The following are security considerations and requirements when backing up to or restoring from the Windows Azure Blob storage services.
When creating a container for the Windows Azure Blob storage service, we recommend that you set the access to private. Setting the access to private restricts the access to users or accounts able to provide the necessary information to authenticate to the Windows Azure account.
Uwaga dotycząca zabezpieczeń SQL Server requires Windows Azure account name and access key authentication to be stored in a SQL Server Credential. This information is used to authenticate to the Windows Azure account when it performs backup or restore operations.
The user account that is used to issue BACKUP or RESTORE commands should be in the db_backup operator database role with Alter any credential permissions.
Installation Prerequisites
SQL Server running on an Azure Virtual Machine: If you are installing SQL Server on the Windows Azure Virtual Machine, install SQL Server 2012 SP1 CU2, or update your existing instance. To request the update from Microsoft Customer Support, see this article.
SQL Server on-premises: SQL Server 2012, SP1 CU2 or later versions includes this feature. To request the update from Microsoft Customer Support, see this article.
Introduction to Key Components and Concepts
The following two sections introduce the Windows Azure Blob storage service, and the SQL Server components used when backing up to or restoring from the Windows Azure Blob storage service. It is important to understand the components and the interaction between them to do a backup to or restore from the Windows Azure Blob storage service.
Creating a Windows Azure account is the first step to this process. SQL Server uses the Windows Azure storage account name and its access key values to authenticate and write and read blobs to the storage service. The SQL Server Credential stores this authentication information and is used during the backup or restore operations. For a complete walkthrough of creating a storage account and performing a simple restore, see Tutorial Using Windows Azure Storage Service for SQL Server Backup and Restore.
Windows Azure Blob Storage Service
Storage Account: The storage account is the starting point for all storage services. To access the Windows Azure Blob Storage service, first create a Windows Azure storage account. The storage account name and its access key properties are required to authenticate to the Windows Azure Blob Storage service and its components.
Container: A container provides a grouping of a set of Blobs, and can store an unlimited number of Blobs. To write a SQL Server backup to the Windows Azure Blob service, you must have at least the root container created.
Blob: A file of any type and size. There are two types of blobs that can be stored in the Windows Azure Blob storage service: block and page blobs. SQL Server backup uses page Blobs as the Blob type. Blobs are addressable using the following URL format: https://<storage account>.blob.core.windows.net/<container>/<blob>
For more information about the Windows Azure Blob storage service, see How to use the Windows Azure Blob Storage Service
For more information about page Blobs, see Understanding Block and Page Blobs
SQL Server Components
URL: A URL specifies a Uniform Resource Identifier (URI) to a unique backup file. The URL is used to provide the location and name of the SQL Server backup file. In this implementation, the only valid URL is one that points to a page Blob in a Windows Azure storage account. The URL must point to an actual Blob, not just a container. If the Blob does not exist, it is created. If an existing Blob is specified, BACKUP fails, unless the “WITH FORMAT” option is specified.
Przestroga |
---|
If you choose to copy and upload a backup file to the Windows Azure Blob storage service, use page blob as your storage option. Restores from Block Blobs are not supported. RESTORE from a block blob type fails with an error. |
Here is a sample URL value: http[s]://ACCOUNTNAME.Blob.core.windows.net/<CONTAINER>/<FILENAME.bak>. HTTPS is not required, but is recommended.
Credential: A SQL Server credential is an object that is used to store authentication information required to connect to a resource outside of SQL Server. Here, SQL Server backup and restore processes use credential to authenticate to the Windows Azure Blob storage service. The Credential stores the name of the storage account and the storage account access key values. Once the credential is created, it must be specified in the WITH CREDENTIAL option when issuing the BACKUP/RESTORE statements. For more information about how to view, copy or regenerate storage account access keys, see Storage Account Access Keys.
For step by step instructions about how to create a SQL Server Credential, see Create a Credential example later in this topic.
For general information about credentials, see Credentials
For information, on other examples where credentials are used, see Create a SQL Server Agent Proxy.
Limitations
The maximum backup size supported is 1 TB.
In this implementation, you can issue backup or restore statements by using TSQL or SMO. A backup to or restoring from the Windows Azure Blob storage service by using SQL Server Management Studio Backup or Restore wizard is not currently enabled.
Creating a logical device name is not supported. So adding URL as a backup device using sp_dumpdevice or through SQL Server Management Studio is not supported.
Appending to existing backup blobs is not supported. Backups to an existing Blob can only be overwritten by using the WITH FORMAT option.
Backup to multiple blobs in a single backup operation is not supported. For example, the following returns an error:
BACKUP DATABASE AdventureWorks2012 TO URL = 'https://mystorageaccount.blob.core.windows.net/mycontainer/AdventureWorks2012_1.bak' URL = 'https://mystorageaccount.blob.core.windows.net/mycontainer/AdventureWorks2012_2.bak' WITH CREDENTIAL = 'mycredential' ,STATS = 5; GO
Specifying a block size with BACKUP is not supported.
Specifying MAXTRANSFERSIZE is not supported.
Specifying backupset options - RETAINDAYS and EXPIREDATE are not supported.
SQL Server has a maximum limit of 259 characters for a backup device name. The BACKUP TO URL consumes 36 characters for the required elements used to specify the URL – ‘https://.blob.core.windows.net//.bak’, leaving 223 characters for account, container, and blob names put together.
Support for Backup/Restore Statements
Backup/Restore Statement |
Supported |
Exceptions |
Comments |
BACKUP |
√ |
BLOCKSIZE, and MAXTRANSFERSIZE are not supported. |
Requires WITH CREDENTIAL specified |
RESTORE |
√ |
Requires WITH CREDENTIAL specified |
|
RESTORE FILELISTONLY |
√ |
Requires WITH CREDENTIAL specified |
|
RESTORE HEADERONLY |
√ |
Requires WITH CREDENTIAL specified |
|
RESTORE LABELONLY |
√ |
Requires WITH CREDENTIAL specified |
|
RESTORE VERIFYONLY |
√ |
Requires WITH CREDENTIAL specified |
|
RESTORE REWINDONLY |
− |
For syntax and general information about backup statements, see BACKUP (Transact-SQL).
For syntax and general information about restore statements, see RESTORE (Transact-SQL).
Support for Backup Arguments
Argument |
Supported |
Exception |
Comments |
DATABASE |
√ |
||
LOG |
√ |
||
TO (URL) |
√ |
Unlike DISK and TAPE, URL does not support specifying or creating a logical name. |
This argument is used to specify the URL path for the backup file. |
MIRROR TO |
− |
||
WITH OPTIONS: |
|||
CREDENTIAL |
√ |
WITH CREDENTIAL is only supported when using BACKUP TO URL option to back up to the Windows Azure Blob storage service. |
|
DIFFERENTIAL |
√ |
||
COPY_ONLY |
√ |
||
COMPRESSION|NO_COMPRESSION |
√ |
||
DESCRIPTION |
√ |
||
NAME |
√ |
||
EXPIREDATE | RETAINDAYS |
− |
||
NOINIT | INIT |
− |
This option is ignored if used. Appending to blobs is not possible. To overwrite a backup use the FORMAT argument. |
|
NOSKIP | SKIP |
− |
||
NOFORMAT | FORMAT |
√ |
This option is ignored if used. A backup taken to an existing blob fails unless WITH FORMAT is specified. The existing blob is overwritten when WITH FORMAT is specified. |
|
MEDIADESCRIPTION |
√ |
||
MEDIANAME |
√ |
||
BLOCKSIZE |
− |
||
BUFFERCOUNT |
√ |
||
MAXTRANSFERSIZE |
− |
||
NO_CHECKSUM | CHECKSUM |
√ |
||
STOP_ON_ERROR | CONTINUE_AFTER_ERROR |
√ |
||
STATS |
√ |
||
REWIND | NOREWIND |
− |
||
UNLOAD | NOUNLOAD |
− |
||
NORECOVERY | STANDBY |
√ |
||
NO_TRUNCATE |
√ |
For more information about backup arguments, see BACKUP (Transact-SQL).
Support for Restore Arguments
Argument |
Supported |
Exceptions |
Comments |
DATABASE |
√ |
||
LOG |
√ |
||
FROM (URL) |
√ |
The FROM URL argument is used to specify the URL path for the backup file. |
|
WITH Options: |
|||
CREDENTIAL |
√ |
WITH CREDENTIAL is only supported when using RESTORE FROM URL option to restore from Windows Azure Blob Storage service. |
|
PARTIAL |
√ |
||
RECOVERY | NORECOVERY | STANDBY |
√ |
||
LOADHISTORY |
√ |
||
MOVE |
√ |
||
REPLACE |
√ |
||
RESTART |
√ |
||
RESTRICTED_USER |
√ |
||
FILE |
− |
||
PASSWORD |
√ |
||
MEDIANAME |
√ |
||
MEDIAPASSWORD |
√ |
||
BLOCKSIZE |
√ |
||
BUFFERCOUNT |
− |
||
MAXTRANSFERSIZE |
− |
||
CHECKSUM | NO_CHECKSUM |
√ |
||
STOP_ON_ERROR | CONTINUE_AFTER_ERROR |
√ |
||
FILESTREAM |
√ |
||
STATS |
√ |
||
REWIND | NOREWIND |
− |
||
UNLOAD | NOUNLOAD |
− |
||
KEEP_REPLICATION |
√ |
||
KEEP_CDC |
√ |
||
ENABLE_BROKER | ERROR_BROKER_CONVERSATIONS | NEW_BROKER |
√ |
||
STOPAT | STOPATMARK | STOPBEFOREMARK |
√ |
For more information about Restore arguments, see RESTORE Arguments (Transact-SQL).
Examples
This section contains the following examples.
Create a Credential
Backing up a complete database
Backing up the database and log
Creating a full file backup of the primary filegroup
Creating a differential file backup of the primary filegroups
Restoring a database and move files
Restoring to a point-in-time using STOPAT
Create a Credential
The following example creates a credential that stores the Windows Azure Storage authentication information.
Tsql
IF NOT EXISTS (SELECT * FROM sys.credentials WHERE credential_identity = 'mycredential') CREATE CREDENTIAL mycredential WITH IDENTITY = 'mystorageaccount' ,SECRET = '<storage access key>' ;
C#
// Connect to default sql server instance on local machine Server server = new Server("."); string identity = "mystorageaccount"; string secret = "<storage access key>"; // Create a Credential string credentialName = "mycredential"; Credential credential = new Credential(server, credentialName); credential.Create(identity, secret);
PowerShell
# create variables $storageAccount = "mystorageaccount" $storageKey = "<storage access key>" $secureString = convertto-securestring $storageKey -asplaintext -force $credentialName = "mycredential" $srvPath = "SQLSERVER:\SQL\COMPUTERNAME\INSTANCENAME" # for default instance, the $srvpath variable would be \"SQLSERVER:\SQL\COMPUTERNAME\DEFAULT\" # Create a credential New-SqlCredential -Name $credentialName -Path $srvpath -Identity $storageAccount -Secret $secureString
Backing up a complete database
The following example backs up the AdventureWorks2012 database to the Windows Azure Blob storage service.
Tsql
BACKUP DATABASE AdventureWorks2012 TO URL = 'https://mystorageaccount.blob.core.windows.net/mycontainer/AdventureWorks2012.bak' WITH CREDENTIAL = 'mycredential' ,COMPRESSION ,STATS = 5; GO
C#
// Connect to default sql server instance on local machine Server server = new Server("."); string identity = "mystorageaccount"; string credentialName = "mycredential"; string dbName = "AdventureWorks2012"; string blobContainerName = "mycontainer"; // Generate Unique Url string url = String.Format(@"https://{0}.blob.core.windows.net/{1}/{2}-{3}.bak", identity, blobContainerName, dbName, DateTime.Now.ToString("s").Replace(":", "-")); // Backup to Url Backup backup = new Backup(); backup.CredentialName = credentialName; backup.Database = dbName; backup.CompressionOption = BackupCompressionOptions.On; backup.Devices.AddDevice(url, DeviceType.Url); backup.SqlBackup(server);
PowerShell
# create variables $backupUrlContainer = "https://mystorageaccount.blob.core.windows.net/mycontainer/" $credentialName = "mycredential" $srvPath = "SQLSERVER:\SQL\COMPUTERNAME\INSTANCENAME" # for default instance, the $srvpath varilable would be \"SQLSERVER:\SQL\COMPUTERNAME\DEFAULT\" # navigate to SQL Server Instance CD $srvPath $backupFile = $backupUrlContainer + "AdventureWorks2012" + ".bak" Backup-SqlDatabase -Database AdventureWorks2012 -backupFile $backupFile -SqlCredential $credentialName -CompressionOption On
Backing up the database and log
The following example backups up the AdventureWorks2012 sample database, which uses the simple recovery model by default. To support log backups, the AdventureWorks2012 database is modified to use the full recovery model. The example then creates a full database backup to Windows Azure Blob, and after a period of update activity, backs up the log. This example creates a backup file name with a datetime stamp.
Tsql
-- To permit log backups, before the full database backup, modify the database -- to use the full recovery model. USE master; GO ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL; GO -- Back up the full AdventureWorks2012 database. -- First create a file name for the backup file with DateTime stamp DECLARE @Full_Filename AS VARCHAR (300); SET @Full_Filename = 'https://mystorageaccount.blob.core.windows.net/mycontainer/AdventureWorks2012_Full_'+ REPLACE (REPLACE (REPLACE (CONVERT (VARCHAR (40), GETDATE (), 120), '-','_'),':', '_'),' ', '_') + '.bak'; --Back up Adventureworks2012 database BACKUP DATABASE AdventureWorks2012 TO URL = @Full_Filename WITH CREDENTIAL = 'mycredential'; ,COMPRESSION GO -- Back up the AdventureWorks2012 log. DECLARE @Log_Filename AS VARCHAR (300); SET @Log_Filename = 'https://mystorageaccount.blob.core.windows.net/mycontainer/AdventureWorks2012_Log_'+ REPLACE (REPLACE (REPLACE (CONVERT (VARCHAR (40), GETDATE (), 120), '-','_'),':', '_'),' ', '_') + '.trn'; BACKUP LOG AdventureWorks2012 TO URL = @Log_Filename WITH CREDENTIAL = 'mycredential' ,COMPRESSION; GO
C#
// Connect to default sql server instance on local machine Server server = new Server("."); string identity = "mystorageaccount"; string credentialName = "mycredential"; string dbName = "AdventureWorks2012"; string blobContainerName = "mycontainer"; // Generate Unique Url for data backup string urlDataBackup = String.Format(@"https://{0}.blob.core.windows.net/{1}/{2}_Data-{3}.bak", identity, blobContainerName, dbName, DateTime.Now.ToString("s").Replace(":", "-")); // Backup Database to Url Backup backupData = new Backup(); backupData.CredentialName = credentialName; backupData.Database = dbName; backup.CompressionOption = BackupCompressionOptions.On; backupData.Devices.AddDevice(urlDataBackup, DeviceType.Url); backupData.SqlBackup(server); // Generate Unique Url for data backup string urlLogBackup = String.Format(@"https://{0}.blob.core.windows.net/{1}/{2}_Log-{3}.bak", identity, blobContainerName, dbName, DateTime.Now.ToString("s").Replace(":", "-")); // Backup Database Log to Url Backup backupLog = new Backup(); backupLog.CredentialName = credentialName; backupLog.Database = dbName; backup.CompressionOption = BackupCompressionOptions.On; backupLog.Devices.AddDevice(urlLogBackup, DeviceType.Url); backupLog.Action = BackupActionType.Log; backupLog.SqlBackup(server);
PowerShell
#create variables $backupUrlContainer = "https://mystorageaccount.blob.core.windows.net/mycontainer/" $credentialName = "mycredential" $srvPath = "SQLSERVER:\SQL\COMPUTERNAME\INSTANCENAME" # for default instance, the $srvpath variable would be \"SQLSERVER:\SQL\COMPUTERNAME\DEFAULT\" # navigate to theSQL Server Instance CD $srvPath #Create a unique file name for the full database backup $backupFile = $backupUrlContainer + "AdventureWorks2012_" + (Get-Date).ToString("s").Replace("-","_").Replace(":", "_").Replace(" ","_").Replace("/", "_") + ".bak" #Backup Database to URL Backup-SqlDatabase -Database AdventureWorks2012 -backupFile $backupFile -SqlCredential $credentialName -CompressionOption On -BackupAction Database #Create a unique file name for log backup $backupFile = $backupUrlContainer + "AdventureWorks2012_" + (Get-Date).ToString("s").Replace("-","_").Replace(":", "_").Replace(" ","_").Replace("/", "_") + ".trn" #Backup Log to URL Backup-SqlDatabase -Database AdventureWorks2012 -backupFile $backupFile -SqlCredential $credentialName -CompressionOption On -BackupAction Log
Creating a full file backup of the primary filegroup
The following example creates a full file backup of the primary filegroup.
Tsql
--Back up the files in Primary: BACKUP DATABASE AdventureWorks2012 FILEGROUP = 'Primary' TO URL = 'https://mystorageaccount.blob.core.windows.net/mycontainer/AdventureWorks2012files.bck' WITH CREDENTIAL = 'mycredential' ,COMPRESSION; GO
C#
// Connect to default sql server instance on local machine Server server = new Server("."); string identity = "mystorageaccount"; string credentialName = "mycredential"; string dbName = "AdventureWorks2012"; string blobContainerName = "mycontainer"; // Generate Unique Url string url = String.Format(@"https://{0}.blob.core.windows.net/{1}/{2}-{3}.bck", identity, blobContainerName, dbName, DateTime.Now.ToString("s").Replace(":", "-")); // Backup to Url Backup backup = new Backup(); backup.CredentialName = credentialName; backup.Database = dbName; backup.Action = BackupActionType.Files; backup.DatabaseFileGroups.Add("PRIMARY"); backup.CompressionOption = BackupCompressionOptions.On; backup.Devices.AddDevice(url, DeviceType.Url); backup.SqlBackup(server);
PowerShell
#create variables $backupUrlContainer = "https://mystorageaccount.blob.core.windows.net/mycontainer/" $credentialName = "mycredential" $srvPath = "SQLSERVER:\SQL\COMPUTERNAME\INSTANCENAME" # for default instance, the $srvpath variable would be \"SQLSERVER:\SQL\COMPUTERNAME\DEFAULT\" # navigate to the SQL Server Instance CD $srvPath #Create a unique file name for the file backup $backupFile = $backupUrlContainer + "AdventureWorks2012_" + (Get-Date).ToString("s").Replace("-","_").Replace(":", "_").Replace(" ","_").Replace("/", "_") + ".bck" #Backup Primary File Group to URL Backup-SqlDatabase -Database AdventureWorks2012 -backupFile $backupFile -SqlCredential $credentialName -CompressionOption On -BackupAction Files -DatabaseFileGroup Primary
Creating a differential file backup of the primary filegroup
The following example creates a differential file backup of the primary filegroup.
Tsql
--Back up the files in Primary: BACKUP DATABASE AdventureWorks2012 FILEGROUP = 'Primary' TO URL = 'https://mystorageaccount.blob.core.windows.net/mycontainer/AdventureWorks2012filesdiff.bck' WITH CREDENTIAL = 'mycredential' ,COMPRESSION ,DIFFERENTIAL; GO
C#
// Connect to default sql server instance on local machine Server server = new Server("."); string identity = "mystorageaccount"; string credentialName = "mycredential"; string dbName = "AdventureWorks2012"; string blobContainerName = "mycontainer"; // Generate Unique Url string url = String.Format(@"https://{0}.blob.core.windows.net/{1}/{2}-{3}.bak", identity, blobContainerName, dbName, DateTime.Now.ToString("s").Replace(":", "-")); // Backup to Url Backup backup = new Backup(); backup.CredentialName = credentialName; backup.Database = dbName; backup.Action = BackupActionType.Files; backup.DatabaseFileGroups.Add("PRIMARY"); backup.Incremental = true; backup.CompressionOption = BackupCompressionOptions.On; backup.Devices.AddDevice(url, DeviceType.Url); backup.SqlBackup(server);
PowerShell
#create variables $backupUrlContainer = "https://mystorageaccount.blob.core.windows.net/mycontainer/" $credentialName = "mycredential" $srvPath = "SQLSERVER:\SQL\COMUTERNAME\INSTANCENAME" # for default instance, the $srvpath variable would be \"SQLSERVER:\SQL\COMPUTERNAME\DEFAULT\" # navigate to SQL Server Instance CD $srvPath #create a unique file name for the full backup $backupdbFile = $backupUrlContainer + "AdventureWorks2012_" + (Get-Date).ToString("s").Replace("-","_").Replace(":", "_").Replace(" ","_").Replace("/", "_") + ".bak" #Create a differential backup of the primary filegroup Backup-SqlDatabase -Database AdventureWorks2012 -backupFile $backupFile -SqlCredential $credentialName -CompressionOption On -BackupAction Files -DatabaseFileGroup Primary -Incremental
Restore a database and move files
To restore a full database backup and move the restored database to C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data directory, use the following steps.
Tsql
-- Backup the tail of the log first DECLARE @Log_Filename AS VARCHAR (300); SET @Log_Filename = 'https://mystorageaccount.blob.core.windows.net/mycontainer/AdventureWorks2012_Log_'+ REPLACE (REPLACE (REPLACE (CONVERT (VARCHAR (40), GETDATE (), 120), '-','_'),':', '_'),' ', '_') + '.trn'; BACKUP LOG AdventureWorks2012 TO URL = @Log_Filename WITH CREDENTIAL = 'mycredential' ,NORECOVERY; GO RESTORE DATABASE AdventureWorks2012 FROM URL = 'https://mystorageaccount.blob.core.windows.net/mycontainer/AdventureWorks2012.bak' WITH CREDENTIAL = 'mycredential' ,MOVE 'AdventureWorks2012_data' to 'C:\Program Files\Microsoft SQL Server\myinstance\MSSQL\DATA\AdventureWorks2012.mdf' ,MOVE 'AdventureWorks2012_log' to 'C:\Program Files\Microsoft SQL Server\myinstance\MSSQL\DATA\AdventureWorks2012.ldf' ,STATS = 5
C#
// Connect to default sql server instance on local machine Server server = new Server("."); string identity = "mystorageaccount"; string credentialName = "mycredential"; string dbName = "AdventureWorks2012"; string blobContainerName = "mycontainer"; // Generate Unique Url string urlBackupData = String.Format(@"https://{0}.blob.core.windows.net/{1}/{2}-Data{3}.bak", identity, blobContainerName, dbName, DateTime.Now.ToString("s").Replace(":", "-")); // Backup to Url Backup backup = new Backup(); backup.CredentialName = credentialName; backup.Database = dbName; backup.Devices.AddDevice(urlBackupData, DeviceType.Url); backup.SqlBackup(server); // Generate Unique Url for tail log backup string urlTailLogBackup = String.Format(@"https://{0}.blob.core.windows.net/{1}/{2}-TailLog{3}.bak", identity, blobContainerName, dbName, DateTime.Now.ToString("s").Replace(":", "-")); // Backup Tail Log to Url Backup backupTailLog = new Backup(); backupTailLog.CredentialName = credentialName; backupTailLog.Database = dbName; backupTailLog.Action = BackupActionType.Log; backupTailLog.NoRecovery = true; backupTailLog.Devices.AddDevice(urlTailLogBackup, DeviceType.Url); backupTailLog.SqlBackup(server); // Restore a database and move files string newDataFilePath = server.MasterDBLogPath + @"\" + dbName + DateTime.Now.ToString("s").Replace(":", "-") + ".mdf"; string newLogFilePath = server.MasterDBLogPath + @"\" + dbName + DateTime.Now.ToString("s").Replace(":", "-") + ".ldf"; Restore restore = new Restore(); restore.CredentialName = credentialName; restore.Database = dbName; restore.ReplaceDatabase = true; restore.Devices.AddDevice(urlBackupData, DeviceType.Url); restore.RelocateFiles.Add(new RelocateFile(dbName, newDataFilePath)); restore.RelocateFiles.Add(new RelocateFile(dbName+ "_Log", newLogFilePath)); restore.SqlRestore(server);
PowerShell
#create variables $backupUrlContainer = "https://mystorageaccount.blob.core.windows.net/mycontainer/" $credentialName = "mycredential" $srvPath = "SQLSERVER:\SQL\COMPUTERNAME\INSTNACENAME" # for default instance, the $srvpath variable would be \"SQLSERVER:\SQL\COMPUTERNAME\DEFAULT\" # navigate to SQL Server Instance CD $srvPath #create a unique file name for the full backup $backupdbFile = $backupUrlContainer + "AdventureWorks2012_" + (Get-Date).ToString("s").Replace("-","_").Replace(":", "_").Replace(" ","_").Replace("/", "_") + ".bak" # Full database backup to URL Backup-SqlDatabase -Database AdventureWorks2012 -backupFile $backupdbFile -SqlCredential $credentialName -CompressionOption On #Create a unique file name for the tail log backup $backuplogFile = $backupUrlContainer + "AdventureWorks2012_" + (Get-Date).ToString("s").Replace("-","_").Replace(":", "_").Replace(" ","_").Replace("/", "_") + ".trn" #Backup tail log to URL Backup-SqlDatabase -Database AdventureWorks2012 -backupFile $backupFile -SqlCredential $credentialName -BackupAction Log -NoRecovery # Restore Database and move files $newDataFilePath = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile ("AdventureWorks_Data","C:\Program Files\Microsoft SQL Server\myinstance\MSSQL\DATA\AdventureWorks2012.mdf") $newLogFilePath = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("AdventureWorks_Log","C:\Program Files\Microsoft SQL Server\myinstance\MSSQL\DATA\AdventureWorks2012.ldf") Restore-SqlDatabase -Database AdventureWorks2012 -SqlCredential $credentialName -BackupFile $backupdbFile -RelocateFile @($newDataFilePath,$newLogFilePath)
Restoring to a point-in-time using STOPAT
The following example restores a database to its state to a point in time, and shows a restore operation.
Tsql
RESTORE DATABASE AdventureWorks FROM URL = 'https://mystorageaccount.blob.core.windows.net/mycontainer/AdventureWorks2012.bak' WITH CREDENTIAL = 'mycredential' ,MOVE 'AdventureWorks2012_data' to 'C:\Program Files\Microsoft SQL Server\myinstance\MSSQL\DATA\AdventureWorks2012.mdf' ,Move 'AdventureWorks2012_log' to 'C:\Program Files\Microsoft SQL Server\myinstance\MSSQL\DATA\AdventureWorks2012.ldf' ,NORECOVERY --,REPLACE ,STATS = 5; GO RESTORE LOG AdventureWorks FROM URL = 'https://mystorageaccount.blob.core.windows.net/mycontainer/AdventureWorks2012.trn' WITH CREDENTIAL = 'mycredential' ,RECOVERY ,STOPAT = 'Oct 23, 2012 5:00 PM' GO
C#
// Connect to default sql server instance on local machine Server server = new Server("."); string identity = "mystorageaccount"; string credentialName = "mycredential"; string dbName = "AdventureWorks2012"; string blobContainerName = "mycontainer"; // Generate Unique Url string urlBackupData = String.Format(@"https://{0}.blob.core.windows.net/{1}/{2}-Data{3}.bak", identity, blobContainerName, dbName, DateTime.Now.ToString("s").Replace(":", "-")); // Backup to Url Backup backup = new Backup(); backup.CredentialName = credentialName; backup.Database = dbName; backup.Devices.AddDevice(urlBackupData, DeviceType.Url); backup.SqlBackup(server); // Generate Unique Url for Tail Log backup string urlTailLogBackup = String.Format(@"https://{0}.blob.core.windows.net/{1}/{2}-TailLog{3}.bak", identity, blobContainerName, dbName, DateTime.Now.ToString("s").Replace(":", "-")); // Backup Tail Log to Url Backup backupTailLog = new Backup(); backupTailLog.CredentialName = credentialName; backupTailLog.Database = dbName; backupTailLog.Action = BackupActionType.Log; backupTailLog.NoRecovery = true; backupTailLog.Devices.AddDevice(urlTailLogBackup, DeviceType.Url); backupTailLog.SqlBackup(server); // Restore a database and move files string newDataFilePath = server.MasterDBLogPath + @"\" + dbName + DateTime.Now.ToString("s").Replace(":", "-") + ".mdf"; string newLogFilePath = server.MasterDBLogPath + @"\" + dbName + DateTime.Now.ToString("s").Replace(":", "-") + ".ldf"; Restore restore = new Restore(); restore.CredentialName = credentialName; restore.Database = dbName; restore.ReplaceDatabase = true; restore.NoRecovery = true; restore.Devices.AddDevice(urlBackupData, DeviceType.Url); restore.RelocateFiles.Add(new RelocateFile(dbName, newDataFilePath)); restore.RelocateFiles.Add(new RelocateFile(dbName + "_Log", newLogFilePath)); restore.SqlRestore(server); // Restore transaction Log with stop at Restore restoreLog = new Restore(); restoreLog.CredentialName = credentialName; restoreLog.Database = dbName; restoreLog.Action = RestoreActionType.Log; restoreLog.Devices.AddDevice(urlBackupData, DeviceType.Url); restoreLog.ToPointInTime = DateTime.Now.ToString(); restoreLog.SqlRestore(server);
PowerShell
#create variables $backupUrlContainer = "https://mystorageaccount.blob.core.windows.net/mycontainer/" $credentialName = "mycredential" $srvPath = "SQLSERVER:\SQL\COMPUTERNAME\INSTANCENAME" # for default instance, the $srvpath variable would be \"SQLSERVER:\SQL\COMPUTERNAME\DEFAULT\" # Navigate to SQL Server Instance Directory CD $srvPath #create a unique file name for the full backup $backupdbFile = $backupUrlContainer + "AdventureWorks2012_" + (Get-Date).ToString("s").Replace("-","_").Replace(":", "_").Replace(" ","_").Replace("/", "_") + ".bak" # Full database backup to URL Backup-SqlDatabase -Database AdventureWorks2012 -backupFile $backupdbFile -SqlCredential $credentialName -CompressionOption On #Create a unique file name for the tail log backup $backuplogFile = $backupUrlContainer + "AdventureWorks2012_" + (Get-Date).ToString("s").Replace("-","_").Replace(":", "_").Replace(" ","_").Replace("/", "_") + ".trn" #Backup tail log to URL Backup-SqlDatabase -Database AdventureWorks2012 -backupFile $backupFile -SqlCredential $credentialName -BackupAction Log -NoRecovery # Restore Database and move files $newDataFilePath = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile ("AdventureWorks_Data","C:\Program Files\Microsoft SQL Server\myinstance\MSSQL\DATA\AdventureWorks2012.mdf") $newLogFilePath = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("AdventureWorks_Log","C:\Program Files\Microsoft SQL Server\myinstance\MSSQL\DATA\AdventureWorks2012.ldf") Restore-SqlDatabase -Database AdventureWorks2012 -SqlCredential $credentialName -BackupFile $backupdbFile -RelocateFile @($newDataFilePath,$newLogFilePath) -NoRecovery # Restore Transaction log with Stop At: Restore-SqlDatabase -Database AdventureWorks2012 -SqlCredential $credentialName -BackupFile $backuplogFile -ToPointInTime (Get-Date).ToString()