Microsoft SQL is the most widely used database management system. As a user, you put 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 set up. 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: /sdn: /su: /sp:/tf:\.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.
Top comments (0)