Today there are several methods of how to backup the SQL Server 2016 database. In this article, we will review how to backup SQL Server 2016 and what features have been added.

How to backup SQL Server 2016

Below we want to discuss the three main ways of how to backup SQL Server 2016. Everyone who works with SQL Server knows that creating backups according to your schedule is crucial for keeping your SQL Server 2016 database safe and sound. So, let’s start:

Using SqlBak

The simplest way to backup SQL Server 2016 is to use a third-party tool, such as SqlBak. This is a simple service that backups SQL Server 2016 according to your backup plan. All you need is to spend a few minutes and create a backup job, and SqlBak will make all the work for you. Please note that all standard T-SQL Commands to backup your SQL Server databases apply in SqlBak.

Using T-SQL Commands

T-SQL Commands are supposed to be the traditional method of how to backup SQL Server 2016. Nowadays, backing up a database manually or with the help of T-SQL, Commands isn’t really convenient because you have to check your SQL Server 2016 database regularly to perform backups according to your backup plan. But what if your database updates continuously, 24 hours, then how to backup SQL Server 2016 at night? Obviously, you can create additional scripts and perform backups with the help of them, although it requires more time, effort, and skills. However, here are the examples of T-SQL Commands for how to backup SQL Server 2016:

BACKUP DATABASE your_database TO DISK = 'full.bak'
BACKUP DATABASE your_database TO DISK = 'diff.bak' WITH DIFFERENTIAL
BACKUP LOG your_database TO DISK = 'log.bak'

Using SQL Server Management Studio

If you want to know a way of how to backup SQL Server 2016 with the help of SSMS, then check out the following steps:

• Right-click the database you need to backup and choose “Tasks”, then select “Back Up”.
• In the “Back Up Database” window make all necessary configurations and press “OK”.

Quick review what’s New for SQL Server 2016 Standard Edition

Query Store

The Query Store feature keeps records of previous query execution plans with their performance data, and quickly detects queries that have gotten slower recently, enabling users to force the use of an older, better plan, if necessary.

Stretch Database

Use the brand new hybrid feature “Stretch Database” from Microsoft to minimize your storage. The basics of Stretch Database are that some portion of your tables will be transferred to Azure SQL Database in the cloud. When you send a query to such tables, the query optimizer knows which rows are on your server and which rows are in Azure, and separates the workload appropriately.

JSON Support

Today SQL Server 2016 supports JavaScript Object Notation (JSON). In recent years several additional large databases have added this support too.

Row Level Security

SQL Server 2016 has introduced row-level security (RLS), a feature that different databases have had for quite a few years. This limits which users may observe what data is in a table, based on a function.

Always Encrypted

Always Encrypted is a brand-new service through the use of an enhanced client library at the application so the data stays encrypted in transit, at rest, and while it is alive in the database.