I have a SQL Server database running on a Amazon Linux machine and I want to create a backup of it. I am using the following query:
BACKUP DATABASE Database TO DISK = 'backup.bak';
This, in fact, creates a backup of my database in the D:\rdsdbdata\BACKUP folder. The only problem is that this folder does not exist on my machine. Trying to specify the path results in SQL attempting to save the backup in D:\rdsdbdata\BACKUP plus my folder, like this:
BACKUP DATABASE Database TO DISK = '/home/work/backup.bak';
I get the following error:
Msg 3201, Level 16, State 1, Server EC2AMAZ, Line 1
Cannot open backup device 'D:\rdsdbdata\BACKUP\home\work\backup.bak'. Operating system error
The system cannot find the path specified.).Msg 3013, Level 16, State 1, Server EC2AMAZ, Line 1
BACKUP DATABASE is terminating abnormally.
Using another query, I can confirm that the .bak file actually exists in D:\rdsdbdata\BACKUP as it also increases the storage on the server. I need to retrieve the .bak file to my machine.
Is there any method to access the SQL Server disk to retrieve this file or a correct way to specify the path?
SELECT @@VERSIONon your instance? Given what you've described so far it looks like an EC2 Windows based machine.msdb.dbo.rds_backup_databaseto perform backups directly to an S3 bucket. See: Importing and exporting SQL Server databases using native backup and restore which also includes IAM policy examples.msdb.dbo.rds_backup_databaseinstead.