Home Search

DriveWorks Pro 22
How To: Backup a SQL Server Database (KB13022701)

Send Feedback

How To: Backup a SQL Server Database

This document will show you how to use SQL Server Management Studio to backup a database.

Applies To:

  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2005 Express
  • Microsoft SQL Server 2008
  • Microsoft SQL Server 2008 Express
  • Microsoft SQL Server 2008 R2
  • Microsoft SQL Server 2008 R2 Express
  • Microsoft SQL Server 2012
  • Microsoft SQL Server 2012 Express
  • Microsoft SQL Server 2014
  • Microsoft SQL Server 2014 Express
  • Microsoft SQL Server 2016
  • Microsoft SQL Server 2016 Express

Prerequisites

To perform the steps in this document you will need one of the SQL Server Management Studio editions mentioned in the Applies To section. The guides below use the SQL Server Management Studio version 2005, 2012 and 2017 as well as SQL Server Express editions 2005, 2012 and 2016, which is freely available from the link given in the Applies To section.

To connect to SQL Server 2005 or SQL Server 2005 Express you can use any of the listed (or later) versions of the Management Studio.
To connect to SQL Server 2008 or SQL Server 2008 Express you will need one of the 2008 editions (or later) of the Management Studio.
To connect to SQL Server 2008 RC or SQL Server 2008 RC Express you will need one of the 2008 RC editions (or later) of the Management Studio.
To connect to SQL Server 2012 or SQL Server 2012 Express you will need one of the 2012 editions (or later) of the Management Studio.
To connect to SQL Server 2014 or SQL Server 2014 Express you will need one of the 2014 editions (or later) of the Management Studio.
To connect to SQL Server 2016 or SQL Server 2016 Express you will need one of the 2016 editions (or later) of the Management Studio.

Steps

If you are working with a database on a server managed by an IT department, make sure you consult your IT department before following any of these steps. They may already have particular backup policies that you will need to adhere to.

If you are unsure and have an IT department, you should consult them first.

Expand the appropriate section for the version of SQL Server you are using:

SQL Server 2005 and 2008
Step 1Start SQL Server Management studio and select the server to which you want to connect.
Step 2Once you are connected, you should see a screen similar to the one on the left.
Step 3Expand the "Databases" folder in the "Object Explorer" and select the database you wish to backup.
Step 4Right click the selected database, open the "Tasks" submenu, and choose the "Backup..." option.
Step 5

A dialog will appear with some options.

  • Make sure that the "Backup type" option is set to "Full".
  • Also, ensure that the expiry option is set to "After 0 days".
  • In this list at the bottom of the dialog, select any backup sets that are already defined, and click "Remove".
Step 6

When you have performed the steps above, your dialog should resemble the one on the left.

Click "Add.." to the right of the destination list.

Step 7

Select the File Name option and click the button to the right of the destination field ("...")

In the Locate Database Files dialog, browse to the location to store the backup and in the File Name field enter a name for the backup followed by .Bak

For example MySharedGroup.Bak

If you are connected to a remote server, the backup location will be on the server, not your local machine.

Click "OK"

Step 8To start the backup, click "OK"
Step 9When the backup is complete, a message similar to the one on the left will appear.
SQL Server 2012 and 2014
Step 1Start SQL Server Management studio and select the server to which you want to connect.
Step 2Once you are connected, you should see a screen similar to the one on the left.
Step 3Expand the "Databases" folder in the "Object Explorer" and select the database you wish to backup.
Step 4Right click the selected database, open the "Tasks" submenu, and choose the "Backup..." option.
Step 5

A dialog will appear with some options.

  • Make sure that the "Backup type" option is set to "Full".
  • Also, ensure that the expiry option is set to "After 0 days".
  • In this list at the bottom of the dialog, select any backup sets that are already defined, and click "Remove".
Step 6

When you have performed the steps above, your dialog should resemble the one on the left.

Click "Add.." to the right of the destination list.

Step 7

Select the File Name option and click the button to the right of the destination field ("...")

In the Locate Database Files dialog, browse to the location to store the backup and in the File Name field enter a name for the backup followed by .Bak

For example MySharedGroup.Bak

If you are connected to a remote server, the backup location will be on the server, not your local machine.

Click "OK"

Step 8To start the backup, click "OK"
Step 9When the backup is complete, a message similar to the one on the left will appear.
SQL Server 2016 and newer
Step 1Start SQL Server Management studio and select the server to which you want to connect.
Step 2Once you are connected, you should see a screen similar to the one on the left.
Step 3Expand the "Databases" folder in the "Object Explorer" and select the database you wish to backup.
Step 4Right click the selected database, open the "Tasks" submenu, and choose the "Backup..." option.
Step 5

A dialog will appear with some options. Make sure you are on the General tab.

  • Make sure that the "Backup type" option is set to "Full".
  • In the list at the bottom of the dialog, select any backup sets that are already defined, and click "Remove".
Step 6Within the Backup Options tab ensure that the expiry option is set to "After 0 days" .
Step 7

When you have performed the steps above, your dialog should resemble the one on the left.

Click "Add.." to the right of the destination list.

Step 8

Select the File Name option and click the button to the right of the destination field ("...")

In the Locate Database Files dialog, browse to the location to store the backup and in the File Name field enter a name for the backup followed by .Bak

For example MySharedGroup.Bak

If you are connected to a remote server, the backup location will be on the server, not your local machine.

Click "OK"

Step 9To start the backup, click "OK"
Step 10When the backup is complete, a message similar to the one on the left will appear.

Knowledge Base Article Ref:KB13022701
© DriveWorks Ltd 2024 - All rights reserved.
Privacy Policy | Terms & Conditions