There are two parts of the solution. First one is an automated backup script. It backups all databases of SQL Server using defined Azure credentials. There are only few options to configure. I've scheduled it as SQL Server Agent Job, to run every night.
It automatically detects what kind of backup should be done:
Simple Recovery databases
It makes full backup at start, then does differential backups until differential backup size reaches configured limit (50% of full backup size in my situation). After this, it makes another full backup and then again will do differentials.
Full Recovery databases
It makes full backup at start, then does log backups for defined number of days (3 in my situation). After this, it makes differential backup and then log backup next days. When differential backup size reaches configured limit (50% of full backup size in my situation) it will make full backup and cycle starts again.
The idea of this strategy
- Be able to recovery for unlimited period of time. Simple Recovery databases can be recovered to specified day, Full Recovery databases can be recovered to any point in time, even second.
- Use no more storage in Azure Blob as is needed to store the backups.As one of my databases reached 400Gb of size, making full backups too often would waste a lot of time and space.
- Make recovery as quick as it can be. In my configuration, every recovery can be done using maximum of five files (full+diff+three logs).
Backup test
I've also written automated database recovery test script.
Source code
/*
SQL Sever 2012 Azure Blob backup script
Author: 2013 Marek Malachowski, PricePanorama.com
*/
-- configuration lines
DECLARE @Diff2FullMaxRatio float=0.5; -- if bigger, it will make full backup instead of diff
DECLARE @maxLogBackupDays int=3; -- too many will make restore problematic
DECLARE @azureContainerPath varchar(259)='https://***.blob.core.windows.net/***/';
DECLARE @azureCredential varchar(20)='mycredential'
-- code
DECLARE @Backup_Filename AS VARCHAR (259); --sql server device name length limit
DECLARE @name VARCHAR(50), @recoveryMode varchar(20)
DECLARE db_cursor CURSOR FOR
SELECT name, recovery_model_desc
FROM sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb') AND name not like 'Test_%' -- exclude these databases
ORDER BY CASE name
WHEN 'Database1' THEN 1
WHEN 'Database2' THEN 2
ELSE 10
END
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name, @recoveryMode
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @FullSize bigint=0, @FullDate datetime, @FullUrl varchar(300)=null
DECLARE @DiffSize bigint=0, @DiffDate datetime, @DiffUrl varchar(300)=null
DECLARE @LogSize bigint=0, @LogDate datetime, @LogUrl varchar(300)=null
DECLARE @performFullBackup bit=0, @performLogBackup bit=0
-- Last full backup
SELECT TOP 1 @FullUrl=m.physical_device_name,@FullSize=s.backup_size,@FullDate=s.backup_start_date
FROM msdb.dbo.backupset s INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name=@name AND s.[type]='D' ORDER BY backup_start_date DESC
-- Last differential backup
SELECT TOP 1 @DiffUrl=m.physical_device_name,@DiffSize=s.backup_size,@DiffDate=s.backup_start_date
FROM msdb.dbo.backupset s INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name=@name AND s.[type]='I' ORDER BY backup_start_date DESC
-- Last transaction log backup
SELECT TOP 1 @LogUrl=m.physical_device_name,@LogSize=s.backup_size,@LogDate=s.backup_start_date
FROM msdb.dbo.backupset s INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name=@name AND s.[type]='L' ORDER BY backup_start_date DESC
IF @recoveryMode='SIMPLE'
BEGIN
-- Full backup only if first or when differential size was too big last time
IF @FullUrl IS NULL
SET @performFullBackup=1
ELSE
IF @DiffSize>(@FullSize*@Diff2FullMaxRatio) SET @performFullBackup=1;
END
ELSE -- recoveryMode='FULL'
BEGIN
-- Full backup if first
IF @FullUrl IS NULL
SET @performFullBackup=1
ELSE
-- Log backup only if not too many days after last Full or Diff backup
DECLARE @preLogDays int
SELECT @preLogDays=datediff(d,MAX(backupDate),getdate()) FROM (VALUES(@FullDate),(@DiffDate)) AS AllDates(backupDate)
IF @preLogDays<@maxLogBackupDays
SET @performLogBackup=1
ELSE
BEGIN -- Diff log if last size wasn't too big
IF @DiffSize>(@FullSize*@Diff2FullMaxRatio) SET @performFullBackup=1;
END
END
IF @performFullBackup=1
BEGIN
SET @Backup_Filename = @azureContainerPath + @name + '_Full_'+ REPLACE (REPLACE (REPLACE (CONVERT (VARCHAR (40), GETDATE (), 120), '-',''),':', ''),' ', '_') + '.bak';
BACKUP DATABASE @name TO URL = @Backup_Filename WITH CREDENTIAL = @azureCredential,COMPRESSION,CHECKSUM;
END
ELSE
IF @performLogBackup=1
BEGIN
SET @Backup_Filename = @azureContainerPath + @name + '_Log_'+ REPLACE (REPLACE (REPLACE (CONVERT (VARCHAR (40), GETDATE (), 120), '-',''),':', ''),' ', '_') + '.bak';
BACKUP LOG @name TO URL = @Backup_Filename WITH CREDENTIAL = @azureCredential,COMPRESSION,CHECKSUM;
END
ELSE -- differential backup
BEGIN
SET @Backup_Filename = @azureContainerPath + @name + '_Diff_'+ REPLACE (REPLACE (REPLACE (CONVERT (VARCHAR (40), GETDATE (), 120), '-',''),':', ''),' ', '_') + '.bak';
BACKUP DATABASE @name TO URL = @Backup_Filename WITH CREDENTIAL = @azureCredential,COMPRESSION,DIFFERENTIAL,CHECKSUM;
END
FETCH NEXT FROM db_cursor INTO @name, @recoveryMode
END
CLOSE db_cursor
DEALLOCATE db_cursor