Transcript Restoring a SQL Server database from Azure Blob

Thank You!
Local PASS Community & Sponsors!
Restoring a SQL Server database
from Azure Blob Storage (200-level)
Randolph West
@rabryst
[email protected]
Meet The Contestants
Azure Blob Storage
Service Level Agreement
No Local Backups
Recovery Time Objective
Pop Quiz, Hotshot
What do you do? What do you do?
To The Cloud!
TO THE CLOUD!
Restore from Azure Blob Storage!
How Backups Work: A Refresher
 TechNet article by Paul Randal
 Understanding SQL Server Backups: https://goo.gl/OlSAEl
Paul Randal / TechNet Magazine
How Backups Work: A Refresher (2)
 Full Recovery Model
 Full Backup (transactionally-consistent point in time)
 Differential Backup (yes, affected by full backups)
 Transaction Log Backup (no, not affected by full backups)
 Supports point-in-time recovery.
How Backups Work: A Refresher (3)
 Full Recovery Model with Differential and Log Backups
Paul Randal / TechNet Magazine
How Backups Work: A Refresher (4)
 Bulk-Logged Model
 Does not support point-in-time recovery
 Simple Recovery Model
 Full Backup (transactionally-consistent point in time)
 Read more from Paul Randal: http://goo.gl/LeIQpv
SQL Server Backup Using Azure Blob Storage
 SQL Server 2012 SP1 Cumulative Update 2 and later
 Requires extra credentials
 Supported by Ola Hallengren’s Maintenance Solution
 The Curious Incident of the Infinite Lease in the Night Time.
Demo
 Set up Azure Blob Storage account and credentials
 SQL Server backup to Azure:
 built-in method
 Ola Hallengren’s method.
Demo
 Set up Azure Blob Storage account and credentials
 SQL Server backup to Azure:
 built-in method
 Ola Hallengren’s method.
Is there a better way?
 Back up any SQL Server database to Azure Blob Storage
 Download only the files needed to restore the latest backup
 All you need to know is the database name
 Gratis.
Leverages Ola Hallengren’s Maintenance Solution
 https://ola.hallengren.com/sql-server-backup.html
 Known Naming Convention:
 SERVER$INSTANCE_DB_TYPE_yyyyMMdd_HHmmSS.bak
 Used by thousands of DBAs all over the world
 No need to use built-in Azure backup method.
What About Other Backup Methods?
 Anything goes:
 strong naming convention, including a DB name and date and time
 Code your own:
 It’s free!
SQL Server Backup Using Blob Storage Redux
 Introducing the AzureBlobStorageSync tool
 Custom expiration date for backup files
 Uses naming convention from known backup solutions.
SQL Server Backup Using Blob Storage Redux (2)
 No file lease, easier maintenance
 Quickly parse tens of thousands of files
 DOES NOT NEED TO RUN ON THE DATABASE SERVER!
 Already being used in a production environment.
Demo
 SQL Server backup to Azure using AzureBlobStorageSync.
AzureBlobStorageSync Configuration File
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings configSource="connections.config" />
<appSettings>
<add key="Container" value="sqlbackup" />
<add key="DeleteFilesFromAzure" value="False" />
<add key="LocalPath" value="D:\SQLData\Backup" />
</appSettings>
</configuration>
AzureBlobStorageSync Connection Settings
<?xml version="1.0" encoding="utf-8"?>
<connectionStrings>
<clear/>
<add name="StorageConnectionString"
connectionString=
"DefaultEndpointsProtocol=https;AccountName=motorfinity
;AccountKey=<redacted>" />
</connectionStrings>
AzureBlobStorageSync Configuration File
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings configSource="connections.config"
/>
<appSettings>
<add key="Container" value="sqlbackup" />
<add key="DeleteFilesFromAzure" value="False" />
<add key="LocalPath" value="D:\SQLData\Backup" />
</appSettings>
</configuration>
AzureBlobStorageSync Sample Output
AzureBlobStorageSync version 1.0.0.0
Copyright (c) 2015 Randolph West. All rights reserved.
Fetching list of files on local machine ...
Fetching list of items in Azure Blob Storage ...
Starting file comparison ...
Uploading file [demo.bak]
Deleting file
[JUPITER\demo\FULL\JUPITER_demo_FULL_20140114_163153.bak]
That’s Cool, But What About Restoring?
 AzureBlobStorageRestore
 Downloads only the required files, and generates a SQL
restore script.
Demo
 Restore SQL Server backup using AzureBlobStorageRestore.
