Programmatically backup Azure Sql db

Question

I've created a script to backup and restore my sql database. I recently just moved to Azure Sql Server. My backup strategy below no longer works. For my program, it would typically save a backup and create a new one with a suffix called training. It seems Azure Sql Server backup strategy is completely different. May I ask how to programmatically backup Azure Sql db's and restore it.

if (!sqlServer.Databases.Contains(databaseTraining))
{
var backupFile = string.Concat(sqlServer.Settings.BackupDirectory, "\\", database, "Training.BAK");

var sourceBackup = new Backup
{
    Database = database,
    Action = BackupActionType.Database
};

var destDevice = new BackupDeviceItem(backupFile, DeviceType.File);
sourceBackup.Devices.Add(destDevice);
sourceBackup.Initialize = true;

sourceBackup.SqlBackup(sqlServer);

var db = new Microsoft.SqlServer.Management.Smo.Database(sqlServer, databaseTraining);
db.Create();

var destinationRestore = new Restore
{
    Database = databaseTraining,
    Action = RestoreActionType.Database
};
destinationRestore.Devices.Add(destDevice);

var dataFile = new RelocateFile
{
    LogicalFileName = destinationRestore.ReadFileList(sqlServer).Rows[0][0].ToString(),
    PhysicalFileName = sqlServer.Databases[databaseTraining].FileGroups[0].Files[0].FileName
};

var logFile = new RelocateFile
{
    LogicalFileName = destinationRestore.ReadFileList(sqlServer).Rows[1][0].ToString(),
    PhysicalFileName = sqlServer.Databases[databaseTraining].LogFiles[0].FileName
};

destinationRestore.RelocateFiles.Add(dataFile);
destinationRestore.RelocateFiles.Add(logFile);

destinationRestore.ReplaceDatabase = true;
destinationRestore.NoRecovery = false;

destinationRestore.SqlRestore(sqlServer);

It is different: MS do backups not you. The [`BACKUP`](https://msdn.microsoft.com/en-us/library/ms186865.aspx?f=255&MSPPError=-2147217396) command is not available on Azure SQL Server. — Richard, Sep 30 '16 at 14:06
For SQL Azure, you might want to look at professional back up services such as [CloudBerry](http://www.cloudberrylab.com/), [RedGate's CherrySafe](https://www.cherrysafe.com/). — Win, Sep 30 '16 at 14:09

score 0 · Answer 1 · edited May 23 '17 at 10:34

I recently just moved to Azure Sql Server. My backup strategy below no longer works

what you observed is correct,your backup strategy won't work in Azure..

If backup's are just needed to ensure point in time restore like OnPremises,you don't need that,since using azure you can get point in time restore capabilties based on your tier..

If you are looking to restore database on local server for testing/other purposes..you can use below links..

1.)How do I copy SQL Azure database to my local development server?

2.)https://blogs.msdn.microsoft.com/mast/2013/03/03/different-ways-to-backup-your-windows-azure-sql-database/

Programmatically backup Azure Sql db

1 Answers1