Nowadays, there are several ways of how to backup the SQL Server 2016 database. In this article, we are going to take a look at how to backup SQL Server 2016 and what features have been added.

How to backup SQL Server 2016

Here we are going to discuss the three main ways of how to backup SQL Server 2016. Everyone who works with SQL Server knows that scheduled backups are crucial for keeping your SQL Server 2016 database safe and sound. So, let’s begin:

Using SQLBackupAndFTP Utility

The easiest way to backup SQL Server 2016 is by using SQLBackupAndFTP. This is a simple tool that backups SQL Server 2016 according to your schedule. All you need to do is to spend several minutes creating a backup job and SQLBackupAndFTP will do all the rest for you. Please note that SQLBackupAndFTP uses all standard T-SQL Commands to backup your databases.

Using T-SQL Commands

T-SQL Commands is supposed to be the classical way of how to backup SQL Server 2016. This method isn’t really convenient because you need to check your SQL Server 2016 database all the time to make backups according to your schedule. But what if the changes occur all day at night, how to backup SQL Server 2016 at night? Of course, you can create some extra scripts and perform backups with the help of them, but it requires more skills, time, and effort. Nevertheless, 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 are looking for a way of how to backup SQL Server 2016 with the help of SSMS, then follow the instructions below:

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

Quick Review What’s New for SQL Server 2016 Standard Edition

Query Store

The Query Store feature keeps a history of query execution plans with their performance data and promptly identifies queries that have got slower recently, enabling users to force the use of an older, better plan, if needed.

Stretch Database

Use the brand new hybrid option “Stretch Database” from Microsoft to minimize your storage. The basics of Stretch Database are that some parts of your tables will be placed into an Azure SQL Database in the cloud. When you make a query to those tables, the query optimizer understands which rows are on your server and which rows are in Azure, and separates the workload properly.

JSON Support

Nowadays SQL Server 2016 supports JSON (JavaScript Object Notation). A number of different large databases have added this support in recent years.

Row Level Security

SQL Server 2016 has introduced row-level security (RLS), a feature that other databases have had for many years. This limits which users can read what data is in a table, based on a function.

Always Encrypted

Always Encrypted is a brand-new functionality 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.