使用 Windows Azure Blob 存储服务进行 SQL Server 备份和还原

项目
06/25/2013

本主题介绍一些概念、注意事项和示例代码，它们对配置和将 SQL Server 备份写入 Windows Azure Blob 存储服务以及从该服务还原至关重要。它还总结了使用 Windows Azure Blob 服务存储 SQL Server 备份的好处。

在 SQL Server 2012 SP1 CU2 中发布的此功能允许 SQL Server 直接备份和还原到 Windows Azure Blob 服务。此功能可用于备份内部实例或运行托管环境（如 Windows Azure 虚拟机）的 SQL Server 实例上的 SQL Server 数据库。备份到云有很多好处，如可用性、无限地理复制的站点外存储以及易于将数据迁移到云和从云迁移。在此版本中，您可以通过使用 tsql 或 SMO 发出 BACKUP 或 RESTORE 语句。在此版本中无法使用 SQL Server Management Studio 备份或还原向导备份到 Windows Azure Blob 存储服务或从该服务还原。

在 SQL Server 2012 SP1 CU4 中，支持使用 PowerShell 备份到 Windows Azure Blob 存储服务或从该服务还原。要请求此更新，请参阅这篇知识库文章。此主题包括示例章节中的 PowerShell 脚本。有关管理多个备份的示例 PowerShell 脚本，请参阅Use PowerShell to Backup Multiple Databases to Windows Azure Blob Storage Service。

将 Windows Azure Blob 服务用于 SQL Server 备份的好处

灵活、可靠、无限制的站点外存储：在 Windows Azure Blob 服务上存储您的备份是一种方便、灵活、易于访问的站点外备选方法。为您的 SQL Server 备份创建站点外存储就像修改您的现有脚本/作业一样简单。站点外存储位置通常应远离生产数据库位置，以防止出现同时影响站点外和生产数据库位置的一个灾难。通过选择地理复制 Blob 存储区，您在发生可能影响整个区域的灾难时多了一层额外的保护。此外，备份副本随时随地可用，并可以轻松访问它们来执行还原。
备份存档：Windows Azure Blob 存储服务提供替代常用磁带方案的更好的方法来存档备份。磁带存储可能需要物理传输到站点外设施并采取措施来保护介质。在 Windows Azure Blob 存储区中存储您的备份可以提供一个即时、高度可用、耐久的存档方案。
无硬件管理开销：没有有关 Windows Azure 服务的硬件管理开销。 Windows Azure 服务管理硬件并支持地理复制以提供冗余和防止硬件故障。
当前对于在 Windows Azure 虚拟机中运行的 SQL Server 实例，可以通过创建附加的磁盘来备份到 Windows Azure Blob 存储服务。但是，对于可以附加到 Windows Azure 虚拟机的磁盘数有限制。限制值为：超大实例最多使用 16 个磁盘，较小的实例可使用的磁盘则更少。通过允许直接备份到 Windows Azure Blob 存储区，您可以绕过 16 个磁盘的限制。

此外，现在在 Windows Azure Blob 存储服务中存储的备份文件直接可用于内部 SQL Server 或在 Windows Azure 虚拟机中运行的另一 SQL Server，不需要进行数据库附加/分离或下载并附加 VHD。
成本优势：仅对使用的服务付费。可以作为经济合算的站点外备份存档方案。有关详细信息和链接，请参阅 Windows Azure 计费注意事项一节。

Windows Azure 计费注意事项：

了解 Windows Azure 存储成本使您能够预测在 Windows Azure 中创建和存储备份的成本。

Windows Azure 价格计算器可以帮助估算您的成本。

**存储：费用基于使用的空间并根据有刻度的标准和冗余级别来计算它。有关详细信息和最新信息，请参阅定价详细信息文章中的“数据管理”**一节。

**数据传输：**传输到 Windows Azure 的入站数据是免费的。出站传输要支付带宽使用费用，并根据有刻度的区域特定标准来计算费用。有关详细信息，请参阅“定价详细信息”文章中的数据传输一节。

要求、组件和概念

本节内容：

安全性
关键组件和概念简介
Windows Azure Blob 存储服务
SQL Server 组件
限制
对备份/还原语句的支持

安全性

以下是备份到 Windows Azure Blob 存储服务或从该服务还原时的安全注意事项和要求。

为 Windows Azure Blob 存储服务创建容器时，我们建议您将访问权限设置为**“私有”**。将访问权限设置为“私有”后，只允许可提供对 Windows Azure 帐户进行身份验证所需的信息的用户或帐户进行访问。

安全说明
SQL Server 要求在 SQL Server 凭据中存储 Windows Azure 帐户名称和访问密钥身份验证信息。在执行备份或还原操作时，此信息用于对 Windows Azure 帐户进行身份验证。

用于发出 BACKUP 或 RESTORE 命令的用户帐户应是具有 Alter any credential 权限的 db_backup operator 数据库角色。

安装必备组件

