By Alison / Last Updated April 19, 2022

What is SQL Server Management Studio

SQL Server Management Studio (SSMS) is an integrated environment for managing the SQL Server infrastructure. It provides tools for configuring, deploying, monitoring, and managing SQL Server instances, which you can use to upgrade data-tier components, generate SQL Server database scripts or execute queries.

With the graphical interface of SQL Server Management Studio, any general user and developer can perform most SQL Server database operations and management, without using complex SQL statements, including the daily database backups.

This article shows you how to use SQL Management Studio to meet different backup needs.

SQL Server Management  Studio logo

How to Create Backups using SQL Server Management Studio

Using SQL Server Management Studio to backup SQL Server databases is almost the first choice for most users. In addition to creating different types of backups, we may also need to back up databases to different locations, back up multiple or all databases, create automatic backup and delete tasks, back up individual tables, etc.

In the following I will describe the detailed steps to achieve them.

Creating Different Types of Backups for Single Database

To meet different needs, SQL Management Studio supports many backup types. This section will show you the detailed steps of the following 5 common types of backups:

  • Full backup: This is the most common backup type, and also the simplest and most basic one. A full backup contains all database objects such as tables, indexes, views and stored procedures, and transaction logs needed when restoring.
  • Differential backup: A SQL Server differential backup only captures the data that has changed since the last full backup. The time to restore a differential backup is shorter than that of a full backup.
  • Transaction Log Backup: A transaction log backup captures all transaction logs that record the changes to the database since the last transaction log backup. It is used to restore the database to a specific point in time.
  • Copy-only backup: A MSSQL copy-only backup is completely independent of the SQL Server database backup sequence, and does not interfere with the subsequent differential backup and restore.
  • Backup Encryption: SQL Server 2014 and later versions provide the option to encrypt backups when they are created. Before enable SQL Server Backup Encryption, you need to create and back up the database master key and certificate.

Creating Full, Differential, or Transaction Log Backups

1. Launch SSMS and connect to your instance.

2. Expand Databases. Right-click the database name you want to back up, and select Tasks > Back Up…

Backup database

3. In the pop-up window, select Full, Differential, or Transaction Log in Backup type.

NoteIf you want to create differential backup or transaction log backups, please create a full backup in advance, as the differential or incremental base.

Select Backup type

4. Click Add… to specify a Destination location. Name this backup file with the suffix .bak in File name.

5. Click OK to execute.

Creating a Copy-only Backup with SSMS

1. Launch SSMS and connect to your instance.

2. Expand Databases. Right-click the database name you want to back up, and select Tasks > Back Up…

3. In the pop-up window, select Full or Transaction Log in Backup type.

4. Check the Copy-only Backup option below Backup type.

Check Copy-only backup

5.Click Add… to specify a Destination location. Name this backup file with the suffix .bak in File name.

6.Click OK to execute.

Note
Copy-only backup option is available only for full backups or transaction log backups. Copy-only full backups work on all recovery models, while copy-only transaction log backups only work on full recovery model and bulk-logged recovery model.
Also, copy-only backups are restored in the same way as other backups.

Copy-only Backup Completed

Enable Backup Encryption on SSMS

Before you begin, please make sure you’ve created a DMK and a certificate, and made backups of them.

1. Launch SSMS and connect to your instance.

2. Expand Databases. Right-click the database name you want to back up, and select Tasks > Back Up…

3. In the pop-up window, General page, select Full, Differential, or Transaction Log in Backup type.

4. Click Add… to specify a Destination location. Name this backup file with the suffix .bak in File name.

5. Turn to Media Options page, select Back up to a new media set, and erase all existing backup sets. Name the new media set.

Backup to new media set

6. Turn to Backup Options page, check Encrypt backup, and select the Algorithm and Certificate or Asymmetric key.

Enable Backup Cryption

7. Click OK to execute.

Backup SQL Server Database to Different Locations

In practical use, sometimes you may need to back up your database to different locations, such as a network drive. Or to make it easier to choose and change backup location, you may also have specified a logical backup device in advance. In this section I will describe how to back up your database to these locations.

Backup to a Logical Backup Device

Before you start, please note that if you want to you need to specify a logical backup device in SQL Server first.

1. Launch SSMS and connect to your instance.

2. Expand Databases. Right-click the database name you want to back up, and select Tasks > Back Up…

3. In the pop-up window, select Full, Differential, or Transaction Log in Backup type.

4. In Destination section, click Add… to specify the logical backup device you created.

Select logical backup device

5. Click OK to execute.

