I'm facing a problem restoring a SQL Server database on Amazon RDS. I've set up a new instance with 150 GB of gp3 storage, which is smaller than the previous one. However, my backup file in S3 is only about 69 GB, so I thought 150 GB would be more than sufficient. I'm using the RDS-native procedures: `rds_backup_database` and `rds_restore_database`. When I check the storage on my original RDS instance, it shows: Total Space Reserved: 1,095.77 GB and Space used: 68.11 GB. Do I need to shrink the database files before backing up? Is SQL Server reserving the full original sizes of the MDF and LDF files during restore, even if there's little actual data?
1 Answer
Yes, that's how SQL Server functions. The actual size of your data doesn't affect the space SQL Server reserves. Even if your database only has 69 GB, the restore operation can require space based on the configured file sizes for your data and log files rather than the actual utilized space.
Just to clarify, when I run the `EXEC sp_spaceused`, it shows the database size as around 1.1 TB with about 1 TB unallocated. So, does that mean I need at least 1.1 TB of storage for the restore?