{"id":77720,"date":"2018-03-22T19:31:29","date_gmt":"2018-03-22T19:31:29","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=77720"},"modified":"2021-08-24T13:39:19","modified_gmt":"2021-08-24T13:39:19","slug":"recover-data-sql-server-aws-rds-help-bacpac","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/recover-data-sql-server-aws-rds-help-bacpac\/","title":{"rendered":"Recover Data in SQL Server AWS RDS with the Help of BACPAC"},"content":{"rendered":"

Amazon Web Services (AWS) provides highly available, highly managed SQL Server instances through the Relational Database Service (RDS). Using RDS instances greatly reduces much of the administrative need for managing backups, minor upgrades, and monitoring. However, these conveniences come at a cost: limited administrative permissions and functionality. One such limitation that requires a workaround becomes obvious when attempting to restore a copy of an existing database to the same SQL Server instance<\/strong>.<\/p>\n

One probable reason for needing multiple copies of a database on the same server might be to maintain separate database environments for development and testing, especially if using additional servers isn\u2019t an option. Another reason might be data recovery \u2013 if data is deleted or otherwise missing, it may be necessary to temporarily restore a new copy of a database (from either a RDS snapshot or from an existing native backup file), recover the missing data, and then drop the duplicated database.<\/p>\n

To demonstrate the RDS limitations and a workaround method, this article will develop a scenario where data has been deleted from a specific table in a RDS database. The objective is to recover the missing data using this method.<\/p>\n

\n

NOTE: This exercise should not be performed on a production RDS instance until it has been tested on a development instance. See the warning in the \u2018Import the BACPAC\u2019 section.<\/p>\n<\/div>\n

Preparing the Scenario<\/h2>\n

For this example, you will create an initial AdventureWorks2016<\/strong> database from the online sample .bak file<\/a> (our first instance of the database). You\u2019ll then simulate a data loss and attempt to restore a temporary copy of the database (using the same .bak file) from which to recover the missing data.<\/p>\n

First, log into your SQL Server RDS instance, using the instance endpoint as the server name:<\/p>\n

<\/p>\n

Next, download the online sample .bak file<\/a> and upload it to your S3 bucket:<\/p>\n

<\/p>\n

Once the file has been uploaded, make a note of the S3 path, in this case backups\/MISC.<\/p>\n

<\/p>\n

You can now initiate a manual database restore using the built-in RDS procedure rds_restore_database. <\/strong>In Management Studio, run the following statement replacing the path if yours is different:<\/p>\n

--first database restore\r\nEXEC msdb.dbo.rds_restore_database \r\n   @restore_db_name = 'AdventureWorks2016',\r\n   @S3_arn_to_restore_from = 'arn:aws:s3:::backups\/MISC\/AdventureWorks2016.bak';<\/pre>\n
\n
IMPORTANT: the path value in the @S3_arn_to_backup_from<\/strong> parameter is case-sensitive. For example, setting the value to ‘arn:aws:s3:::backups\/misc\/AdventureWorks2016.bak’ will not work.<\/p>\n<\/div>\n
Adding the SQLSERVER_BACKUP_RESTORE Option<\/h2>\n
If Native Backup and Restore Option has not yet been configured for your RDS instance, you will get an error, Database backup\/restore option is not enabled yet or is in the process of being enabled. Please try again later,<\/em> when attempting to use the rds_restore_database<\/strong> procedure:<\/p>\n
<\/p>\n
Per AWS documentation<\/a>, an option group associated with the RDS instance must have the SQLSERVER_BACKUP_RESTORE<\/em> option added. To do this, you\u2019ll need to create a new option group by navigating to Option groups and clicking Create a group<\/em>. Then enter a name for the option group, a description, the SQL Server engine (Enterprise, Standard, etc.) and version you are using. Then click Create<\/em>:<\/p>\n
<\/p>\n
Once created, select the new option group and click Add option<\/em>:<\/p>\n
<\/p>\n
The only available option is the SQLSERVER_BACKUP_RESTORE<\/em> option, the one that you need. Make sure that Create a new role<\/em> is selected, enter a name for the new IAM role<\/em>, and select an existing or create a new S3 bucket. Select Apply Immediately<\/em> and then click Add Option<\/em>:<\/p>\n
<\/p>\n
Take a look at the new sqlNativeBackup<\/em> policy (IAM\/Policies<\/em>, then select sqlNativeBackup<\/em>) that was automatically created for the new IAM role (rl_sqlserver_backup_restore<\/em>). On the {} JSON<\/em> tab, you can see the Action<\/strong> allowed on the Resource<\/strong> (S3 bucket):<\/p>\n
<\/p>\n
All that is left to do now is to associate the RDS instance with the new option group. Select your instance name from RDS\/Instances<\/em> and choose Modify<\/em> from the Instance actions<\/em> dropdown. Next, scroll down to Database Options\/Option group<\/em> and select the new option group from the dropdown.<\/p>\n
<\/p>\n
Click Continue<\/em>. Select Apply immediately<\/em>,<\/strong> and then Modify DB Instance<\/em>. Even after completing the steps, it may take several minutes to go into effect.<\/p>\n
Checking the Restore Status<\/h2>\n
You should now be able to execute the rds_restore_database <\/strong>procedure successfully. When executed, the rds_restore_database<\/strong> command returns a summary acknowledgement and status:<\/p>\n
<\/p>\n
At any time during the restore, you can check the status using the rds_task_status<\/strong> procedure in another query window:<\/p>\n
--check status\r\nEXEC msdb.dbo.rds_task_status @db_name = 'AdventureWorks2016';\r\n<\/pre>\n
During the restore, the lifecycle<\/em> column will show a value of IN_PROGRESS<\/em>:<\/p>\n
<\/p>\n
When the restore is complete, the lifecycle<\/em> column will return SUCCESS. <\/em><\/p>\n
<\/p>\n
The new database will be visible in Object Explorer<\/em>:<\/p>\n
<\/p>\n
Simulating data loss<\/h2>\n
To simulate missing data, delete 500 records from the DatabaseLog<\/strong> table:<\/p>\n
USE AdventureWorks2016;\r\nDELETE FROM DatabaseLog\r\nWHERE DatabaseLogID <= 500;<\/pre>\n
The Problem<\/h2>\n
Now you can demonstrate the problem of trying to create a duplicate database (with a different database name) using the same backup file. Remember, the goal is to recover the missing data that was deleted in the previous step. Attempt to perform the restore again, this time using a different database name:<\/p>\n
--second database restore\r\nEXEC msdb.dbo.rds_restore_database \r\n   @restore_db_name = 'AdventureWorks2016_clone',\r\n   @S3_arn_to_restore_from = 'arn:aws:s3:::backups\/MISC\/AdventureWorks2016.bak';<\/pre>\n
The initial status returned looks normal:<\/p>\n
<\/p>\n
\u2026but the restore ends with a lifecycle value of ERROR<\/em> upon checking the rds_task_status<\/strong> procedure:<\/p>\n
--check status\r\nEXEC msdb.dbo.rds_task_status @db_name = 'AdventureWorks2016_clone';<\/pre>\n
<\/p>\n
The task_info<\/em> field displays the following error message:[2018-01-23 19:24:22.180] Aborted the task because of a task failure or a concurrent RESTORE_DB request.[2018-01-23 19:24:22.210] Task ID 57 (RESTORE_DB) exception: Database AdventureWorks2016_clone cannot be restored because <\/em>there is already an existing database with the same file_guids<\/em><\/strong> on the instance.<\/em><\/p>\n
File_guids<\/h2>\n
You can see that the RDS limitation\u2019s root cause is the inability to allow the same database unique identifier (file_guid<\/em>) more than once on the same instance \u2013 and since the AdventureWorks2016 database\u2019s original file_guids<\/em> are contained in the backup file, any databases restored from it has that same file_guid<\/em>. You would have the same issue even if you attempted a restore from a fresh backup of the database. Database file_guid<\/em>s can be viewed by selecting them from a database\u2019s sys.database_files<\/strong> table:<\/p>\n
USE AdventureWorks2016;\r\nSELECT name, file_guid FROM sys.database_files; <\/pre>\n
<\/p>\n
Amazon acknowledges this issue in the Importing and Exporting SQL Server Databases<\/a> online user guide \u2013 which says that it is not possible to create a copy of a database to the same instance. According to the guide:<\/p>\n
<\/p>\n
Restoring a new RDS instance from a snapshot would make missing data available, but might also expose other limitations. For example, using a linked server connection would allow data transfer between two RDS instances, but linked servers appear to be unsupported in RDS at the time of this writing. An attempt to create a linked server generates the error below:<\/p>\n
<\/p>\n
A Solution<\/h2>\n
However, it is possible, using a known workaround, to create and maintain concurrent, identical databases on the same RDS instance. This workaround entails using the Export Data-Tier Application<\/strong><\/a> module in SQL Server Management Studio (SSMS) to create an export of a database to a .bacpac<\/strong> file. A BACPAC export can contain either an entire database or just specific objects, and can be used for importing data to Azure, AWS, and conventional SQL Server instances \u2013 and also for data archival purposes. A TechNet article<\/a> describes a .bacpac file:<\/p>\n
\u201cThe export file contains all supported database schema objects and table data in a single package. Schema objects include logins, users, schemas, tables, columns, constraints, indexes, views, stored procedures, functions, triggers, and other objects relevant to the definition of a database.\u201d<\/em><\/p>\n
When a .bacpac file is generated and imported, the database\u2019s file_guids are reset \u2013 which is exactly what is needed to allow a duplicate database in RDS.<\/p>\n
To get a BACPAC export of the AdventureWorks2016<\/strong> data that is in our original AdventureWorks2016.bak<\/em> backup, you need to restore the backup file to a new instance. You could restore an RDS snapshot (taken before the data was deleted) as a new, temporary RDS instance and then export the .bacpac file from that instance\u2019s copy of AdventureWorks2016<\/strong>, but for this example, you\u2019ll just restore the original backup file to another existing SQL Server instance. Restore the original AdventureWorks2016.bak<\/em> to a conventional, on-premises SQL Server 2016 instance (SQL2016_DEV) that already exists as a development environment. Depending on your instance, you may need to use WITH MOVE<\/strong> in the backup statement to change the data and log file locations, if the original AdventureWorks2016<\/strong> database used a path that doesn\u2019t exist on you SQL2016_DEV server:<\/p>\n
RESTORE DATABASE AdventureWorks2016 FROM DISK = 'D:\\Backup\\AdventureWorks2016.bak'\r\nWITH MOVE 'AdventureWorks2016_Data' TO 'D:\\Data\\AdventureWorks2016_Data.mdf',\r\nMOVE 'AdventureWorks2016_Log' TO 'D:\\Data\\AdventureWorks2016_Log.ldf';<\/pre>\n
Once the restore is complete, you can see the new database in Object Explorer:<\/p>\n
<\/p>\n
When you check the file_guids<\/em> of the new database, you\u2019ll see that it has exactly the same file_guids<\/em> as the copy you restored to the RDS instance (the name<\/em> value is the logical name of the file, not the renamed file path name):<\/p>\n
USE AdventureWorks2016;\r\nSELECT name, file_guid FROM sys.database_files; <\/pre>\n
<\/p>\n
Export the BACPAC<\/h2>\n
To start the BACPAC export of the newly-restored AdventureWorks2016<\/strong> database, right-click the AdventureWorks2016<\/em> database, select Tasks<\/em>, then Export Data-tier Application<\/em>:<\/p>\n
<\/p>\n
\n
NOTE: SQL Server Management Studio must be a supported version for this step. If not, the BACPAC export will fail. For example, using SQL Server Management Studio 2014 to export a SQL Server 2016 database generates this error:<\/p>\n
<\/p>\n<\/div>\n
On the Introduction<\/em> page, click Next<\/em>. On the Export Settings<\/em> page, click Browse<\/em> to select a filename and location for the BACPAC export. Keep the default location, and name the export AdventureWorks2016.bacpac<\/em>:<\/p>\n
<\/p>\n
Since you only need to recover data from a single object (the DatabaseLog<\/strong> table), select that object only in the Advanced<\/em> tab. This will minimize the recovery time by reducing the size of the export file.<\/p>\n
<\/p>\n
Click Next<\/em>, and then Finish<\/em>:<\/p>\n
<\/p>\n
Check the summary of the processes on the Results<\/em> page and click Close<\/em>. The BACPAC export is complete.<\/p>\n
The next step will be to immediately import the file back to the same instance as a new database in order to reset the file_guid<\/em>.<\/p>\n
Import the BACPAC<\/h2>\n
WARNING!<\/strong> You could attempt to import the .bacpac<\/em> file directly back to the RDS instance, but even though RDS supports BACPAC exports (and imports using files generated from the same instance \u2013 see the note at the end of this article), a BACPAC file imported to RDS that was exported from a different instance can fail with the following error (at the time of this writing), leaving behind an empty database that cannot be easily removed<\/strong>:<\/p>\n
<\/p>\n
Attempting to drop the empty database that is left behind results in the below error:<\/p>\n
<\/p>\n
For this reason, it is highly recommended to <\/strong>restore a new RDS instance from a snapshot<\/strong><\/a> to use for practicing the BACPAC import process before performing it on a production system.<\/strong><\/p>\n
To avoid the \u2018empty database\u2019 scenario, you will perform the actual BACPAC import to a new database on the on-premises SQL2016_DEV instance, create a native backup from that new database, and then backup and restore it to the RDS instance. That way, you won\u2019t risk a BACPAC import failure.<\/p>\n
Right-click on Databases<\/em> in Object Explorer<\/em> and select Import Data-tier Application<\/em>:<\/p>\n
<\/p>\n
On the Introduction<\/em> page, click Next<\/em>. On the Import Settings<\/em> page, browse to the .bacpac<\/em> export file and click Next<\/em>:<\/p>\n
<\/p>\n
On the Database Settings<\/em> page, enter a name for the temporary copy of the database on SQL2016_DEV, click Next<\/em>, and then Finish<\/em>:<\/p>\n
<\/p>\n
A BACPAC Import Limitation<\/h2>\n
You will quickly find out that the BACPAC import process is very sensitive to a few factors – one of these being full-text indexes. The import process will fail with a \u2018Full-text Search is not installed, or a full-text component cannot be loaded\u2019<\/em> error, if your SQL2016_DEV instance does not have the Full-text search component installed. Why does this generate such an error on tables when no such tables are included in the BACPAC export? This is because, even though the BACPAC export does not export data from excluded objects, the object schema is still part of the export. You can verify this after the unsuccessful BACPAC import.<\/p>\n
<\/p>\n
Since the goal is to recover missing data only and not full-text indexes, you can circumvent this error without installing Full-text Search. Generate a dynamic SQL script to drop all full-text indexes in the AdventureWorks2016<\/strong> database on the SQL2016_Dev server, execute the script, then export and import the .bacpac file again:<\/p>\n
USE AdventureWorks2016;\r\nSELECT 'DROP FULLTEXT INDEX ON ['+OBJECT_SCHEMA_NAME(object_id)+'].['+OBJECT_NAME(object_id)+'];' as ObjectName \r\nFROM sys.fulltext_indexes;<\/pre>\n
<\/p>\n
Now that you have generated the appropriate DROP statements, copy and execute them:<\/p>\n
<\/p>\n
Export the .bacpac<\/em> file exactly as before, and immediately import it back to SQL2016_DEV to reset the file_guids. The process is successful this time:<\/p>\n
<\/p>\n
Now check the file_guids<\/em> of the new AdventureWorks2016-clone<\/strong> database:<\/p>\n
<\/p>\n
You will see that the new database has brand new file_guids<\/em> after the BACPAC import. The RDS instance will now accept AdventureWorks2016-clone<\/strong> as a new database.<\/p>\n
Now back up the new database so you can upload it to you S3 bucket:<\/p>\n
BACKUP DATABASE [AdventureWorks2016-clone] TO DISK = 'D:\\BACKUP\\AdventureWorks2016-clone.bak';<\/pre>\n
Notice how much smaller the new backup file is now, since you\u2019ve only exported data for one table:<\/p>\n
<\/p>\n
Connect back to the RDS instance and try the database restore in SSMS now that you have reset the file_guids<\/em>:<\/p>\n
--restore bacpac-exported\/imported database\r\nEXEC msdb.dbo.rds_restore_database \r\n   @restore_db_name = 'AdventureWorks2016_clone',\r\n   @S3_arn_to_restore_from = 'arn:aws:s3:::backups\/MISC\/AdventureWorks2016-clone.bak';<\/pre>\n
The rds_task_status<\/strong> procedure returns a lifecycle value of SUCCESS. <\/strong><\/p>\n
--check status\r\nEXEC msdb.dbo.rds_task_status @db_name = 'AdventureWorks2016_clone';<\/pre>\n
You can see the new database in the Object Explorer<\/em> pane:<\/p>\n
<\/p>\n
<\/p>\n
Check object contents to make sure the BACPAC import only pushed data for the required table and not the others. A count check reveals that while the original AdventureWorks2016<\/strong> database\u2019s Person.Address<\/strong> table contains many records, the AdventureWorks2016_clone<\/strong> database\u2019s correlated table contains no records. The DatabaseLog<\/strong> table\u2019s data is intact, as expected:<\/p>\n
USE AdventureWorks2016;\r\nSELECT COUNT(1) FROM Person.Address;\r\nSELECT COUNT(1) FROM DatabaseLog;<\/pre>\n
<\/p>\n
USE AdventureWorks2016_clone;\r\nSELECT COUNT(1) FROM Person.Address;\r\nSELECT COUNT(1) FROM DatabaseLog;<\/pre>\n
<\/p>\n
Recover the Missing Data<\/h2>\n
You\u2019re now able to recover the missing DatabaseLog<\/strong> data by joining the original table to the restored one:<\/p>\n
SET IDENTITY_INSERT AdventureWorks2016.dbo.DatabaseLog ON;\r\nINSERT INTO AdventureWorks2016.dbo.DatabaseLog (DatabaseLogID, PostTime, DatabaseUser, [Event], [Schema], [Object], [TSQL], XmlEvent)\r\nSELECT a.DatabaseLogID, a.PostTime, a.DatabaseUser, a.[Event], a.[Schema], a.[Object], a.[TSQL], a.XmlEvent \r\nFROM AdventureWorks2016_clone.dbo.DatabaseLog a\r\nLEFT OUTER JOIN AdventureWorks2016.dbo.DatabaseLog b \r\n ON b.DatabaseLogID = a.DatabaseLogID\r\nWHERE b.DatabaseLogID IS NULL;\r\nSET IDENTITY_INSERT AdventureWorks2016.dbo.DatabaseLog OFF;<\/pre>\n
<\/p>\n
Cleanup<\/h2>\n
Now that you\u2019ve successfully recovered the missing data, clean up by dropping the duplicated database from the RDS instance. If Multi-AZ (mirroring) is in place for the instance, you\u2019ll have to break the mirror partner before dropping the database:<\/p>\n
USE master;\r\n--break mirroring\r\nALTER DATABASE [AdventureWorks2016_clone] set PARTNER OFF;\r\n--drop database\r\nDROP DATABASE AdventureWorks2016_clone;<\/pre>\n
Summary<\/h2>\n
You\u2019ve completed the steps necessary to recover missing data from a database object in a SQL Server RDS instance using the BACPAC export method using these steps. For reference, the instructional steps are condensed here as follows:<\/p>\n
    \n
  1. Restore a copy of the database (from an existing backup or RDS snapshot) to another SQL Server instance, which can be either a conventional instance or a RDS instance (if it does not already have a copy of the database on it).\n
      \n
    • NOTE: This instance must be of the same major SQL Server version as the instance that created the database.<\/em><\/li>\n<\/ul>\n<\/li>\n
    • Export that copy of the database as a .bacpac<\/em> file, including only the objects needed, so that the export stays as small as possible.<\/li>\n
    • Import that .bacpac<\/em> file as a new database (can be imported to the same instance it was exported from, as long as it is NOT a RDS instance). This step gives the database a new file_guid<\/em>.<\/li>\n
    • Now that the new database contains a new file_guid<\/em>, back it up to S3.<\/li>\n
    • Restore the backup file in S3 to the original RDS instance as a new, duplicated database (with a new name).<\/li>\n
    • Recover the missing data from the duplicated database.<\/li>\n<\/ol>\n
      \n
      NOTE: If you didn\u2019t need to recover data, but simply wanted a copy of an existing RDS database, you could perform a BACPAC export, then import the .bacpac file immediately to the same instance it came from as a database copy with a new name \u2013 the empty database issue does not seem to happen when the BACPAC import is from a file that was exported from the same instance.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
      With Amazon RDS, it\u2019s easy to have a SQL Server running in minutes. There are some limitations, though. Seth Delconte explains a workaround to overcome one of the limitations: the inability to restore a second copy of a database to recover missing data.…<\/p>\n","protected":false},"author":221920,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[137092,143527,43],"tags":[95506],"coauthors":[48345],"class_list":["post-77720","post","type-post","status-publish","format-standard","hentry","category-aws","category-database-administration-sql-server","category-platform-as-a-service","tag-automate"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/77720","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221920"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=77720"}],"version-history":[{"count":3,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/77720\/revisions"}],"predecessor-version":[{"id":77770,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/77720\/revisions\/77770"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=77720"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=77720"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=77720"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=77720"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}