Using SQL Management Studio Backup to Network Drive

1. Launch SSMS and connect to your instance.

2. Expand Databases. Right-click the database name you want to back up, and select Tasks > Back Up…

3. In the pop-up window, select Full, Differential, or Transaction Log in Backup type.

4. In Destination section, click Add… and manually input the UNC path of the network drive, add the name of this backup file with the suffix .bak. For example: \\192.168.0.52\backups\Data_1.bak

Backup to network drive

6. Click OK to execute.

Using SQL Management Studio to Backup All Databases

If you want to use SQL Management Studio to back up all or multiple databases, it is much easier and faster to create a Maintenance Plan than to manually do it one by one.

However, Maintenance Plan and SQL Server Agent is not available on Express version.

Here are the detailed steps:

1. Launch SSMS and connect to your instance.

2. Expand Management. Right-click Maintenance Plans and select New Maintenance Plan…

New Maintenance Plan

3. Name this plan. Click Toolbox next to Object Explorer, and double-click Back Up Database Task.

Select Back Up Database Task

4. Double-click the newly created backup task in the right blank. First select Backup type in General page.

5. Expand Database(s) dropping menu and select All databases. Click OK to save.

Select All databases

6. Turn to Destination page, fill in the path of the location where you want to store backups. Click OK to save.

Select Destination path

Now you can close maintenance plan settings window or use shortcut win+S to save this plan.

7. Right-click SQL Server Agent to Start it. Find the newly created plan in SQL Server Agent > Jobs. Right-click it and select Start Job at Step… to execute.

Start Job at Step

Schedule Automatic Backup and Deletion Task with SSMS

In order to avoid accidental data loss, timely backup is essential. However, manually using SQL Management Studio to back up database can be boring and time-consuming, so you can make schedule automatic backup tasks to keep them up-to-date.

Besides, backups will accumulate over time and take up disk space. Therefore, I recommend you creating automatic Cleanup task to delete old backups, in combination with auto backup task.

Use SQL Management Studio to Schedule Automatic Backup Task

1. Launch SSMS and connect to your instance.

2. Expand Management, right-click Maintenance Plans and select New Maintenance Plan…

3. Name this plan. Click Toolbox next to Object Explorer, and double-click Back Up Database Task.

4. Double-click the newly created backup task in the right blank. First select Backup type in General page.

5. Expand Database(s) dropping menu and select single or multiple specific databases you want to back up. Click OK to save.

6. Turn to Destination page, fill in the path of the location where you want to store backups. Click OK to close the settings window.

7. Click Subplan Schedule (calendar icon). In the pop-up window, arrange the Frequency, Daily Frequency, Duration, Start date and End date. Click OK to save.

Schedule automatic backup tasks

Now you can close maintenance plan settings window or use shortcut win+S to save this plan.

8. Right-click SQL Server Agent to Start it. Find the newly created plan in SQL Server Agent > Jobs. Right-click it and select Start Job at Step… to execute.

Use SQL Server Management Studio to Delete Backup Files Automatically

1. Launch SSMS and connect to your instance.

2. Expand Management, right-click Maintenance Plans and select New Maintenance Plan…

3. Name this plan. Click Toolbox next to Object Explorer, and double-click Maintenance Cleanup Task.

Select Maintenance Cleanup Task

4. Double-click the newly created cleanup task in the right blank. Select Backup files as deleting files.

5. Select Search folder and delete files based on an extension, and click to specify the Folder location where the backups are stored. Input bak in File extension.

If you want to delete the transaction log backups you can input trn in File extension.

6. In File age, specify after how many days the older files will be deleted. Click OK to save the settings.

Deletion settings

7.Click Subplan Schedule (calendar icon). In the pop-up window, arrange the Frequency, Daily Frequency, Duration, Start date and End date. Click OK to save.

Schedule Frequency

Now you can close maintenance plan settings window or use shortcut win+S to save this plan.

8. Right-click SQL Server Agent to Start it. Find the newly created plan in SQL Server Agent > Jobs. Right-click it and select Start Job at Step… to execute.

Start Job at Step

Generate Scripts to Copy Tables to Another Database

Tables are the basic SQL Server database objects used to store all the data in the database. If you only made minor changes to one single table, you may think a full backup is taking up space.

Actually, there’s no directly way to back up or restore table only in SQL Server. We can only achieve similar results in other ways. For example, copy the table to another database, or export the data in tables.

Note: For a table may depend on other tables via foreign key relationships, so please make sure your table is independent and complete before you start.

1. Launch SSMS and connect to your instance.

