Skip to content

quickstart-backup-restore-database.md

Latest commit

 

History

History
158 lines (112 loc) · 5.89 KB

quickstart-backup-restore-database.md

File metadata and controls

158 lines (112 loc) · 5.89 KB
title Quickstart: Back Up and Restore a Database by Using SSMS
titleSuffix SQL Server
description Learn how to create a new database, create a backup of the database, and restore the backup in SQL Server by using SSMS.
author MashaMSFT
ms.author mathoma
ms.reviewer randolphwest, markingmyname
ms.date 08/21/2025
ms.update-cycle 365-days
ms.service sql
ms.subservice backup-restore
ms.topic quickstart
ms.custom
sfi-image-nochange

Quickstart: Backup and restore a SQL Server database with SSMS

[!INCLUDE SQL Server]

In this quickstart, you create a new database, create a full backup of it, and then restore it.

For more detailed information, see Create a full database backup and Restore a database backup using SSMS.

Prerequisites

Create a test database

  1. Open SQL Server Management Studio (SSMS) and connect to your SQL Server instance.

  2. Open a New Query window.

  3. Create your test database by using the following Transact-SQL (T-SQL) code.

    USE [master];
GO

CREATE DATABASE [SQLTestDB];
GO

USE [SQLTestDB];
GO
CREATE TABLE SQLTest (
    ID INT NOT NULL PRIMARY KEY,
    c1 VARCHAR(100) NOT NULL,
    dt1 DATETIME NOT NULL DEFAULT GETDATE()
);
GO

USE [SQLTestDB];
GO

INSERT INTO SQLTest (ID, c1) VALUES (1, 'test1');
INSERT INTO SQLTest (ID, c1) VALUES (2, 'test2');
INSERT INTO SQLTest (ID, c1) VALUES (3, 'test3');
INSERT INTO SQLTest (ID, c1) VALUES (4, 'test4');
INSERT INTO SQLTest (ID, c1) VALUES (5, 'test5');
GO

SELECT * FROM SQLTest;
GO

  4. Refresh the Databases node in Object Explorer to see your new database.

Create a backup

To create a backup of your database, follow these steps by using [!INCLUDE ssmanstudiofull-md] or [!INCLUDE tsql-md]:

SSMS

  1. Open SQL Server Management Studio (SSMS) and connect to your SQL Server instance.
  2. Expand the Databases node in Object Explorer.
  3. Right-click the database, hover over Tasks, and then select Back up....
  4. Under Destination, confirm that the path for your backup is correct. If you need to change the path, select Remove to remove the existing path, and then select Add to type in a new path. You can use the ellipsis button (...) to navigate to a specific file.
  5. Select OK to create a backup of your database.

:::image type="content" source="media/quickstart-backup-restore-database/backup-db-ssms.png" alt-text="Screenshot that shows the SSMS menu options for creating a backup." lightbox="media/quickstart-backup-restore-database/backup-db-ssms.png":::

T-SQL

Alternatively, you can run the following Transact-SQL command to back up your database. The path might be different on your computer.

USE [master];
GO

BACKUP DATABASE [SQLTestDB]
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\SQLTestDB.bak'
WITH NOFORMAT, NOINIT,
NAME = N'SQLTestDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;
GO

For more information about backup options, see BACKUP.

Restore a database

SSMS

To restore your database, follow these steps:

  1. Open SQL Server Management Studio (SSMS) and connect to your SQL Server instance.

  2. Right-click the Databases node in Object Explorer and select Restore Database....

    :::image type="content" source="media/quickstart-backup-restore-database/restore-db-ssms1.png" alt-text="Screenshot that shows the SSMS menu options for restoring a database.":::

  3. Select Device, and then select the ellipsis button (...) to locate your backup file.

  4. Select Add and navigate to the location of your .bak file. Select the .bak file, and then select OK.

  5. Select OK to close the Select backup devices dialog.

  6. Select OK to restore your database.

    :::image type="content" source="media/quickstart-backup-restore-database/restore-db-ssms2.png" alt-text="Screenshot that shows the steps for restoring a database." lightbox="media/quickstart-backup-restore-database/restore-db-ssms2.png":::

T-SQL

Alternatively, you can run the following Transact-SQL script to restore your database. The path might be different on your computer.

USE [master];
GO
RESTORE DATABASE [SQLTestDB]
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\SQLTestDB.bak' WITH  FILE = 1, NOUNLOAD, STATS = 5;
GO

Clean up resources

Run the following Transact-SQL command to remove the database you created, along with its backup history in the msdb database:

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'SQLTestDB'
GO

USE [master];
GO
DROP DATABASE [SQLTestDB];
GO

Related content