DB Backup in Azure VM

backupDB_to_NAS.jpgIf you are using the SQL Express version in your Azure VM then you need to take regular backups in the VM for your SQL DB. This article will help you to understand how to do it.

Choose the right backup method

Here is a quick summary of the most common backup methods available:

Mirror Backup creates a mirror copy of the source data. When a source file is deleted, that file is also deleted in the mirror backup automatically.

  • Pros: Fast backups, fast recovery
  • Cons: High risk of data loss

Full Backup captures your entire system and all the data you want to protect. Frequent full backups result in easier recovery operations.

  • Pros: Fast, reliable recovery
  • Cons: Slower backup, lots of storage required

Incremental Backup captures only the changes made since the last incremental backup. Saves both time and storage space, and ensures that your backup is up to date.

  • Pros: Fast backups, low storage space
  • Cons: Slower recovery, risk of data loss

Differential Backup captures only the changes made since the last full backup, not since the last differential backup. This requires more storage space, but ensures an easier, more reliable restore. Many organizations struggle with incremental vs. differential backup solutions.

  • Pros: Fast backups, low storage space
  • Cons: Slower recovery

Where To Store DB Backup’s!

Once you’ve created a backup, where is the best place to store it safely? Here are the most common options:

External Hard Drive

  • Portable and comparatively less expensive
  • Widely used storage media

Cloud Storage

  • Secure offsite storage
  • Files remain safe & accessible at any time
  • Most reliable & convenient for business data

Network Attached Storage (NAS)

  • Uses NAS device
  • Accessible from anywhere on your network

Let’s See How we can manage with SQL Express!

In SQL Standard version or higher we can use SQL Agent to configure a job to generate the DB backup. But if you are using Express version of SQL then you cannot use SQL Agent instead you can use any alternate method to generate the SQL DB backups.

The following method shows how we can generate DB backups using a bat file configured in Windows Task Scheduler using a Stored Procedure.

The below script is used for generating the Stored Procedure for Backup https://support.microsoft.com/en-sg/kb/2019698

The below mentioned options are available to take DB Backups

  • Full Backup
  • Differential Backup
  • Log Backup

Example1: Full backups of all databases in the local named instance of SQLEXPRESS by using Windows Authentication

sqlcmd -S .\EXPRESS –E -Q “EXEC sp_BackupDatabases @backupLocation=’D:\SQLBackups\’, @backupType=’F'”

Example2: Differential backups of all databases in the local named instance of SQLEXPRESS by using a SQLLogin and its password

sqlcmd -U SQLLogin -P password -S .\SQLEXPRESS -Q “EXEC sp_BackupDatabases  @backupLocation =’D:\SQLBackups’, @BackupType=’D’”

Note: The SQLLogin shouldhave at least the Backup Operator role in SQL Server.

Example 3: Log backups of all databases in local named instance of SQLEXPRESS by using Windows Authentication

sqlcmd -S .\SQLEXPRESS -E -Q “EXEC sp_BackupDatabases @backupLocation=’D:\SQLBackups\’,@backupType=’L'”

The following swicthes can be used with sqlcmd

Note:
-S for Server Name/Instance, Port
or use
/S

-E for Trusted Connection

100.png

Next create a Schedule Task in Windows Task Scheduler as mentioned below.48.png

Create  a Trigger for the Task which will fire the Task daily once

116.png

Add an Action for the Task, which will call the bat file from the specified location.

117.png

After saving the Task you can see the Task as mentioned below.1002.png

This Scheduled Task will take a back up of All DB in the Server to the specified path.

Happy Programming.

 

 

%d bloggers like this: