4 Ways to Backup Database in SQL Server Management Studio

Any loss or corruption of crucial SQL data could have severe consequences for businesses, ranging from financial loss to damage to reputation. Therefore, we introduce 5 efficient backup methods in SQL Server Management Studio to ensure data safety.


By Delia / Updated on April 1, 2024

Share this: instagram reddit

SQL Server Management Studio Backup Database Guide

As you probably know, there are multiple backup methods in Microsoft SSMS (SQL Server Management Studio), each with its own advantages and disadvantages. So before you start, I’d like to briefly introduce and compare them for your reference:

Backup GUI: This feature is easy to operate without using scripts, but only one database can be backed up at a time, and the operation cannot be automated.

T-SQL script: You need to know basic T-SQL commands beforehand, and any small mistake may lead to operation failure. But with effective scripts, you can perform some more advanced operations easily.

Maintenance Plans: It allows you to backup multiple databases at once, and add a schedule to automate the task. However, it is only available for non-Express editions and is relatively cumbersome to operate.

SQL Server Agent: It can also automate backups with high flexibility, but you need to fill in the backup script yourself as the core step. Also, it is not applicable to SQL Express editions either.

You can pick the way you like, or use another SQL backup software that can schedule backup multiple databases simply and intuitively.

sql server

Method 1. Create a SQL backup task with GUI

To use Microsoft SQL Server Management Studio backup database, the easiest way is using the backup GUI.

1. Launch SSMS and connect to your instance. Right-click the database you want to back up in Object Explorer, choose Tasks > Back Up.

sql database backup gui

2. Choose the backup type (full/differential/transaction log), and Add a path as you need.

It's recommended specify a easily accessible path so that the backup file can be easily found when you need to perform recovery. Then, enter the file name as you like and remember to add the .bak suffix.

add target path

3. Click OK to execute the backup. It will prompt you a message once the operation is complete. Then you can find the backup file in the target folder.

Tip: In SQL management stutio, you can also restore SQL database from bak file. Click to learn more details.

Method 2. Backup database with T-SQL commands

The basic T-SQL backup statements is BACKUP DATABASE TO DISK. You can use it flexibly as you need.

Create a full backup:

USE master
BACKUP DATABASE [database] TO DISK = 'filepath\fileaname.bak' WITH COMPRESSION, INIT, STATS = 5;

full backup

Create a differential backup:

USE master
BACKUP DATABASE [database] TO DISK = 'filepath\filename.bak' WITH DIFFERENTIAL

differential backup

Create a transaction log backup:

USE master
BACKUP LOG [database] TO DISK ='filepath\filename.trn' WITH NORECOVERY

transaction log backup

Method 3. Schedule backup with Maintenance Plans

This feature gives you options to create a plan manually, or complete the operation following a wizard. Here I will mainly introduce the first one.

1. Make sure the SQL Server Agent is enabled, otherwise please Start it first.

2. Navigate to Management > Maintenance Plans. Right-click it and choose New Maintenance Plan. Give it a name to proceed.

new maintenance plan

3. Click Subplan Schedule (calendar icon) to set up the backup schedule. Keep the schedule type as the default Recurring, and configure the frequency as you like. Click OK to confirm it.

schedule settings

4. Hit Toolbox and drag the Back Up Database Task into the blank area on the right side.

5. Double-click the task to choose the backup type, and the databases to backup. Then specify a target folder.

set up backup task

6. After saving the plan, you can find it under SQL Server Agent > Jobs. It will be executed automatically according to your schedule. You can also right-click it and choose Start Job at Step to test it immediately.

Tip: The procedure of creating a backup task with Maintenance Plan Wizard is largely the same. But it will walk you through all the steps, so it's more suitable for beginners.

select backup task

Method 4. Create an auto backup job with SQL Server Agent

This method essentially uses T-SQL commands to perform database backup, but you can add a schedule to automate the task.

1. Right-click SQL Server Agent and choose New > Job.

new job

2. Enter a name and description for the job. Then move to Steps tab.

3. Click New to add a step name and any T-SQL backup commands as you need. Here’s an example

set @strPath = convert(NVARCHAR(19),getdate(),120)
set @strPath = REPLACE(@strPath, ':' , '.')
set @strPath = 'filepath' + @strPath + '.bak'

4. Move to Schedules tab and click New to set up the schedule type and frequency. Click OK to finish the setup.


5. Then you can find the created backup job under SQL Server Agent > Jobs, and choose Start job at Step to test it.

test backup job

Easier way to backup multiple SQL databases with schedule

If you find the above 4 backup methods in SQL Server Management Studio complicated or not effective enough, you can also try the professional SQL backup software - AOMEI Cyber Backup to backup your MSSQL. It allows you to backup multiple databases or even all databases at once. Also, you can try the following features to ensure the safety and integrity of your SQL databases.

Scheduled Backups: Set up regular backup schedules to automatically safeguard your SQL data without manual intervention.
Flexible Backup Options: Choose full, incremental, or differential backup types to suit your specific backup needs and optimize storage usage.
Intuitive Interface: Enjoy a user-friendly interface that simplifies the backup process, making it easy to configure and manage SQL backups efficiently.
Centralized Management: Manage all your SQL backups from a centralized console, streamlining administration and monitoring tasks across multiple databases or servers.

Download the 30-day free trial to proceed:

Download Free TrialMicrosoft SQL Server 2005-2022
Centralized and secure SQL backup

💡 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.

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 icon -> Authentication to validate the database instance. You can choose Windows Authentication or SQL Authentication.

Add Device

💡 Create MSSQL backup task

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

Backup Type

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

Select Database

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

Choose Target

4. 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.

* 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.

5. Click Start Backup to 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. 

💡 Restore a database in SQL Server easily

1. Click Backup Task on the left menu bar, locate the task you want to restore, and click icon -> Restore.


2. Choose the backup version you wish to restore.

Select Content

3. Choose the destination where you want to restore, and then click Start Restore.

* If you're restoring to the original location, no need to select a target. "Restore to original location" is automatically selected, allowing you to begin restoration directly.

Restore to Original Location

If you prefer to Restore to a new location, you must choose a target and provide the name for the new database. You also have the option to adjust the storage location.

Restore to New Location

4. Confirm the settings, then click Start Restore to initiate the restoration process. Please wait patiently for it to finish. You can click on theiconbutton to view restore details or cancel the process if needed.

Start Restore

When the restore is complete, you can see the restored database in Microsoft SQL Server Management Studio. With AOMEI Cyber Backup's SQL backup, you can rest assured that your SQL databases are protected, allowing you to focus on your business without worrying about data loss or downtime.


This article aims to outline the steps for database backup using SQL Server Management Studio, offering four efficient methods along with a user-friendly alternative - AOMEI Cyber Backup. AOMEI Cyber Backup not only offers a sleek graphical interface but also streamlines database selection and plan creation. Serving as a centralized management tool, it even facilitates direct backup SQL databases to another server.

Delia · Editor
Delia owns extensive experience in writing technology-related blog posts, and has been a part of AOMEI since 2020 to provide expertise in data security and disaster recovery. She works with Windows operating systems, SQL databases, and virtualization platforms such as VMware and Hyper-V, specializing in troubleshooting and advising on data protection and migration.