在 Azure 虚拟机上运行的 SQL Server：如果正在 Windows Azure 虚拟机上安装 SQL Server，请安装 SQL Server 2012 SP1 CU2 或更新现有实例。要从 Microsoft 客户支持请求获得更新，请参阅此文章。
内部 SQL Server：SQL Server 2012、SP1 CU2 或更高版本包含此功能。要从 Microsoft 客户支持请求获得更新，请参阅此文章。

关键组件和概念简介

以下两节介绍了 Windows Azure Blob 存储服务以及在备份到 Windows Azure Blob 存储服务或从中还原时使用的 SQL Server 组件。了解这些组件和它们之间的交互对于执行备份到 Windows Azure Blob 存储服务或从中还原很重要。

首先需要创建 Windows Azure 帐户。 SQL Server 使用 Windows Azure storage account name 和 access key 值来进行身份验证和对存储服务写入和读取 blob。 SQL Server 凭据存储此身份验证信息并在备份或还原操作期间使用它。有关创建存储帐户和执行简单还原的完整演练，请参阅将 Windows Azure 存储服务用于 SQL Server 备份和还原的教程。

将存储帐户映射到 sql 凭据

Windows Azure Blob 存储服务

**存储帐户：**存储帐户是所有存储服务的起始点。要访问 Windows Azure Blob 存储服务，请首先创建一个 Windows Azure 存储帐户。需要使用 storage account name 和 access key 属性来向 Windows Azure Blob 存储服务及其组件进行身份验证。

**容器：**一个容器提供一组 Blob，可以存储无限数目的 Blob。要将 SQL Server 备份写入 Windows Azure Blob 服务，您必须至少创建根容器。

**Blob：**任意类型和大小的文件。 Windows Azure Blob 存储服务中可存储两类 blob：块 blob 和页 blob。 SQL Server 备份将页 Blob 作为 Blob 类型。 Blob 采用以下 URL 格式寻址：https://<存储帐户>.blob.core.windows. net/<容器>/<blob>

Azure Blob 存储

有关 Windows Azure Blob 存储服务的详细信息，请参阅如何使用 Windows Azure Blob 存储服务

有关页 Blob 的详细信息，请参阅了解块 Blob 和页 Blob

SQL Server 组件

**URL：**URL 指定统一资源标识符 (URI) 来标识唯一备份文件。 URL 用于提供 SQL Server 备份文件的位置和名称。在此实现中，唯一有效的 URL 是指向 Windows Azure 存储帐户中页 Blob 的 URL。该 URL 必须指向实际 Blob，而不仅仅是容器。如果 Blob 不存在，则创建它。如果指定现有 Blob，BACKUP 将失败，除非指定了“WITH FORMAT”选项。

注意
如果您选择复制备份文件并将它上载到 Windows Azure Blob 存储服务，则将页 blob 用作您的存储选项。不支持从块 Blob 进行还原。从块 blob 类型 RESTORE 将出错并且失败。

以下是一个示例 URL 值：http[s]://ACCOUNTNAME.Blob.core.windows. net/<CONTAINER>/<FILENAME.bak>。 HTTPS 不是必需的，但建议这样做。

**凭据：**SQL Server 凭据是用于存储连接到 SQL Server 外部资源所需的身份验证信息的对象。在这里，SQL Server 备份和还原进程使用凭据对 Windows Azure Blob 存储服务进行身份验证。凭据存储着存储帐户的名称和存储帐户的 access key 值。创建凭据后，在发出 BACKUP/RESTORE 命令时必须在 WITH CREDENTIAL 选项中指定它。有关如何查看、复制或重新生成存储帐户 access keys 的详细信息，请参阅存储帐户访问密钥。

有关如何创建 SQL Server 凭据的分步说明，请参阅本主题后面的创建凭据示例。

有关凭据的一般信息，请参阅凭据

有关使用凭据的其他示例的信息，请参阅创建 SQL Server 代理的代理帐户。

限制

支持的最大备份大小为 1 TB。
在此实现中，您可以使用 TSQL 或 SMO 发出备份或还原语句。当前不支持使用 SQL Server Management Studio 备份或还原向导备份到 Windows Azure Blob 存储服务或从该服务还原。
不支持创建逻辑设备名称。因此，不支持使用 sp_dumpdevice 或通过 SQL Server Management Studio 将 URL 添加为备份设备。
不支持追加到现有备份 blob。只能使用 WITH FORMAT 选项覆盖到现有 Blob 的备份。

不支持在单个备份操作中备份到多个 blob。例如，下面的代码将返回错误：

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

不支持使用 BACKUP 指定块大小。
不支持指定 MAXTRANSFERSIZE。
不支持指定备份集选项 - RETAINDAYS 和 EXPIREDATE。
SQL Server 要求备份设备名称最多包含 259 个字符。对于用于指定以下 URL 所需的元素，BACKUP TO URL 占用 36 个字符：“https://.blob.core.windows. net//.bak”，将其余 223 个字符用于帐户、容器和 blob 名称总和。

对备份/还原语句的支持

