[3 Methods] How to Perform MSSQL Backup and Restore

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.

Top 3 Ways to Perform MSSQL Database Backup and Restore

In Microsoft SQL backup database can be done by many ways. I'll mainly introduce 3 effective ways of how to backup database in SQL server 2019, 2017, 2012. 

Solution 1. Backup Microsoft SQL using Server Management Studio, the Official MSSQL management tool. Provide a basic backup feature to backup each database individually. 

Solution 2. MSSQL Backup databases using Transact-SQL: Suitable for those who are familiar with using commands.

Solution 3. Backup MSSQL with AOMEI Cyber Backup: Professional backup solution with a user-friendly interface. Support automatic backup, and centralized backup for all databases. Provides various advanced features to meet different backup demands.

Solution 1: backup Microsoft SQL using 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 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: MSSQL backup databases with 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 perform SQ server backup database using commands. 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: Backup MSSQL database with AOMEI Cyber Backup

If you want the flexibility to do more complex operations and do not want to use T-SQL scripts, then specialized SQL server 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.
🔹 Support multiple backup locations:
You can save backup files to different local, such as the local path, external hard drive, Network Share, NAS drive. And if you want to backup MSSQL to AWS S3, it supports adding Amazon S3 bucket and archiving backup versions to it.

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

Add MSSQL databases

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 the 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 versions automatically and therefore save storage space.
• Email Notification enables you to receive email notifications when the task is abnormal or successful.

Wrapping things up

Perform MSSQL backup is ensure data security. In this post, you can learn three effective solutions to protect your Microsoft SQL databases. You can choose SQL Server Management Studio, Transact-SQL, or AOMEI Cyber Backup depending on your situation. But by contrast, AOMEI Cyber Backup is more recommended, it uses an intuitive and offers automated backup to simplify this whole process.