Restoring from Backup Files to a Self-Built SQL Server Database Using SSMS

RDS for SQL Server backups include data backups and incremental backups (log backups) in the .bak format. The .bak files can be used to restore data to a self-managed database.

Prerequisites

You have downloaded the .bak files from the cloud to a local path of a self-managed database.

Restoring a Data Backup

Use the Microsoft official tool SQL Server Management Studio (SSMS) to log in to a self-managed database.

Figure 1 Logging in to a self-managed database
Right-click Databases, and choose Restore Database from the shortcut menu.

Figure 2 Selecting a database
Select Device, add the .bak backup file, and click OK.

Figure 3 Adding a backup file
Select the database to be restored. You can select the source database from the Database drop-down list box in the Source area and change the name of the destination database in the Destination area.

Figure 4 Selecting the source and destination databases
Click OK.

Figure 5 Successful restoration

Restoring Incremental Backups (Log Backups)

Before restoring log backups, ensure that the data backup has been restored and the database is in the Restoring state. Log backups must be consecutive. You must restore a database according to its backup sequence. If any backup is missing, the restoration cannot be completed.

Restore the data backup by referring to 1 to 4.
Click Option and set Recovery state to RESTORE WITH NORECOVERY.

Figure 6 Setting Recovery state
Check that the database status is Restoring.

Figure 7 Checking the restoration status
Right-click the database and choose Tasks > Restore > Transaction Log from the shortcut menu.

Figure 8 Selecting a database
Select From device and add the backup file to be restored.

Figure 9 Adding a backup file
If the backup file is not the last incremental backup file and you need to restore other incremental backup files, change the value of Recovery state to RESTORE WITH NORECOVERY. Otherwise, select RESTORE WITH RECOVERY for Recovery state and click OK.

Figure 10 Restoring log backups
If there are any other incremental backups that need to be restored, repeat 4 to 6 until the last log backup is restored.

FAQ

Q: Can data be restored if there is only the rdsadmin database but no target database in the downloaded .bak file?

A: Yes. The solution is as follows:

The downloaded backup file contains two databases. The first database is rdsadmin, and the second database is the target database, for example, test.
Query backup file header information.
```
restore headeronly from disk='Local path of the .bak file'
```
Figure 11 Querying backup file information
Query information about the databases that were backed up.
```
restore filelistonly from disk='Local path of the .bak file'
```
By default, only information about the first database is read.

Figure 12 Querying information about the databases that were backed up
To read the second or third database, add with file. The value of with file is that of position in the command output of restore headeronly.
```
restore filelistonly from disk='Local path of the .bak file' with file=2
```
Figure 13 Querying information about other databases
Restore data.
Figure 14 Restoring data
```
USE [master]
RESTORE DATABASE [@dbname]
FROM DISK='@path'
WITH FILE= @file
MOVE '@logicalname1' TO '@filepath1'
MOVE '@logicalname2' TO '@filepath2'
NOUNLOAD, STATS=5
GO
```
- @dbname: Database name.
- @path: Full backup file path.
- @file: Location of the database in the .bak file, that is, the value of position in the command output of restore headeronly.
- @logicalname1: Logical name in the backup file and the file path of the new database. Its value is that of LogicalName in the command output of restore filelistonly.
- @filepath1: Local path for storing physical files.
- @logicalname2: The same as @logicalname1.
- @filepath2: The same as @filepath1.
Run the SQL statements above based on the header information obtained in 2 to restore the data.