Microsoft SQL Server Backup with NAKIVO Backup & Replication

In my previous post I shared How to Configure NAKIVO VM Backup to Azure Cloud Storage  one of the great feature by NAKIVO to customers. In this post I will share detailed information and procedure to configure Microsoft SQL server backup with NAKIVO .

Microsoft SQL Server is one of the popular databases used by mostly used by many application in every Organization. Database contain the core data of all application and we should consider this business-critical one, so backup of database is very important. As we know data loss may happen in many ways like viruses, human errors or accidental deletion, database corruption etc.

What type of backup need for SQL Database?

As I mentioned above  database backup is very important but what type of backup we have to perform for SQL since there are many types like , full , incremental , synthetic , image level etc are  available .  Application is fully depended on consistent data on the database, so we have to make sure backup should be consistent. Therefore, it is necessary to perform application-aware backup, which will ensure that database data is consistent and can recovered without an issue.

How to ensure data consistency?

Every SQL Server database has a transaction log that records all transactions and the database modifications made by each transaction. The transaction log is a critical component of the database.

Is Good to keep the Transaction Logs?

As I said it is critical component for the data base but SQL Server can accumulate a very large number of transaction log files due to the continues transactions made by application. Moreover, if you keep this longer time on server, it will consume more storage and this is not good for database .To get rid of such situation we have to truncate the transaction logs from server.

What is the best way to truncate log?

We can use trusted backup solution like NAKIVO to keep our data safely with Log truncation feature. NAKIVO  uses application-aware backup mode to perform log file truncation without any risk of losing data in the running applications. Application-aware mode can flush any application data out of RAM and pending I/O operations to disk, so that backups remain application consistent.

Configure Microsoft SQL Backup

About Environment we run backup

• vSphere 6.7
• WIndows 2012 R2 Virtul Machine
• Microsoft SQL Server 2014
• NAKIVO Backup & Replication V8

Log in to NAKIVO portal and click Create in the Jobs dashboard

Select VMware vSphere backup job.

Select the SQL Virtual Machine you need to backup and Click Next

 

Select the backup repository  and click Next

Schedule your backup job as per requirement Click Next or select Do not Schedule , run on demand if you want to run manually and no schedule required

For Manual Backup – we are using this option as we don’t need any schedule .

Set your retention rules as per requirement  and Click Next

You have to fill the Job Options  as shown below and click Finish 

Job name : Backup Job Name

App-aware mode : Enabled (proceed on error) which is default option from drop down .

• Enabled (proceed on error) -: This option will allow finishing a backup job  even if an error occurred during the application-aware processing.
• Enabled (fail on error) :- This option will stop the backup process, if an error occurs during the application-aware processing.
• Disabled – App-aware mode will be disabled

Select Truncate SQL Server logs from checkbox  with  On successful VM processing only which will  truncate the SQL log after a VM application-aware backup is successfully completed.

Note :- When select Truncate SQL Server logs  new window will appear to select either existing credentials to the instance database or add the new credential from Manage Credentials Option . And Credentials are mandatory to authorize the VM and access to the SQL database.

 

Navigate to Jobs dashboard and click Run Job and then click Run  for all VM’s 

Run for selected VM’s :- If you have more VM’s in Job created select the VMs and use  the option .

 

Download a full-featured free trial –  Download

Conclusion

We have completed the backup of the SQL server 2014  on windows 2012 R2 virtual machine which is hosted on vSphere 6.7 environment using NAKIVO Backup & Replication version 8 . Backup of SQL is very important because database contains  business-critical data and NAKIVO Backup & Replication has ability  to take the complete virtual machine ,database backup .Also NAKIVO Backup & Replication has Microsoft SQL object recovery feature is available which help to recover data very quick.