2. Expand Databases. Right-click the database name and select Tasks > Generate Scripts…

Select Generate Scripts

3. Select specific database objects, and check the tables you want to back up. Then click Next.

Check specific tables to backup

4. Select Save scripts to a specific location. And then click Advanced.

Click Advanced

5. Find Types of data to script option in the pop-up window, select Schema and data next to it. Click OK to save.

Select Schema and data

6. Select Open in new query window option below. Then click Next.

Open in new query window

7. Review your selections and click Next to save scripts.

Summary

8. Click Finish and backup to SSMS interface. You can see the generated script is in Query window.

Change the database name in the front line, to copy the table to another database. Or you will come to “Database already exist” error. In this way you can also copy the tables to another server.

Scripts generated

10. Click Execute on toolbar to copy these tables.

Best Alternative for SQL Server Management Studio

As you can see, when performing SQL Management Studio backups, some practical features are not available on the Express version, or old versions. So, if you are using the Express version, or if you want an easier, faster, and more efficient way to automatically back up your database regularly, AOMEI Centralized Backupper Database can be a very good alternative. 

  • Intuitive interface and simple operations: All essential SQL Server backup options are displayed in an intuitive task interface with modifiable task titles to differentiate. Once created each backup task will be listed separately for further operations, such as changing the settings and restoring.
  • Remotely backup another computer within LAN: AOMEI Centralized Backupper Database allows you to control all laptops, workstations, and servers within LAN from one single computer. You can remotely back up single or multiple databases on another computer, while monitoring the network speed and available disk space of these computers to avoid failures.
  • Remotely restore to another computer: When restoring, backup files will be automatically organized into a list by backup type and time. And you can also choose to restore them to your local sever, and another computer within LAN.
  • Encrypt databases in versions before SQL Server 2014: You can encrypt backup while creating, and no need to create and back up DMK and certificate in advance, the password is all needed. Support all SQL Server versions (including SQL Server Express and versions prior to 2014).

AOMEI Centralized Backupper Database is a great solution to save the time and reduce errors of operating individually on different computers. It is effeciency especially for enterprises.

Here’s a 30-day free trial for you:

Download Free Trial Win PCs & Servers
Secure Download

To properly use AOMEI Centralized Backupper Database Edition, especially if you want to manage multiple computers within LAN, you need to go to the Computers page first.

Click Install client program to download the Agent on client computers manually or remotely.

Install Client Program

For those computers which download client program manually, you need to request control over them for further management and operation.

Click Computers > Uncontrolled Computers to check single or multiple client computers, and click Request Control on the upper bar. A pop-up window will show on their computers, once all permissions are received, you can start backup and restore whenever you like.

Request Control

How to Automate Backups of Multiple Databases on Other Computers

Click Tasks > New Task and select SQL Server Backup to create a new backup task. There are 3 steps you need to follow.

SQL Server Backup

Step 1, click Add Computers to add the controlled client computer you want to back up with.

Add Computers

Step 2, click Add, you can select single or multiple SQL instances and databases you want to back up. Click OK to the next step.

Select databases

Step 3, select a location as the destination path. You can click Add Storage to add a share or NAS path as a storage end.

Select destination path

Besides, click Settings you can enable encryption, email notification, and compression for backups. To further save the space occupied by excessive backups, Centralized Backupper Database provides 2 compression levels, the higher the compression level you choose, the smaller the backup file will be.

You can encrypt databases in all versions of SQL Servers, including Express and versions before SQL Server 2014.

Enable Backup Encryption

Click Schedule next to Settings, you can select to run this task Once, Daily, Weekly, or Monthly. Each option you can select specific days and start time. You can also change it after.

Schedule settings

Click Advanced you can choose to perform Full or Differential Backup.

Advanced settings

After all these settings, you can click Start Backup to select Only create the task, or Create and execute the task. Once created, the tasks will be listed in Tasks separately for further edit or restore.

Summary

This article is a quick start guide to SQL Server Management Studio. It introduces how to perform SQL Management Studio backups of different types, how to back up single or multiple databases, back up to different locations, create automatic backup and deletion tasks, and back up tables.

However, as you can see, there are many essential backup features that are not available on Express or older versions. Therefore, I recommend you AOMEI Centralized Backupper Database Edition, the best alternative to SQL Server Management Studio.

AOMEI Centralized Backupper Database not only simplifies the backup operations, but also makes it easy to back up multiple databases. Most importantly, using it you can back up databases on another computer within LAN, or to restore backups to another server. It can effectively reduce management time and errors.