Hướng dẫn Restore database SQL Server 2014

 Hướng dẫn Backup, Restore database SQL Server

1.  Hướng dẫn backup database SQL Server

Mở SQL server

Chọn database cần backup

Chọn Backup 

Chọn lại đường dẫn mặc định của SQL Server.

Chọn OK

Backup hoàn tất.

2. Hướng dẫn restore  SQL server

Chọn database cần restore lại.

Chọn From database 

Chọn file backup

Chọn file backup và mục Options

Chọn ghi đè lên database cũ và chọn đường dẫn lưu trữ chính xác [thường để mặc định ổ đĩa c]

Chọn OK

Thực hiện restore lại hoàn tất.

Hướng dẫn backup restore database SQL server.

Chuyển đến nội dung chính

Trình duyệt này không còn được hỗ trợ nữa.

Hãy nâng cấp lên Microsoft Edge để tận dụng các tính năng mới nhất, bản cập nhật bảo mật và hỗ trợ kỹ thuật.

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

• Bài viết
• 11/18/2022
• 2 phút để đọc

Trong bài viết này

Applies to: SQL Server [all supported versions]

In this quickstart, you'll create a new database, take a full 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'll need:

• SQL Server
• SQL Server Management Studio [SSMS]

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.

   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.

Take a backup

To take a backup of your database, follow these steps:

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 that the path for your backup is correct. If you need to change the path, 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.

Alternatively, you can run the following Transact-SQL command to back up your database. The path may 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

To read more about the different backup options, see BACKUP [Transact-SQL].

Restore a backup

To restore your database, follow these steps:

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

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.

Alternatively, you can run the following Transact-SQL script to restore your database. The path may 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

See also

• Restore SQL Server databases on an Azure VM - Azure Backup
• Quickstart: Restore a backup [SSMS] - Azure SQL Managed Instance
• Restore a database from a backup - Azure SQL Database & SQL Managed Instance

Next steps

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