It is possible to create a backup of a database running on an Amazon RDS instance and restore it on a local machine using the standard Task -> Backup and Task -> Restore features within Microsoft SQL Server Management Studio? If so, how do you go about doing this?
Note, this question does not pertain to whether you can bulk copy the data or generate the scripts, but whether you can create a true .BAK database backup which can be restored using the SSMS restore feature.
You cant currently create a .bak file out of amazon rds. The way I handle this is to use the azure migration wizard which just happens to also work with amazon rds.
I spin up a EC2 instance with SQL Server and the migration wizard installed. I then use the migration tool to copy the RDS database to the ec2 instance.
once that is done you can create a .bak file from the SQL Server running on the EC2 instance. its a pain but it works. if you have the bandwidth or your database is small you may be able to use the migration tool directly on your target machine.
You can now get a bak file out into S3. Here are the instructions: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.html
Here's a quick snippet that'll generate a backup:
exec msdb.dbo.rds_backup_database
@source_db_name='database_name',
@s3_arn_to_backup_to='arn:aws:s3:::bucket_name/file_name_and_extension',
@overwrite_S3_backup_file=1;
Before this feature, I could get a bacpac out that worked well in my case. In SSMS, right-click the database > Tasks > Export Data-tier Application.
I was able to import this onto my server without any issues.
You can create a backup locally from AWS RDS. Using SQL Management Studio, right-click your database > Task > Export Data
You just have to choose the right "Data Source", e.g. "SQL Server Native 11" for both, the source and destination.
It is well described in AWS documentation page: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.html#SQLServer.Procedural.Exporting.SSIEW
Section: "SQL Server Import and Export Wizard"
How to backup/Export/Import Amazon RDS MSSQL database from Visual Studio/3rd Party Tool.
First create new Bucket with Default Settings for MSSQL DB to restore/export/Backup.
Go to AWS RDS and click on Option Groups
Create Group (Name, Description, Engine, Version)
Go to created option group > Add Option > select option Name (SQLSERVER_BACKUP_RESTORE)
In IAM Role > Create New IAM Role, Put role name and add these 2 permissions (AWSBackupServiceRolePolicyForBackup and AWSBackupServiceRolePolicyForRestores)
In S3 Destination > selected created S3 bucket for backup/export/restore.
In Scheduling > Immediately
Go to Visual Studio, connect your DB, and Execute the below command.
EXEC msdb.dbo.rds_backup_database
@source_db_name = 'mydbname',
@S3_arn_to_backup_to = 'arn:aws:s3:::mydbbackup/mydbbackup-name'
EXEC msdb.dbo.rds_task_status
For backed up MSSQL DB > go to S3 Bucket > Click on Bucket Name > click on created backup file and download.
If you want to restore the DB at RDS from the backup file at S3, execute below script.
exec msdb.dbo.rds_restore_database
@restore_db_name= 'MySQL dB',
@s3_arn_to_restore_from= arn:aws:s3::rdsbackuprestore1/sample.backup'
You can use not only native sql server backup but any other sql server functionality for data migration. I would highlight the following options for resolving your case.
Sql server native backup
1.Native sql server backup\restoe
RDS Sql Server doesn't support T-SQL CREATE DATABASE commands. However, you can do a native backup on S3 using the stored procedure
EXEC [msdb].[dbo].[rds_backup_database]
@source_db_name = '<database name>',
@S3_arn_to_backup_to = N'arn:aws:s3:::<bucket name>/sample.backup'
For this stored procedure to work, you need to do a little pre-configuration. More details can be found here
From the point of view of speed - creating a native backup will be the most optimal, but this method is not very convenient to use. When launched, the stored procedure does not create a backup directly but creates a “task” that needs to be monitored. And only when this task is successfully completed can you copy the backup from S3 to your local server.
To restore a backup file to a local server, use SSMS: Databases -> Right-click -> Restore database
2. Export\Import data tier application
Export\import bacpac is a mechanism for migrating a database from one server to another. You can export\import your database using either SSMS or the sqlpackage utility.
Via SSMS
Via sqlpackage.exe
sqlpackage.exe /action:Export /SourceServerName: /SourceDatabaseName: /SourceUser:admin /SourcePassword: /TargetFile:<patch to target .bacpac> /p:ExtractAllTableData=True
Export data-tier application does not guarantee transactional data consistency. For the exported file to be transactionally consistent, you need to ensure that there are no writes operations during the data export.
To restore a .bacpac file to your local server, use SSMS: Databases -> Right-click -> Import data-tier application
More details about both options can be found here
3. Generate t-sql script
If your database is not very large then you can create a T-SQL script that contains instructions for restoring the database.
Creating t-sql script is the slowest but also the most flexible way to copy a database. In the advanced settings, you can choose many options for script creating.
As well as the export data-tier application, it is necessary to ensure that there are no writes operations to the database during the export.
To restore to a local server, drag the files into SSMS and press F5. But if the file is too large, then use the sqlcmd utility
© 2022 - 2024 — McMap. All rights reserved.