SQL Server 2022 Restore a database from AWS S3 bucket - Part 3

By:   |   Updated: 2022-07-05   |   Comments   |   Related: 1 | 2 | 3 | More > SQL Server 2022


Please do not scroll away - stay informed.
Dear Database Professional,

Did you know that MSSQLTips.com publishes new SQL Server content on a daily basis as well as offers free webinars and tutorials?

We know your day is hectic and you don't necessarily have time to research new topics and solutions every day, but we can keep you informed.

Take 30 seconds to register for our newsletter and look for free educational content to help you grow your career. >> REGISTER HERE <<

Thank you,
Greg Robidoux and Jeremy Kadlec (MSSQLTips.com Co-Founders)
Problem

SQL Server 2022 lets you create native backups directly to an AWS S3 bucket. In part 1 and part 2 we explored how you can configure the AWS S3 bucket. Further and create SQL database backups. In this tutorial, we look at how to restore SQL Server backups stored in an S3 bucket.

Solution

Database backup and restoration are frequent tasks for a database administrator. If you store your backups in cloud storage, you can restore backups in two ways:

  1. Download the backups onto a local directory and use it for database restoration
  2. Restore the database directly from the backup files stored in the AWS S3 bucket

In the first approach, you need to download the backup to a local drive and it requires drive space to accommodate the backup. Similarly, in case of multiple backup files, you need to download all backup files. Therefore, restoring a backup directly from the backup files stored in the S3 bucket can make the restore much simpler.

Requirements

  • AWS subscription with S3 bucket configuration
  • SQL Server 2022 (I used cumulative version CTP 2.0 for my testing)
  • SQL Credentials with AWS access ID and secret key
  • Existing SQL Database backups

Steps to restore SQL database backup from AWS S3 bucket

SQL Server 2022 CTP 2.0 requires the following permissions on the S3 bucket to restore the SQL database from the backup stored on the AWS S3 bucket.

  • ListBucket
  • Readonly

Previously, in part 1, we assigned ListBucket and WriteOnly permissions in the AWS custom policy. Let's connect to the AWS portal and edit the existing policy. In part 2, I created the policy named [SQL2022backuppolicy]. Therefore, edit the policy and add GetObject from the actions menu. This grants permission to retrieve objects from Amazon S3.

aws policy settings

Review IAM policy and save changes.

aws policy settings

It saves the policy changes and displays the following message.

aws policy settings updated

Suppose I have the following SQL database backup file, demodb.bak, stored in the S3 bucket.

aws bucket contents

Before we restore a database, let's drop the source database with the following T-SQL statement, so we can do an easy restore.

drop database demoDb
Go

The restore database command for restoring the database from the object storage endpoint location is below. We specify the backup file location similar to the path specified in the BACKUP DATABASE statement in the command.

RESTORE DATABASE demodb
FROM URL = 's3://sqlserver2022.s3.ap-south-1.amazonaws.com/fullbackups/demodb.bak'
WITH STATS = 10;
restore database command

If the database already exists and you try to restore you will get this message.

restore database command

To fix this, you can use REPLACE to overwrite the SQL database.

RESTORE DATABASE demodb
FROM URL = 's3://sqlserver2022.s3.ap-south-1.amazonaws.com/fullbackups/demodb.bak'
WITH REPLACE 
    ,STATS = 10;
restore database command

Restore SQL database from backup file striped across multiple files in AWS S3 bucket

As stated in part 2, we can split a backup file into 64 URLs to accommodate a big database backup that can support the maximum supported size of a file is 10,000 parts * MAXTRANSFERSIZE * URLs. The MAXTRANSFERSIZE can be between 5 to 20 MB (10 MB default).

Suppose we used three URLs to split a SQL database backup into the S3 bucket, as shown below.

aws bucket contents

We need to specify all required backup files in the FROM URL section to restore the database.

RESTORE DATABASE demodb
FROM    
     URL = 's3://sqlserver2022.s3.ap-south-1.amazonaws.com/fullbackups/demodb1.bak'
    ,URL = 's3://sqlserver2022.s3.ap-south-1.amazonaws.com/fullbackups/demodb2.bak'
    ,URL = 's3://sqlserver2022.s3.ap-south-1.amazonaws.com/fullbackups/demodb3.bak'
WITH STATS = 10;
restore database command

Custom MAXTRANSFERSIZE with SQL database restore

The default value for the MAXTRANSFERSIZE parameter is 10 MB. Like the backup database statement, we can specify a custom value between 5 MB to 20 MB while restoring a database.

The following RESTORE DATABASE statement uses the MAXTRANSFERSIZE value as 20 MB ( 20971520 bytes).

