Top 3 Ways to Run 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 Centralized Backupper Database Edition is an enterprise-class backup software that enables you to backup and restore all Windows PCs & Servers within LAN. The supported data includes files, partitions, disks, OS and SQL databases.

More specifically, you can enjoy the following benefits with its SQL Server Backup feature: 

◑ 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 backup MSSQL databases, you could download the 30-day free trial, and follow the guide to have a try:

To backup MSSQL databases, you could download the 30-day free trial, and follow the guide to have a try:

Installation: Install Centralized Backupper Database on a computer as the central management console, and use it to install the client program on all computers you want to manage.

Setup: Request control for the client computers with SQL instance installed. After confirmation, you can continue to create centralized backup task.

1. Navigate to Tasks > New Task > SQL Server Backup.

2. Name the task as you like, and click + Add Computers in Step 1 to detect all the client computers with SQL instance. It will return a list of results and you can select one computer from it.

3. Now in Step 2, click + Add to detect the instances on the selected computer, and choose what to backup. You can select as many databases as you want.

4. Click on Step 3, and click Add Storage to enter the network path of share of NAS as the backup target. The added path will be saved for your direct use in the future.

5. Manage the Settings or set up a Schedule as you need, then you can click Start Backup to Only create the task or Create and execute tasks.

The created tasks will be added to the Tasks tab. You can directly edit the task or restore databases from it.

Tips:

• Settings - Here you can configure the backup encryption, compression, or enable email notification.

• Schedule - Choose a schedule type (Once/Daily/Weekly/Monthly) and set up its frequency and execution time points specifically. In Advanced tab, you can also choose full backup or differential backup. But please note you can only perform full backup on the master database. 

Wrapping things up

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

If you need to protect MySQL database as well, please refer to MySQL full backup for a more applicable solution.