4 Ways to Backup Database in SQL Server Management Studio

In this article, we introduce 4 common backup methods in SQL Server Management Studio, and a third party backup software as alternative.

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.

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.

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.

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
GO
BACKUP DATABASE [database] TO DISK = 'filepath\fileaname.bak' WITH COMPRESSION, INIT, STATS = 5;
GO

Create a differential backup:

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

Create a transaction log backup:

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

 

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.

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.

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.

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.

 

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.

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.

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

 

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 Cyber Backup 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:

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.

Conclusion

The purpose of this article is to introduce the steps to use SQL Server Management Studio backup database, and a more convenient alternative – AOMEI Cyber Backup.

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.