By using AWS re:Post, you agree to the AWS re:Post Terms of Use
AWS re:Postre:Post

Restore verify failed using S3 url from Azure VM to AWS S3 bucket

0

Hi Team,

I am trying to run restore verify command using S3 url but getting an error, below are the steps and error we are getting :

  1. Create S3 bucket and upload backup files.
  2. Create IAM policy which has list, get and put object permissions on S3 bucket.
  3. Create IAM user and attached the IAM policy.
  4. Create bucket policy which allow the access on bucket folder and objects to IAM user.
  5. We have installed TLS 1.2 on Azure Virtual machine ( from where we are running restore command)

Below is the command I am running on azure Virtual machine through SSMS and error I am getting:

RESTORE VERIFYONLY FROM URL = 'https://bucket_name/Folder/file_name.bak' WITH CREDENTIAL = 's3://bucket_name/Folder';

Msg 3271, Level 16, State 1, Line 6 A nonrecoverable I/O error occurred on file " https://bucket_name/Folder/file_name.bak:" Backup to URL received an exception from the remote endpoint. Exception Message: Error occurred while referencing Blob in remote storage. Msg 3013, Level 16, State 1, Line 6 VERIFY DATABASE is terminating abnormally.

Note: I am able to access the S3 bucket from same server using AWS CLI interface.

Topics
StorageSecurity, Identity, & Compliance
Tags
StorageIAM Policies
Language
English
harry rajpal
asked a month ago39 views
1 Answer
0

The S3 URLs used in the T-SQL statements should start with s3:// instead of https:// to indicate the protocol in the URLs.

Looking at the examples in this Microsoft documentation article: https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url-s3-compatible-object-storage?view=sql-server-ver16#examples, it seems you should first associate the AWS credentials with the S3 path containing the backups with a CREATE CREDENTIAL statement. Once that's done, simply specify the URL parameter, containing the s3://... style URL, to the RESTORE statement. SQL Server should identify that credentials exist for the S3 URL and use them automatically to access the objects.

EXPERT
Leo K
answered a month ago
  • harry rajpal
    a month ago

    I haven’t taken backup using url. I have uploaded the backups from my local file system to s3 bucket. Then, I created the credentials on target server and performed the restore. Regarding using the s3 instead of https, as per backup/restore syntax, backup path or url always starts with https.

  • Leo K EXPERT
    a month ago

    The examples here: https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url-s3-compatible-object-storage?view=sql-server-ver16#restore-from-url all use regular s3:// URLs, which they should be also in a general S3 context. Where are you seeing URLs starting with https://?

  • harry rajpal
    a month ago

    I have tried with url s3:// and getting below error:

    Msg 3201, Level 16, State 2, Line 1 Cannot open backup device 's3://Bucket_name/folder/file_name.bak'. Operating system error 12007(failed to retrieve text for this error. Reason: 15100). Msg 3013, Level 16, State 1, Line 1 VERIFY DATABASE is terminating abnormally.

  • Leo K EXPERT
    a month ago

    Which version of SQL Server are you using? SELECT @@VERSION

  • harry rajpal
    a month ago

    It is solved, actually my bucket name has region <west> within its name and my bucket is existing in west region. while running restore command, I was putting <east> in bucket region name as west. soi it was syntax issue. it worked now thanks.

    bucket name: s3://abc-us-west-2-def.s3.us-west-2.amazonaws.com/backups

    while running restore command I was using syntax like: s3://abc-us-east-2-def.s3.us-west-2.amazonaws.com/backups

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions

Relevant content