RDS SQL Server EE native backup succeeds, restore fails

Question

Hi all,

I'm currently trying to duplicate a SQL Server EE database following the instructions here: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.html

The original db looks like this:

```
1> use OLDDB
2> exec sp_spaceused
3> go
Changed database context to 'OLDDB'.
database_name                                                                                                                    database_size      unallocated space 
-------------------------------------------------------------------------------------------------------------------------------- ------------------ ------------------
OLDDB                                                                                                                           16796.31 MB        5271.73 MB        
reserved           data               index_size         unused            
------------------ ------------------ ------------------ ------------------
1870552 KB         1685304 KB         165616 KB          19632 KB       
```

I also have:

```
D:\rdsdbdata\DATA\OLDDB.mdf = 6GB
D:\rdsdbdata\DATA\OLDDB_log.ldf = 9GB   
```

The backup that lands in the S3 bucket is ~ 1.8GB and completes without error, but the restore task doesn't and ends in the state:

```
          5 RESTORE_DB_NORECOVERY                              NEWDB                                                                                                                                  0              1 ERROR                                              
[2023-09-27 15:44:22.387] Aborted the task because of a task failure or a concurrent RESTORE_DB_NORECOVERY request.
[2023-09-27 15:44:22.453] Task has been aborted
[2023-09-27 15:44:22.457] Maximum supported database size on SQL Server Express edition is: 2023-09-27 15:44:22.457 2023-09-27 15:43:36.700 arn:aws:s3:::mybucket
```

The RDS instance (source and target) is a db3.t.large (2CPU/8GB) with 200GB, RDS version 15.00.4198.2.v1, my backup command is:

```
exec msdb.dbo.rds_backup_database @source_db_name='OLDDB',
  @s3_arn_to_backup_to='arn:aws:s3:::mybucket/olddb.bak', 
  @overwrite_s3_backup_file=1, @type='FULL', @number_of_files=1;
```

and restore:

```
exec msdb.dbo.rds_restore_database @restore_db_name='NEWDB',
  @s3_arn_to_restore_from='arn:aws:s3:::mybucket/olddb.bak', 
  @with_norecovery=1, @type='FULL';
```

I went through the troubleshooting steps in https://repost.aws/knowledge-center/rds-sql-server-fix-native-backup-restore, but I can't find any obvious mistakes in my configuration, policy and policy-assignments all look ok.

Am I running into some limitation (due to EE) here or am I doing something wrong?

Thanks,

Marc

Edit: FWIW, I can backup and restore a much smaller database on the same instance using the exact same process without a problem.

adrian-AWS · Accepted Answer

You're running into a limitation for Express edition. 
The actual error message is 
```
[2023-09-28 10:36:23.137] Aborted the task because of a task failure or a concurrent RESTORE_DB request. 
[2023-09-28 10:36:23.283] Task has been aborted 
[2023-09-28 10:36:23.290] Maximum supported database size on SQL Server Express edition is: 10 GB.
```

The Microsoft [documentation](https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2019?view=sql-server-ver16#Cross-BoxScaleLimits) makes that clear as well.

Truncate the transaction log and shrink it so that both the Data and Log files don't go over 10GB and take a new backup.

Marc · Answer

Thanks Adrian. It's what I suspected, but I wasn't sure, because I found some sources that said the 10GB limit refers to the database only, not to the log. I asked the db owner if this is how we want to proceed, I'll report back with the result as soon as I know.

RDS SQL Server EE native backup succeeds, restore fails

Relevant content