Top 3 Ways for MSSQL Backup in SQL Server 2012, 2017, 2019

What is MSSQL and why is MSSQL backup important

Microsoft SQL Server (MSSQL) is a relational database management system developed by Microsoft. It can help to store and retrieve data as requested by other software applications which may run either on the same computer or on another computer across a network (including the Internet).

There are a dozen different editions of Microsoft SQL Server aimed at different audiences and different workloads, such as Enterprise edition, Standard edition, Web edition, and Evaluation edition (Trial edition), etc.

If your enterprise is using Microsoft SQL Server, it is important to backup MSSQL databases because:

• Potential security threats: media failure, user errors, a damaged disk drive, permanent loss of a server, or even natural disasters can cause data damage or data loss.

• Instant disaster recovery: you can easily restore damaged or lost data from the backup copy so as to keep your business ongoing.

• Database administration: sometimes you may need to copy a database from one server to another or archive a database, etc.

Then, how to do MSSQL database backup? Please read on.

How to backup MSSQL databases in SQL Server 2012/2017/2019 easily

In MSSQL backup database can be done by many ways. I'll mainly introduce 3 effective solutions to run Microsoft SQL Server backup.

Solution 1: Using SQL Server Management Studio
Solution 2: Using Transact-SQL
Solution 3: Using third-party MSSQL backup software

Solution 1: Using SQL Server Management Studio

SQL Server Management Studio (SSMS) is an integrated environment for managing any SQL infrastructure, from SQL Server to Azure SQL Database. You can use it to query, design, and manage your databases and data warehouses whether they are on your local computer or in the cloud.

As an example, I will illustrate how to backup database in SQL Server 2012 SSMS:

1. Download and install SQL Server Management Studio on your computer.

Tip: SSMS is available only as a 32-bit application for Windows. If you need a tool that runs on operating systems other than Windows, Azure Data Studio is recommended.

2. Start SQL Server Management Studio and the Connect to Server window opens. After you've completed all the fields, select Connect.

Tip: If you fail to connect to an instance of the SQL Server Database Engine on a single server, visit Troubleshoot connecting to the SQL Server Database Engine.

3. In Object Explorer, expand the server tree. Then, expand Databases.

4. Right-click the database that you wish to backup, point to Tasks, and then click Back Up....

5. In the Back Up Database dialog box, do the following settings:

• Database: the database that you selected appears. You can change to any other database on the server from the drop-down list.

• Backup type: the default type is Full. You must perform at least one full database backup before you can perform a SQL Server differential backup or a transaction log backup.

• Backup component: choose Database.

• Back up to: choose Disk and review the default location for the backup file (in the .../mssql/backup folder).

6. Click OK to initiate the backup. When the backup completes successfully, click OK to close the SQL Server Management Studio dialog box. Exit SSMS.

Notes:

• When selecting Backup type, you can see an option called Copy-only backup. A MSSQL Copy-only backup is an independent SQL Server backup that won’t affect the overall backup and restore procedures for the database.

• To do more configurations, click Media Options and Backup Options from the left panel of the Back Up Database dialog box.

Solution 2: Using Transact-SQL

Transact-SQL (T-SQL) is a set of programming extensions from Sybase and Microsoft that add several features to the SQL. You can use the BACKUP DATABASE statement to create a MSSQL backup. The basic SQL server backup database command is:

BACKUP DATABASE database TO backup_device [ ,...n ] [ WITH with_options [ ,...o ] ] ;

Follow the steps below to backup the complete TestDB database to disk:

1. Open SQL Server Management Studio and connect to SQL server. Then, click New Query from the Standard bar.

2. Enter the following T-SQL code snippet into the query window:

USE TestDB;
GO
BACKUP DATABASE TestDB TO DISK = 'D:\MSSQLBackup\TestDB.bak'
WITH FORMAT, MEDIANAME = 'SQLServerBackups';
GO

3. Click Execute to run the backup.

Notes:

• To put the backup file in the default backup directory, you can omit the path from a DISK clause. In my case, it would be DISK = 'TestDB.bak'.

• Use the FORMAT clause when you are using media for the first time or you want to overwrite all existing data. Optionally, assign the new media a media name using MEDIANAME.

Solution 3: Using specialized MSSQL backup software

If you want the flexibility to do more complex operations and do not want to use T-SQL scripts, then specialized MSSQL backup software may suit you better.

AOMEI Cyber Backup is an enterprise-class backup software that enables you to backup and restore SQL databases on all Windows PCs & Servers within LAN. More specifically, you can enjoy the following benefits:

🔹Schedule automatic backup
For SQL backup tasks that need to be executed frequently, you only need to set it up once to have it performed automatically on a daily, weekly or monthly basis. Full or differential backups can also be performed optionally.
🔹Select multiple databases at once
When setting up a task, you can choose as many databases as you want, or even multiple instances on a server. When restoring, you can select all the databases included in the backup, or only a few specific databases.
🔹Manage backup tasks centrally
All the created backup tasks will be listed in the software, allowing you to execute, edit, delete or restore them centrally. You don't need to find all those bak files scattered all over the computer.
🔹Restore to another SQL Server
Besides restoring to the original location, this software also allows you to restore database to another SQL Server on another client computer.

To back up MSSQL databases with less limitations, you can download the 30-day free trial, and follow the guide to have a try:

Set up the MSSQL backup software: 

1. Run the installed AOMEI Cyber Backup on the server machine, navigate to Source Device -> Microsoft SQL -> Add Microsoft SQL.

2. Choose Download proxy program (or Copy link) and install it on to the device with SQL Server installed. Then, click Already installed proxy and select the device.

Next, click  -> Authentication to validate the database instance. You can choose Windows Authentication or SQL Authentication.

Create MSSQL backup task:

1. Click Backup Task -> Create New Task to launch the task creating page. Choose backup type as Microsoft SQL Backup.

2. Click on Device Name to specify the SQL instances and databases for backup. You can select one or multiple databases as you need.

3. Select Target to save backups. You can either specify a local path or network path.

4. Before Start Backup, you can also set up a Schedule to run the SQL database backup daily, weekly or monthly, and set the backup method as Full, Incremental or Differential Backup.

Execute the task. Once it's finished, you will find it in the Backup Task tab. You can edit it or restore database from it as you need.

✍More useful features:

• Backup Cleanup helps you to delete older backup version automatically and therefore save storage space.
• Email Notification enables you to receive email notifications when the task is abnormal or successful.

Wrapping things up

You have learned three effective solutions to perform MSSQL backup. You can choose SQL Server Management Studio, Transact-SQL or AOMEI Cyber Backup depending on your situation.