By: Rajendra Gupta | Updated: 2022-07-05 | Comments (2) | Related: 1 | 2 | 3 | > SQL Server 2022
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:
- Download the backups onto a local directory and use it for database restoration
- 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.
Review IAM policy and save changes.
It saves the policy changes and displays the following message.
Suppose I have the following SQL database backup file, demodb.bak, stored in the S3 bucket.
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;
If the database already exists and you try to restore you will get this message.
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 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.
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;
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;
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;
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;
As shown below, the [demodb] database is in Restoring mode after restoring the full backup.
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;
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;
Refresh the database to verify that the restored database is online and accessible.
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 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;
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.
Next Steps
- Check out these other SQL Server 2022 articles.
About the author
Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2022-07-05
