托管备份可自动根据数据库的运行情况决定备份策略，只需要设置保存期限，即可获得在保存期限内不超过两个小时时间点的备份保护。

备份

先决条件：

  1. SQL Server Agent应启动运行
  2. 创建备份所使用的azure storage账户
  3. 需要托管备份的数据库日志是 完整 或 大容量日志，不能是 简单。

步骤

  1. 创建凭据，使用azure storage账户的信息：
1
2
3
4
5

IF NOT EXISTS
(SELECT * FROM sys.credentials
WHERE credential_identity = 'mycredential')
CREATE CREDENTIAL  mycredential  WITH IDENTITY = 'mystorageaccount'
,SECRET = '<storage access key> ;
  1. 开启备份：
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

Use msdb;
GO
EXEC smart_admin.sp_set_db_backup
                @database_name='TestDB'
                ,@retention_days=30
                ,@credential_name='MyCredential'
                ,@encryption_algorithm ='AES_128'
                ,@encryptor_type= 'Certificate'
                ,@encryptor_name='MyBackupCert'
                ,@enable_backup=1;
GO
  1. 检查数据库的备份状态:
1
2
3

Use msdb
GO
SELECT * FROM smart_admin.fn_backup_db_config('NewDB')

还原

1
2
3
4
5

RESTORE DATABASE AdventureWorks2012
FROM URL = 'https://mystorageaccount.blob.core.windows.net/privatecontainertest/AdventureWorks2012.bak'
WITH CREDENTIAL = 'mycredential';
, STATS = 5 – use this to see monitor the progress
GO

监视状态

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

USE msdb
GO
SELECT
database_name AS [Database Name]
,backup_path AS [Backup Destination and File]
,[Backup Type] =
CASE backup_type
WHEN 1 THEN 'FULL'
WHEN 2 THEN 'LOG'
END
,[Backup Status] =
CASE [status]
WHEN 'A' THEN 'Available'
WHEN 'B' THEN 'Copy In Progress'
WHEN 'C' THEN 'Corrupted'
WHEN 'D' THEN 'Deleted'
WHEN 'F' THEN 'Copy Failed'
WHEN 'U' THEN 'Unknown'
END
,first_lsn AS [First LSN]
,last_lsn AS [Last LSN]
,backup_start_date AS [Backup Start Time]
,backup_finish_date AS [Backup Completion Time]
,expiration_date AS [Backup Expiry Date/Time]
FROM
smart_backup_files;

参考

SQL Server 托管备份到 Windows Azure http://msdn.microsoft.com/zh-cn/library/dn449496.aspx SQL Server 托管备份到 Windows Azure - 保持和存储设置 http://msdn.microsoft.com/zh-cn/library/dn449497.aspx#databasedisable smart_admin.set_db_backup http://msdn.microsoft.com/zh-cn/library/dn451013.aspx