Skip to content

quickstart-backup-restore-database.md

Latest commit

 

History

History
127 lines (97 loc) · 4.57 KB

quickstart-backup-restore-database.md

File metadata and controls

127 lines (97 loc) · 4.57 KB
title Quickstart: Back up & restore database
titleSuffix SQL Server
description This quickstart shows how to back up and restore a SQL Server database on premises.
author MashaMSFT
ms.author mathoma
ms.custom seo-lt-2019
ms.date 12/17/2019
ms.topic conceptual
ms.prod sql
ms.technology backup-restore
ms.prod_service backup-restore
ms.assetid

Quickstart: Backup and restore a SQL Server database on-premises

[!INCLUDEappliesto-ss-xxxx-xxxx-xxx-md]

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

For a more detailed how-to, see Create a full database backup and Restore a backup using SSMS.

Prerequisites

To complete this quickstart, you will need the following:

Create a test database

  1. Launch SQL Server Management Studio (SSMS) and connect to your SQL Server instance.
  2. Open a New Query window.
  3. Run the following Transact-SQL (T-SQL) code to create your test database. Refresh the Databases node in Object Explorer to see your new database.
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

Take a backup

To take a backup of your database, do the following:

  1. Launch 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 select Back up....
  4. Under Destination, confirm the path for your backup is correct. If you need to change this, select Remove to remove the existing path, and then Add to type in a new path. You can use the ellipses to navigate to a specific file.
  5. Select OK to take a backup of your database.

Take SQL backup

Alternatively, you can run the following Transact-SQL command to back up your database:

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

Restore a backup

To restore your database, do the following:

  1. Launch 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....

    Restore a database

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

  4. Select Add and navigate to where your .bak file is located. Select the .bak file and then select OK.

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

  6. Select OK to restore the backup of your database.

    Restore the database

Alternatively, you can run the following Transact-SQL script to restore your database:

USE [master]
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]
DROP DATABASE [SQLTestDB]
GO

See more

Back up and restore overview Back up to URL Create a full backup Restore a database backup