Microsoft Azure Blob Storage is a scalable, online cloud data storage service that stores data blobs in containers.
This article helps you understand how to write backups to and restore from the Azure Blob Storage Service and to do so we will create an Azure Blob Container, credentials for accessing the storage account, writing a backup to the blob service, and then performing a simple restore.The backup file will be stored in .bak format.
Add following namespace to work with Azure Storage and Sql:
1.using Microsoft.Azure;
2.using Microsoft.WindowsAzure.Storage;
3.using Microsoft.WindowsAzure.Storage.Blob;
4.using System.Data;
5.using System.Data.SqlClient;
Create connection in web config:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
<appSettings> <add key="webpages:Version" value="3.0.0.0" /> <add key="webpages:Enabled" value="false" /> <add key="ClientValidationEnabled" value="true" /> <add key="ConnectionString" value="Data Source=DESKTOP-****\SQLEXPRESS2016;initial catalog=***;user id=sa;password=****;MultipleActiveResultSets=True;" /> <add key="UnobtrusiveJavaScriptEnabled" value="true" /> <add key="Secret" value="****" /> <add key="Container" value="mytestcontainer" /> <add key="AccountName" value="hr67zeymbbyjksawinvm" /> <add key="BlobUrl" value="https://hr67zeymbbyjksawinvm.blob.core.windows.net/mytestcontainer" /> <add key="CredentialName" value="SampleCred" /> <add key="StorageConnectionString" value="DefaultEndpointsProtocol=https;AccountName=hr67zeymbbyjksawinvm;AccountKey=*****;EndpointSuffix=core.windows.net" /> <add key="FolderName" value="DatabaseBackup"/> <add key="SqlLocation" value="C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS2016\MSSQL\DATA\" /> </appSettings> |
Backup:
To take backup on Blob storage follow following steps:
1. Create credential with blob account name and secret.
2. Set database to full recovery.
3. Take database backup in the blob with credentials.
|
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 27 28 29 30 31 32 33 34 35 36 37 38 39 |
public void TakeDatabaseBackup(string secret, string containerName, string accountName, string credentialName, string databaseName, string url) { SqlCommand sqlCommand = new SqlCommand(); SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString); sqlConnection.Open(); string makeCredentialToAzure = string.Format("IF NOT EXISTS (SELECT * FROM sys.credentials WHERE NAME = '" + credentialName + "') CREATE CREDENTIAL [" + credentialName + "] WITH IDENTITY = '" + accountName + "', SECRET ='" + secret + "' "); sqlCommand = new SqlCommand(makeCredentialToAzure, sqlConnection); sqlCommand.CommandTimeout = 25200; sqlCommand.ExecuteNonQuery(); string alterDatabase = string.Format("ALTER DATABASE " + databaseName + " SET RECOVERY FULL"); sqlCommand = new SqlCommand(alterDatabase, sqlConnection); sqlCommand.CommandTimeout = 25200; sqlCommand.ExecuteNonQuery(); string backupDatabase = string.Format("BACKUP DATABASE " + databaseName + " TO URL = N'" + url + "' WITH CREDENTIAL = '" + credentialName + "'"); sqlCommand = new SqlCommand(backupDatabase, sqlConnection); sqlCommand.CommandTimeout = 25200; sqlCommand.ExecuteNonQuery(); sqlCommand.Dispose(); sqlConnection.Close(); sqlConnection.Dispose(); } public ActionResult TakeBackup() { // creates new file name everytime string fileName = "Test" + "-" + DateTime.Now.Day + DateTime.Now.Month + DateTime.Now.Year + "-" + DateTime.Now.Hour + DateTime.Now.Minute + DateTime.Now.Second + ".bak"; string secret = ConfigurationManager.AppSettings.Get("Secret").ToString(); string containerName = ConfigurationManager.AppSettings.Get("Container").ToString(); string accountName = ConfigurationManager.AppSettings.Get("AccountName").ToString(); string url = ConfigurationManager.AppSettings.Get("BlobUrl").ToString() + "/" + fileName + ""; string credentialName = ConfigurationManager.AppSettings.Get("CredentialName").ToString(); TakeDatabaseBackup(secret, containerName, accountName, credentialName, "DatabaseName", url); return View(); } |
Restore:
To restore the .bak file stored on blob, follow following steps:
1.Download .bak file from azure blob storage to local machine.
2.Get logical name of the file to be restored.
3.Restore the .bak file on database.
|
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 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 |
public ActionResult Restore(fileName) { //Get the file from Blob and store on local DownloadFileFromBlob(fileName); var fileToBeRestored = Server.MapPath("FolderName").ToString() +"\\" + fileName; var PreviousDb = Path.GetFileName(fileToBeRestored).Split('-'); //Get databaseName from the file name var sourceDatabase = PreviousDb[0]; var destinationDatabase = "NewDatabaseName"; List logicalNames = GetLogicalName(sourceDatabase); var restoreAsMdf = ConfigurationManager.AppSettings["SqlLocation"].ToString() + destinationDatabase + ".mdf"; var restoreAsLdf = ConfigurationManager.AppSettings["SqlLocation"].ToString() + destinationDatabase + "_log.ldf"; var logicalName = logicalNames[0]; var logicalName_log =logicalNames[1]; RestoreBackup(destinationDatabase, fileToBeRestored, logicalName, logicalName_log, restoreAsMdf, restoreAsLdf); } public void DownloadFileFromBlob(fileName) { var storageAccount = CloudStorageAccount.Parse(ConfigurationManager.AppSettings.Get("StorageConnectionString").ToString()); var blobClient = storageAccount.CreateCloudBlobClient(); // Get Blob Container var container = blobClient.GetContainerReference(ConfigurationManager.AppSettings.Get("Container").ToString()); // Get reference to blob (binary content) var pageBlob = container.GetPageBlobReference(fileName); string directoryPath = Server.MapPath(ConfigurationManager.AppSettings.Get(ConfigurationManager.AppSettings.Get("FolderName").ToString()).ToString(); string filePath = directoryPath + "\\" + fileName; if (!Directory.Exists(directoryPath)) { Directory.CreateDirectory(directoryPath); } blockBlob.DownloadToFile(filePath, FileMode.OpenOrCreate); } public List<string> GetLogicalName(string sourceDatabase) { List<string> logicalNames = new List<string>(); SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString); sqlConnection.Open(); var dataSet = new DataSet(); SqlCommand sqlCommandGetLogicalName = new SqlCommand("SELECT f.name LogicalName FROM sys.master_files f INNER JOIN sys.databases d ON d.database_id = f.database_id where d.name = '" + sourceDatabase + "'", sqlConnection); SqlDataReader reader = sqlCommandGetLogicalName.ExecuteReader(); while (reader.Read()) { logicalNames.Add(reader["LogicalName"].ToString()); } return logicalNames; } public void RestoreBackup(string destinationDatabase, string fileToBeRestored, string logicalName, string logicalName_log, string restoreAsMdf, string restoreAsLdf) { SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString); sqlConnection.Open(); string restore = string.Format("RESTORE DATABASE [" + destinationDatabase + "] " + "FROM DISK ='" + fileToBeRestored + "' WITH RECOVERY," + "MOVE '" + logicalName + "' TO '" + restoreAsMdf + "' ,MOVE '" + logicalName_log + "'TO '" + restoreAsLdf + "'"); SqlCommand sqlCommandRestore = new SqlCommand(restore, sqlConnection); sqlCommandRestore.CommandTimeout = 25200; sqlCommandRestore.ExecuteNonQuery(); sqlCommandRestore.Dispose(); sqlConnection.Close(); sqlConnection.Dispose(); } |
Look into Azure Blob Storage for connection and connection string:
Thanks for sharing your knowledge.
I want to implement this for multi tenant application my tenant database is in single database.
Any Suggestion ?
Thanks,
Bharat Prajapat
Nice article