By Delia / Last Updated October 30, 2021

Backup database in SQL Server Management Studio

As you probably know, there are multiple backup methods in Microsoft SSMS, 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

In Microsoft SQL Server Management Studio, the easiest way to backup database 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: After backup, you can also restore SQL database from bak file via SSMS. 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
GO
BACKUP DATABASE [database] TO DISK = 'filepath\fileaname.bak' WITH COMPRESSION, INIT, STATS = 5;
GO

full backup

Create a differential backup:

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

differential backup

Create a transaction log backup:

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

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

DECLARE @strPath NVARCHAR(200)
set @strPath = convert(NVARCHAR(19),getdate(),120)
set @strPath = REPLACE(@strPath, ':' , '.')
set @strPath = 'filepath' + @strPath + '.bak'
BACKUP DATABASE [database] TO DISK = @strPath WITH NOINIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT

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

schedule

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 AOMEI Centralized Backupper Database to backup MSSQL.

It supports full or differential SQL backup, and allows you to select multiple databases or even all databases at once. Also, you can set up a schedule to automate the operation easily.

Download the 30-day free trial to proceed:

Download Free Trial Win PCs and Servers
Secure Download

Install Centralized Backupper Database on the central machine, and client packages on all network connectetd PCs, workstations and servers that you want to protect. Then you can Request Control over these client computers and manage their backup and restore tasks centrally.

request control

Backup SQL databases with centralized backup software:

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

sql server backup task

2. Click + Add Computers to detect the client computers with SQL database, select any of them from the result list.

add computer

3. Click + Add to detect instances on the specified computer. You can select multiple databases or the entire instance at once.

add databases

4. Click on Step 3 > Add Storage to specify a target network share or NAS. Then you can click Start Backup to save and execute the task.

add storage

To automate the backup, just click Schedule to configure the frequency and backup type (full or differential).

backup schedule

To restore from the backup, click Advanced at the top right corner of the task, and choose Restore to proceed. It allows you to restore one or multiple databases to any earlier date.

restore backup

Conclusion

The purpose of this article is to introduce the frequently used methods of backing up databases via SQL Server Management Studio and a more convenient alternative - AOMEI Centralized Backupper Database Edition.

It not only provides a concise graphic interface, but also simplifies the process of selecting databases and creating schedules. As a centralized managment tool, it even enables you to restore SQL databases to another server directly.