备份/还原语句	是否支持	例外	注释
BACKUP	√	不支持 BLOCKSIZE 和 MAXTRANSFERSIZE。	要求指定 WITH CREDENTIAL
RESTORE	√		要求指定 WITH CREDENTIAL
RESTORE FILELISTONLY	√		要求指定 WITH CREDENTIAL
RESTORE HEADERONLY	√		要求指定 WITH CREDENTIAL
RESTORE LABELONLY	√		要求指定 WITH CREDENTIAL
RESTORE VERIFYONLY	√		要求指定 WITH CREDENTIAL
RESTORE REWINDONLY	−

有关备份语句的语法和一般信息，请参阅 BACKUP (Transact-SQL)。

有关还原语句的语法和一般信息，请参阅 RESTORE (Transact-SQL)。

对备份参数的支持

参数	是否支持	例外	注释
DATABASE	√
LOG	√

TO (URL)	√	与 DISK 和 TAPE 不同，URL 不支持指定或创建逻辑名称。	此参数用于指定备份文件的 URL 路径。
MIRROR TO	−
WITH 选项：
CREDENTIAL	√		仅当使用 BACKUP TO URL 选项备份到 Windows Azure Blob 存储服务时，才支持 WITH CREDENTIAL。
DIFFERENTIAL	√
COPY_ONLY	√
COMPRESSION\|NO_COMPRESSION	√
DESCRIPTION	√
NAME	√
EXPIREDATE \| RETAINDAYS	−
NOINIT \| INIT	−		如果使用，则忽略此选项。不能追加到 blob。要覆盖备份，请使用 FORMAT 参数。
NOSKIP \| SKIP	−
NOFORMAT \| FORMAT	√		如果使用，则忽略此选项。除非指定 WITH FORMAT，否则对现有 blob 的备份失败。指定 WITH FORMAT 时，覆盖现有 blob。
MEDIADESCRIPTION	√
MEDIANAME	√
BLOCKSIZE	−
BUFFERCOUNT	√
MAXTRANSFERSIZE	−
NO_CHECKSUM \| CHECKSUM	√
STOP_ON_ERROR \| CONTINUE_AFTER_ERROR	√
STATS	√
REWIND \| NOREWIND	−
UNLOAD \| NOUNLOAD	−
NORECOVERY \| STANDBY	√
NO_TRUNCATE	√

有关备份参数的详细信息，请参阅 BACKUP (Transact-SQL)。

对还原参数的支持

参数	是否支持	例外	注释
DATABASE	√
LOG	√
FROM (URL)	√		FROM URL 参数用于指定备份文件的 URL 路径。
WITH 选项：
CREDENTIAL	√		仅当使用 RESTORE FROM URL 选项从 Windows Azure Blob 存储服务还原时，才支持 WITH CREDENTIAL。
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	√

有关还原参数的详细信息，请参阅 RESTORE 参数 (Transact-SQL)。

示例

本节包含以下示例。

创建凭据
备份整个数据库
备份数据库和日志
创建主文件组的完整文件备份
创建主文件组的差异文件备份
还原数据库并移动文件
使用 STOPAT 还原到某个时间点

创建凭据

下面的示例创建存储 Windows Azure 存储身份验证信息的凭据。

Tsql

IF NOT EXISTS
(SELECT * FROM sys.credentials 
WHERE credential_identity = 'mycredential')
CREATE CREDENTIAL mycredential WITH IDENTITY = 'mystorageaccount'
,SECRET = '<storage access key>' ;

// 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

备份整个数据库

下面的示例将 AdventureWorks2012 数据库备份到 Windows Azure Blob 存储服务。

Tsql

BACKUP DATABASE AdventureWorks2012 
TO URL = 'https://mystorageaccount.blob.core.windows.net/mycontainer/AdventureWorks2012.bak' 
      WITH CREDENTIAL = 'mycredential' 
     ,COMPRESSION
     ,STATS = 5;
GO

// 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

备份数据库和日志

下面的示例备份 AdventureWorks2012 示例数据库，默认情况下，该数据库使用简单恢复模式。若要支持日志备份，请将 AdventureWorks2012 数据库改为使用完整恢复模式。然后，该示例对 Windows Azure Blob 创建完整数据库备份，并在一段更新活动过后备份日志。此示例将创建具有日期时间戳的备份文件名。

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

// 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

创建主文件组的完整文件备份

下面的示例创建主文件组的完整文件备份。

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

// 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

创建主文件组的差异文件备份

下面的示例创建主文件组的差异文件备份。

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

// 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

还原数据库并移动文件

要还原完整数据库备份并将还原的数据库移到 C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data 目录，请使用以下步骤。

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

// 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)

使用 STOPAT 还原到某个时间点

下面的示例将数据库状态还原到某个时间点并显示一个还原操作。

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

// 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()

使用 Windows Azure Blob 存储服务进行 SQL Server 备份和还原

将 Windows Azure Blob 服务用于 SQL Server 备份的好处

Windows Azure 计费注意事项：

要求、组件和概念

安全性

安装必备组件

关键组件和概念简介

Windows Azure Blob 存储服务

SQL Server 组件

限制

对备份/还原语句的支持

对备份参数的支持

对还原参数的支持

示例

创建凭据

备份整个数据库

备份数据库和日志

创建主文件组的完整文件备份

创建主文件组的差异文件备份

还原数据库并移动文件

使用 STOPAT 还原到某个时间点

其他资源