Backup your NAV Database to Microsoft Azure

I am not sure that you will find safer place to place your SQL backups than on a Microsoft Azure Storage. Azure keeps three copies of all the data within the data center and if you enable Geo-Replication another three copies will be kept in another data center somewhere else in the world.

Running the NAV SQL Server on a virtual machine within Azure also means that the SQL database is also in six places, just to make sure.

I moved my NAV services to Azure and wanted to make sure that the backup would be as good as everything else. To backup directly from SQL to Azure Storage you will need SQL Server 2012. There are most likely other third-party solutions to move you backups to Azure. There even is a program from Microsoft – SQL Server Backup to Windows Azure Tool – that will pick up files a local folder(s) and upload them to the Azure Storage. I am using this tool to upload backup from my MySql server.

If you have SQL Server 2012, on Azure or on premise you can backup directly to your storage account. You will need Cumulative update package 2 for SQL Server 2012 to update your installation to the required version to get this feature supported.

How it works is being shared in many places, for example on SQLServerCentral.com. I followed these instructions and created a storage account in USA. My SQL server is running in North Europe and I wanted to make sure that the backups where on another continent.

I created the credentials in my master database and then started to test the backup. After a succesful backup I started to think about automating this process. As usual a dynamic script was needed, I did not want to have to manually do anything. Even if I removed or added a database. This is the script

[code lang=”sql”]declare @yea varchar(10)
declare @wk varchar(10)
declare @dw varchar(10)
declare @hh varchar(10)
declare @url varchar(250)
SET @yea = (SELECT datepart(yy,Getdate()));
SET @wk = (SELECT datepart(wk,GetDate()));
SET @dw = (SELECT datepart(dw,GetDate()));
SET @hh = (SELECT datepart(hh,GetDate()));

DECLARE @TableName VARCHAR(256)
DECLARE @DBName VARCHAR(256)
DECLARE @varSQL VARCHAR(max)
DECLARE @getDBName CURSOR
SET @TableName = ‘$ndo$dbproperty’
SET @getDBName = CURSOR FOR SELECT name FROM sys.databases
CREATE TABLE #TmpTable (DBName VARCHAR(256), SchemaName VARCHAR(256), TableName VARCHAR(256))
OPEN @getDBName
FETCH NEXT FROM @getDBName INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @varSQL = ‘USE [‘ + @DBName + ‘];
INSERT INTO #TmpTable SELECT ”’+ @DBName + ”’ AS DBName, SCHEMA_NAME(schema_id) AS SchemaName, name AS TableName FROM sys.tables
WHERE name LIKE ”%’ + ltrim(rtrim(@TableName)) + ‘%”’
EXEC (@varSQL)
FETCH NEXT
FROM @getDBName INTO @DBName
END
CLOSE @getDBName
DEALLOCATE @getDBName

You can see that my company name, Kappi, is in the script. If you want to use this script you need to update the storage URL and the credential name.

What happens is that all databases are read and within them the script looks for the table ‘$ndo$dbproperty’. If this table exists then I add this database name to a temporary table. I then loop through the temporary table and start a backup for each database.

My plan is to do a full backup once a week and transaction backup every six hours. The line ‘IF @dw= 1 AND @hh < 6′ triggers a full backup on Sundays before six in the morning. Then I created a job that executes every six hours to start the backup.

[code lang=”sql”]USE [msdb]
GO

/****** Object: Job [Kappi_Backup] Script Date: 7.12.2013 14:16:04 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [Database Maintenance] Script Date: 7.12.2013 14:16:04 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N’Database Maintenance’ AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N’Database Maintenance’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N’Kappi_Backup’,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=3,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N’Database Backup’,
@category_name=N’Database Maintenance’,
@owner_login_name=N’ONAZURE\kappi’,
@notify_email_operator_name=N’Gunnar Þór Gestsson’, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Start Backup] Script Date: 7.12.2013 14:16:05 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Start Backup’,
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N’TSQL’,
@command=N’declare @yea varchar(10)
declare @wk varchar(10)
declare @dw varchar(10)
declare @hh varchar(10)
declare @url varchar(250)
SET @yea = (SELECT datepart(yy,Getdate()));
SET @wk = (SELECT datepart(wk,GetDate()));
SET @dw = (SELECT datepart(dw,GetDate()));
SET @hh = (SELECT datepart(hh,GetDate()));

DECLARE @TableName VARCHAR(256)
DECLARE @DBName VARCHAR(256)
DECLARE @varSQL VARCHAR(max)
DECLARE @getDBName CURSOR
SET @TableName = ”$ndo$dbproperty”
SET @getDBName = CURSOR FOR SELECT name FROM sys.databases
CREATE TABLE #TmpTable (DBName VARCHAR(256), SchemaName VARCHAR(256), TableName VARCHAR(256))
OPEN @getDBName
FETCH NEXT FROM @getDBName INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @varSQL = ”USE [” + @DBName + ”];
INSERT INTO #TmpTable SELECT ”””+ @DBName + ””” AS DBName, SCHEMA_NAME(schema_id) AS SchemaName, name AS TableName FROM sys.tables
WHERE name LIKE ””%” + ltrim(rtrim(@TableName)) + ”%”””
EXEC (@varSQL)
FETCH NEXT
FROM @getDBName INTO @DBName
END
CLOSE @getDBName
DEALLOCATE @getDBName

DECLARE @DBName_Cursor CURSOR
SET @DBName_Cursor = CURSOR FOR SELECT DBName FROM #TmpTable;
OPEN @DBName_Cursor
FETCH NEXT FROM @DBName_Cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
IF @dw = 1 AND @hh < 6
BEGIN
SET @url = ”https://kappisqlbackup.blob.core.windows.net/sqlbackup/” + REPLACE(@dbname,” ”,”_”) + ”_” + @yea + ”_” + @wk + ”.bak”
BACKUP DATABASE @dbname TO
URL=@url
WITH CREDENTIAL=”kappiAzureCredential”, STATS = 10, FORMAT, COMPRESSION
END
SET @url = ”https://kappisqlbackup.blob.core.windows.net/sqlbackup/” + REPLACE(@dbname,” ”,”_”) + ”_” + @yea + ”_” + @wk + ”_” + @dw + ”_” + @hh + ”.trn”
BACKUP LOG @dbname TO
URL=@url
WITH CREDENTIAL=”kappiAzureCredential”, STATS = 10, NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION
FETCH NEXT FROM @DBName_Cursor INTO @dbname
END;
CLOSE @DBName_Cursor;
DEALLOCATE @DBName_Cursor;
DROP TABLE #TmpTable
GO
‘,
@database_name=N’master’,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N’Every Six Hours’,
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=8,
@freq_subday_interval=6,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20131205,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N’dce606ac-0803-43a2-a98a-c8ffedb1df09′
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

[/code]

Leave a Reply Cancel reply