CODEWITHSUNDEEP
sql-serveramazon-web-servicesamazon-s3
chuseuiti
chuseuiti

Reputation: 813

RESTORE DATABASE RDS - MS SQL - 0% complete

Im trying to restore/create a MS SQL database from a .bak file of another MS SQL database (versions 2014), however from SQL benchmark I can see that my task is never done.

I followed this documentation and the one from AWS:

https://trailheadtechnology.com/blog/restoring-a-sql-server-backup-to-amazon-rds

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.html#SQLServer.Procedural.Importing.Native.Using

Steps:

  1. Create the db with permissions to S3 for data restoring.
  2. Install SQL benchmark Linux and Driver
  3. Connect to the database
  4. Upload data to S3

  5. RESTORE:

    exec msdb.dbo.rds_restore_database 
    @restore_db_name='DB_test', 
    @s3_arn_to_restore_from='arn:aws:s3:::bucket/backup.bak';

Then it creates the TASK and hangs there..I had wait for 30 min and the status is always 0% complete...no errors

PD. My user is the creator of the database, the one given to AWS at the creation through the console.

Upvotes: 4

Views: 1451

Answers (2)

Vicente Quintans
Vicente Quintans

Reputation: 1416

This happened to me with SQL Wrorkbench and driver sqljdbc4.jar.

After waiting for some time with the task lifecycle in CREATED status I executed a COMMIT and inmediately the task changed from CREATED to IN_PROGRESS.

I think that the default behaviour of newer drivers or other tools is to "autocommit" and that is the real reason it works in SQL Server Management Studio.

Upvotes: 1

Kostis
Kostis

Reputation: 41

I had the same problem recently trying to restore a .bak dump from S3 into an RDS SQL Server 2008 R2 using SQL Workbench/J (did not try SSMS)

I finally made it work by using the Microsoft JDBC Driver 6.0 for SQL Server with support for the JDBC 4.2 API (included in the Sqljdbc42.jar which requires a JRE of 8 and supports the JDBC 4.2 API)

AWS docs are unfortunately inaccurate in this subject as they propose to use Microsoft JDBC Drivers 4.1 (Preview) or 4.0 for SQL Server with SQL Workbench/J http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ConnectToMicrosoftSQLServerInstance.html

Upvotes: 2

Related Questions