BlogDevOps

How to Automate Continuous Database Backups and Restores

By March 26, 2020 August 7th, 2020 No Comments

How to Automate Continuous Database Backups and Restores

Database Backup & Restore

Wondering “How do I automate backup and restore in SQL Server?

In this post, we will share SQL automation scripts using DevOps (The scripts can be used to automate any database system). These automation scripts can not only help you automate the backup process but will also help in automating the database restore process.

Microsoft SQL is the most widely used database management system. As a user, you put a huge amount of data there. Using this popular database is easy but its maintenance and backup is not. We hear a lot of cries from users where they accidentally drop a database, lose backups, want to restore and retrieve the data and more.

While there are several easy ways you can lose your data such as a bug in the database engine, using an unreliable infrastructure to host your database, damage to the files, storage device failure etc., there are also very easy and simple steps to avoid data loss and automate SQL database backup & restore.

But you don’t want to lose your data, do you? Or are you hit by this disaster ALREADY???

To help you out of this mess, we bring to you this step-by-step guide on how to automate SQL database backup and how to restore the backups.

Although these days databases are more stable and reliable, you still need perfect, automated database backup and restore solutions for your database, be it SQL, MySQL, Oracle or any other database. Let’s dive in:

Other posts: Azure Infrastructure Migration

How to Automate Continuous Backups for SQL Database

Whether your SQL server is running on Microsoft Azure, AWS (Amazon Web Service), Google Cloud or is running on any Virtual Machines (VMS) or PaaS instances, the tips and tricks for automation of database backups shared in this article are universal.

Important Benefits of Database Backup Automation
  • Guess what? you need not be an Automation expert, a skilled database engineer or an experienced database administrator to execute this job.
  • Forget database mirroring. You don’t have to spend time in learning how it’s done.
  • This is just a one-time setup. Yes, you read it right! Once you setup the automation explained in this article, you will never have to worry about scheduling backups. It automatically does all that for you.
Follow this easy 4-step guide on how to automate continuous backups for SQL database:

Step 1: Run an Azure Batch Job – Portal

Microsoft Azure Batch is largely used for its cost optimization benefit. You’re charged only when you execute a back-up or restore Job. This job scheduling and compute management platform is widely used as it enables running large-scale applications efficiently on the cloud.

Now, follow this link to create a batch account which you will need to create pools, jobs, and tasks.

Step 2:  obtain the SQLpackage command-line utility

You can obtain the SQLpackage command-line utility in “SQL Server Database Tools” from Microsoft.

Step 3: Upload to the Azure blob container

Step 4: Create Azure batch task Add

  • RESOURCE FILES as blob container location

Command Line

cmd /c  “%AZ_BATCH_APP_PACKAGE_sqlpack#v1%\\DAC\\bin\\SqlPackage.exe /a:Export /ssn:<HOST IP> /sdn:<databasename> /su:<loginUSErNAME> /sp:<PASSWORD>/tf:\\<Azure-file-share>.file.core.windows.net\common\extractv-batch.bacpac”
How to Automate Continuous Database Restore:

Follow the below 4 easy steps to automate database restore:

Step 1: Run an Azure Batch Job – PortalFollow this link to create a batch account which you will need to create pools, jobs, and tasks.

Step 2: Obtain the SQLpackage command-line utility

Step 3: Upload to the Azure blob container

Step 4:  Create Azure batch task Add

  • RESOURCE FILES as blob container location

Command line

cmd /c “%AZ_BATCH_APP_PACKAGE_sqlpack#v1%\\DAC\\bin\\SqlPackage.exe /a:Import /tsn:ae-dev-sql.database.windows.net /tdn:extractv /tu:tabexpone /tp:Tabexp@123 /sf:\\.file.core.windows.net\common\extractv3-batch.bacpac
Conclusion

The above steps for automation of database backups will help you avoid disasters from data loss and focus on more important things.

TabExp Apps with several years in Microsoft Azure DevOps and Azure Automation space, has helped several businesses automate software development and delivery. Get in touch with our Azure consultants for quick cloud migration and Azure DevOps challenge resolution.

Our Recent Posts

A multinational chain of hospitals in the USA performs an Azure database migration as a solution for higher data protection and security while meeting the regulatory compliances.

A key player from the manufacturing industry performs legacy application migration to the Azure cloud. TabExp Apps builds an Azure SaaS module solution & Azure IoT solution.

TabExp Apps performs an Azure infrastructure migration for a key player in the entertainment and gaming industry to scale-up their platform & render better user & gaming experience.

Leave a Reply