RESTORE DATABASE demodb
FROM    
     URL = 's3://sqlserver2022.s3.ap-south-1.amazonaws.com/fullbackups/demodb1.bak'
    ,URL = 's3://sqlserver2022.s3.ap-south-1.amazonaws.com/fullbackups/demodb2.bak'
    ,URL = 's3://sqlserver2022.s3.ap-south-1.amazonaws.com/fullbackups/demodb3.bak'
WITH MAXTRANSFERSIZE = 20971520
    ,REPLACE
    ,STATS = 10;
restore database command

Restoring a full, differential, and log backup from the AWS S3 bucket

Suppose you took a full backup followed by a differential and transaction log backup and stored it in the AWS S3 bucket.

You use the following t-SQL statement to take full, differential, and t-log backups.

BACKUP DATABASE demodb
TO   URL = 's3://sqlserver2022.s3.ap-south-1.amazonaws.com/fullbackups/demodb.bak'
WITH FORMAT 
    ,STATS = 10
    ,COMPRESSION;

BACKUP DATABASE demodb
TO   URL = 's3://sqlserver2022.s3.ap-south-1.amazonaws.com/fullbackups/demodb_diff.bak'
WITH DIFFERENTIAL
    ,FORMAT 
    ,STATS = 10
    ,COMPRESSION;
 
BACKUP LOG DEMODB
TO   URL = 's3://sqlserver2022.s3.ap-south-1.amazonaws.com/fullbackups/demodb_tran.trn'
WITH STATS = 10
    ,COMPRESSION;
aws bucket contents

Let's restore these backup files onto an on-premises SQL instance. As you might be aware, the restore sequence of a SQL database is a full backup, differential followed by log backup.

First, let's restore the full database using the file demodb.bak with NORECOVERY.

RESTORE DATABASE demodb
FROM    
     URL = 's3://sqlserver2022.s3.ap-south-1.amazonaws.com/fullbackups/demodb.bak'
WITH MAXTRANSFERSIZE = 20971520
    ,REPLACE
    ,NORECOVERY
    ,STATS= 10;
restore database command

As shown below, the [demodb] database is in Restoring mode after restoring the full backup.

ssms object explorer

Now, we can apply the differential backup on top of the full backup. The database remains in the Restoring mode after the restoration.

RESTORE DATABASE demodb
FROM    
     URL = 's3://sqlserver2022.s3.ap-south-1.amazonaws.com/fullbackups/demodb_diff.bak'
WITH MAXTRANSFERSIZE = 20971520
    ,REPLACE
    ,NORECOVERY
    ,STATS= 10;
restore database command

Let's restore the t-log backup and recover the database.

RESTORE DATABASE demodb
FROM    
     URL = 's3://sqlserver2022.s3.ap-south-1.amazonaws.com/fullbackups/demodb_tran.trn'
WITH MAXTRANSFERSIZE = 20971520
    ,STATS= 10;
restore database command

Refresh the database to verify that the restored database is online and accessible.

ssms object explorer

Restore encrypted database backup from the S3 bucket

Part 2 explored taking an encrypted backup of a SQL database. If the backup is encrypted, we can restore it similar to a regular database. However, we need to restore the backup certificate if you are restoring the backup on a different SQL instance than the source instance.

For this example we will restore the database on the same SQL instance where the backup was created, so we don't need to restore the backup credential.

RESTORE DATABASE demodb
FROM    
     URL = 's3://sqlserver2022.s3.ap-south-1.amazonaws.com/fullbackups/demodb_encrypt.bak'
WITH MAXTRANSFERSIZE = 20971520
    ,STATS = 10
    ,REPLACE;
restore database command

Restore encrypted database backup from the S3 bucket and move data files

Suppose you want to restore the SQL database by moving the data and log files to a different drive than the source database directory. As shown below, the T-SQL statement moves the files to the C:\Temp directory. To move the data and log file, you can use the WITH MOVE statement and specify the new directory.

RESTORE DATABASE demodb
FROM    
     URL = 's3://sqlserver2022.s3.ap-south-1.amazonaws.com/fullbackups/demodb_encrypt.bak'
WITH MOVE 'demoDb' TO 'C:\Temp\demoDb.mdf'
    ,MOVE 'demoDb_log' TO 'C:\Temp\demoDb_log.ldf'
    ,MAXTRANSFERSIZE = 20971520
    ,STATS= 10;
restore database command

Once the database is restored from the S3 bucket backup, you can connect to it and run the stored procedure sp_helpfile to verify the data and log file locations.

query results
Next Steps



Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights




get scripts

next tip button



About the author
MSSQLTips author Rajendra Gupta Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

View all my tips


Article Last Updated: 2022-07-05

Comments For This Article





download














get free sql tips
agree to terms