CODEWITHSUNDEEP
sql-serveramazon-web-servicesamazon-rds
gotmike
gotmike

Reputation: 1615

How to restore SQL Server .bak in Amazon RDS

Trying to restore a .bak file from S3.

I have tried on SQL server express and web. Versions 2012 through 2017.

I have tried with t2.micro and t2.small.

Here is what my task_info says:

 [2018-03-21 10:20:22.107] Task execution has started.
 [2018-03-21 10:20:22.280] myDb.bak: Completed processing 17.27% of S3 chunks.
 [2018-03-21 10:20:22.327] myDb.bak: Completed processing 34.48% of S3 chunks.
 [2018-03-21 10:20:22.357] 9 percent processed.
 [2018-03-21 10:20:22.373] myDb.bak: Completed processing 51.68% of S3 chunks.
 [2018-03-21 10:20:22.450] myDb.bak: Completed processing 68.88% of S3 chunks.
 [2018-03-21 10:20:22.500] 13 percent processed.
 [2018-03-21 10:20:22.513] myDb.bak: Completed processing 86.09% of S3 chunks.
 [2018-03-21 10:20:22.547] 18 percent processed.
 [2018-03-21 10:20:22.560] myDb.bak: Completed processing 100% of S3 chunks.
 [2018-03-21 10:20:22.640] 23 percent processed.
 [2018-03-21 10:20:22.653] Read on "F470E52D-DEA8-4EB1-B0AC-6F03D091E223" failed: 30(The system cannot read from the specified device.)
 [2018-03-21 10:20:22.653] RESTORE DATABASE is terminating abnormally.
 [2018-03-21 10:22:22.027] myDb.bak: S3 processing completed successfully
 [2018-03-21 10:22:22.027] Aborted the task because of a task failure or a concurrent RESTORE_DB request.
 [2018-03-21 10:22:22.090] myDb.bak: S3 processing has been aborted
 [2018-03-21 10:22:22.107] Unable to retrieve family guid for database name: myDbName

I have searched all over the web and no luck.

the process is outlined here: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.html

Any help would be great!

UPDATE

I noticed on this page: https://aws.amazon.com/blogs/database/migrating-microsoft-sql-server-enterprise-workloads-to-amazon-rds-part-1/

it says:

You might see this error when the database you have restored contains a large log file and SQL Server is performing crash recovery. Shrink the log file on premises before creating a backup, uploading it to S3, and issuing the restore task.

Trying this now...

Upvotes: 2

Views: 2641

Answers (1)

gotmike
gotmike

Reputation: 1615

okay, i found a workaround, but i wouldn't actually say i found the problem.

i was creating the backup in a hosting environment using a cpanel. turns out that was running sql server 2012. i'm not sure if that matters.

while i could get it to restore locally, it wouldn't work in RDS.

so what i did was install the latest version of SQL Server 2017 on my desktop, restore to that machine, then do a shrink of the database and files.

after that, i did a new backup on my desktop and then uploaded to s3 and ran the restore commands.

this time it worked. no idea why.

there must be something weird about the remote backup process.

hope this helps someone else.

Upvotes: 1

Related Questions