Labels

admin (1) aix (1) alert (1) always-on (2) Architecture (1) aws (3) Azure (1) backup (3) BI-DWH (10) Binary (3) Boolean (1) C# (1) cache (1) casting (3) cdc (1) certificate (1) checks (1) cloud (3) cluster (1) cmd (7) collation (1) columns (1) compilation (1) configurations (7) Connection-String (2) connections (6) constraint (6) copypaste (2) cpu (2) csv (3) CTE (1) data-types (1) datetime (23) db (547) DB2 (1) deadlock (2) Denali (7) device (6) dotNet (5) dynamicSQL (11) email (5) encoding (1) encryption (4) errors (124) excel (1) ExecutionPlan (10) extended events (1) files (7) FIPS (1) foreign key (1) fragmentation (1) functions (1) GCP (2) gMSA (2) google (2) HADR (1) hashing (3) in-memory (1) index (3) indexedViews (2) insert (3) install (10) IO (1) isql (6) javascript (1) jobs (11) join (2) LDAP (2) LinkedServers (8) Linux (15) log (6) login (1) maintenance (3) mariadb (1) memory (4) merge (3) monitoring (4) MSA (2) mssql (444) mssql2005 (5) mssql2008R2 (20) mssql2012 (2) mysql (36) MySQL Shell (5) network (1) NoSQL (1) null (2) numeric (9) object-oriented (1) offline (1) openssl (1) Operating System (4) oracle (7) ORDBMS (1) ordering (2) Outer Apply (1) Outlook (1) page (1) parameters (2) partition (1) password (1) Performance (103) permissions (10) pivot (3) PLE (1) port (4) PostgreSQL (14) profiler (1) RDS (3) read (1) Replication (12) restore (4) root (1) RPO (1) RTO (1) SAP ASE (48) SAP RS (20) SCC (4) scema (1) script (8) security (10) segment (1) server (1) service broker (2) services (4) settings (75) SQL (74) SSAS (1) SSIS (19) SSL (8) SSMS (4) SSRS (6) storage (1) String (35) sybase (57) telnet (2) tempdb (1) Theory (2) tips (120) tools (3) training (1) transaction (6) trigger (2) Tuple (2) TVP (1) unix (8) users (3) vb.net (4) versioning (1) windows (14) xml (10) XSD (1) zip (1)

Restoring a SQL Server database backup file to AWS RDS instance

What we want to do?
Restore SQL Server database backup file (.bak) to AWS RDS instance.
We have:
1. A bak file
2. An AWS RDS instance.

What we can't do?
We can't use the "restore" task ib SSMS - because we cann't access the local file system of the SQL Server (it handled by AWS).

What we can do?
Restoring using Amazon S3 - the object storage of AWS.

Restoring steps:

In Amazon S3:
1. Create bucket
a. Enter a name.
b. Enter the same Region as in the target RDS instance.
2. Upload the bak file to the bucket.

In AWS RDS:
3. Setting an Option Group
a. Create an option group in AWS RDS --> Options Groups tab.
b. Add SQLSERVER_BACKUP_RESTORE option to the option group.
4. Modify settings in the AWS RDS instance
a. on the databases list, sign your instance and check "Modify" button.
b. Under "Database options" section - select the Option group that was created in step 

In SQL Server Management Studio (SSMS):
5. run msdb.dbo.rds_restore_database (see script below). this SP crates a task to restore the DB
6. check task execution:
exec msdb.dbo.rds_task_status @task_id = 8

Diagram from aws site
(A diagram from aws site)


-- msdb.dbo.rds_restore_database script:

-- ==============================================
-- Declaration
-- ==============================================
DECLARE @par_restore_db_name sysname
DECLARE @par_s3_arn_to_restore_from nvarchar(2048)
DECLARE @par_kms_master_key_arn nvarchar(256)
DECLARE @par_type nvarchar(256)
DECLARE @par_with_norecovery int

----------------------------
-- Set parameter values:
----------------------------
SET @par_restore_db_name = N'ItaiDB'
SET @par_s3_arn_to_restore_from = 'arn:aws:s3:::itaibckt/ItaiDB.bak'
-- @s3_arn_to_restore_from='arn:aws:s3:::bucket_name/file_name.extension'
SET @par_with_norecovery = 0
-- 0 = restore with RECOVERY. The database is online after the restore.
-- 1 = restore with NORECOVERY. The database remains in the RESTORING state after restore task completion.
--SET @par_kms_master_key_arn = 
-- If you encrypted the backup file, the KMS key to use to decrypt the file.
--SET @par_type = 'FULL'
-- Type of restore: DIFFERENTIAL or FULL. The default is FULL

-- ==============================================
-- Execution
-- ==============================================
EXECUTE dbo.rds_restore_database
@restore_db_name = @par_restore_db_name,
@s3_arn_to_restore_from = @par_s3_arn_to_restore_from,
--@kms_master_key_arn = @par_kms_master_key_arn,
--@type = @par_type ,
@with_norecovery = @par_with_norecovery
GO

-- ==============================================
-- check task execution:
-- ==============================================
exec msdb.dbo.rds_task_status @task_id = 8
GO

No comments:

Post a Comment