AzureBlobStorageRestore Configuration File
AzureBlobStorageRestore Sample Output
AzureBlobStorageRestore version 1.0.0.0
Copyright (c) 2015 Randolph West. All rights reserved.
Fetching list of items in Azure Storage ...
Number of items found in Azure Container: 10913
Starting file review ...
Downloading file [MF_MIB/FULL/WIN_MF_MIB_FULL_20150623_000016.bak]
Downloading file [MF_MIB/DIFF/WIN_MF_MIB_DIFF_20150623_070002.bak]
Downloading file [MF_MIB/LOG/WIN_MF_MIB_LOG_20150623_070003.bak]
Downloading file [MF_MIB/LOG/WIN_MF_MIB_LOG_20150623_071501.bak]
Downloading file [MF_MIB/LOG/WIN_MF_MIB_LOG_20150623_073004.bak]
Downloading file [MF_MIB/LOG/WIN_MF_MIB_LOG_20150623_074501.bak]
AzureBlobStorageRestore T-SQL Restore Script
-- Cloud Storage Restore Script, to restore
-- ABSR_MF_MIB. Generated on 2015-06-22 23:55:53
USE [master];
GO
IF DB_ID('[ABSR_MF_MIB]') IS NOT NULL
ALTER DATABASE [ABSR_MF_MIB]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
AzureBlobStorageRestore T-SQL Restore Script (2)
DECLARE @fullBackup NVARCHAR(MAX) =
N'D:\SQLData\Backup\MF_MIB\FULL\WINOS2G5MRT4UH_MF_MIB_FULL_20150623_000016.bak';
DECLARE @path NVARCHAR(255) = N'D:\Temp\_ABSR_';
DECLARE @template NVARCHAR(MAX) = N'
RESTORE DATABASE [ABSR_MF_MIB]
FROM DISK = N''D:\SQLData\Backup\MF_MIB\FULL\WINOS2G5MRT4UH_MF_MIB_FULL_20150623_000016.bak''
WITH {%%MOVE%%}
REPLACE,
NOUNLOAD,
NORECOVERY,
STATS = 5;';
AzureBlobStorageRestore T-SQL Restore Script (3)
DECLARE @FileListInfo TABLE (…);
DECLARE @sql NVARCHAR(MAX) = N'',
@i INT, @x INT = 1;
DECLARE @header NVARCHAR(MAX) = 'RESTORE
FILELISTONLY FROM DISK =
''D:\SQLData\Backup\MF_MIB\FULL\WINOS2G5MRT4UH_MF_MIB_FULL_20150623_000016.bak''';
INSERT INTO @FileListInfo EXEC (@header);
AzureBlobStorageRestore T-SQL Restore Script (4)
RESTORE DATABASE [ABSR_MF_MIB]
FROM DISK = N'D:\SQLData\Backup\MF_MIB\FULL\WINOS2G5MRT4UH_MF_MIB_FULL_20150623_000016.bak‘
WITH MOVE N'MF_MIB' TO
N'D:\Temp\_ABSR_MF_MIB.mdf',
MOVE N'MF_Audit' TO N'D:\Temp\_ABSR_MF_Audit.ndf',
MOVE N'MF_MIB_log' TO
N'D:\Temp\_ABSR_MF_MIB_log.ldf',
REPLACE,
NOUNLOAD,
NORECOVERY,
STATS = 5;
AzureBlobStorageRestore T-SQL Restore Script (5)
RESTORE DATABASE [ABSR_MF_MIB]
FROM DISK = N'D:\SQLData\Backup\MF_MIB\DIFF\WINOS2G5MRT4UH_MF_MIB_DIFF_20150623_070002.bak'
WITH FILE = 1,
NOUNLOAD,
REPLACE,
NORECOVERY,
STATS = 5;
GO
RESTORE LOG [ABSR_MF_MIB]
FROM DISK = N'D:\SQLData\Backup\MF_MIB\LOG\WINOS2G5MRT4UH_MF_MIB_LOG_20150623_070003.trn'
WITH FILE = 1,
NOUNLOAD,
REPLACE,
NORECOVERY,
STATS = 5;
GO
AzureBlobStorageRestore T-SQL Restore Script (6)
RESTORE DATABASE [ABSR_MF_MIB]
WITH RECOVERY;
GO
ALTER DATABASE [ABSR_MF_MIB]
SET MULTI_USER;
GO
DBCC CHECKDB ([ABSR_MF_MIB])
WITH ALL_ERRORMSGS,
NO_INFOMSGS,
DATA_PURITY;
GO
How Does It Work?
 Written in C# with standard Azure Storage DLL
 Requires .NET Framework 4.x
 DOES NOT NEED TO RUN ON THE DATABASE SERVER
 UNC path to backups, can run on any network-connected machine
 Test restores can be run independently.
Summary Of This Thing I Made
 Free!
 MIT License
 Available right now on GitHub:
 https://github.com/bornsql/azureblobstoragesync
Storage Wars
 Amazon S3 support
 Sync and Restore with the same ease as Azure Blob Storage
 Network support
 Sync and Restore with a network file share
 Introducing:
 CloudStorageSync
 CloudStorageRestore
 Now you can back up to all three at the same time:
 Geo-redundant, highly distributed database backups.
Call Me, Maybe
 Ask your questions
 Email: [email protected]
 Twitter: @rabryst
 Complete your evaluation form, please!
Images from freeimages.com, unsplash.com and technet.microsoft.com