Import/Export DB's

Overview

To import or export databases to and from SQL Managed system there are different methods available. Below you will find the main method to accomplish import and export tasks.

Day 2 actions

There are three option as day 2 action available:

Action name	Description
Import DB	Import single DB through full backup file. User manual reference
Export DB	Export a single DB through full backup file. User manual reference
Mass Migration	Import of multiple DBs using full, differential or transaction log backups. User manual reference

These three option are based done via S3 buckets.

Starting with v171, only Object Storage S3 instances provisioned from your ESC tenant can be used: these instances are per default not exposed to the internet and only reachable from your private network.

The import/export functionalities are available from the portal or through API.

This is an information message

Important

Make sure the needed IP ranges are whitelisted accordingly.

Import DB

To import a DB to a Managed SQL Server follow these steps:

1. (if needed) Order an Object Storage S3 instance incl. S3 User via ESC Catalog.
2. Get the Credentials for your S3 instance via Get S3 Credentials action on your S3 Instance.
3. (if needed) Create a bucket on your S3 namespace
4. Upload a DB backup (*.bak) to your S3 bucket
5. Run the "Import MS SQL Database" Day 2 action on our SQL deployment
   1. Enter the credentials
   2. Input a name for the DB to be migrated even if the name won't change
   3. Input a password for the default SQL db_owner named dbo_databasename which will be created

Basic Actions

Below are summarized the basic actions and check routine of the import functionality:

• Create a SQL Login named dbo_databasename
• Calculate backup size and cross check if the target server has enough disk space
• Calculate if the database uncompressed will fit in the target server
• Check if backup is damaged
• Check if backup file contains more than one backup
• Check if database mode is read-only
• Check if database mode is single-user
• Restore the database
• Check if DB is online and accessible
• Delete all the DB user that don't have a login and don't own a schema on the target server
• Fix the SQL user DB that have a login on the target server. If user DB can't be fixed the process will be still considered successful
• After a successfully restore the backup file will be deleted

If one check is not passed the action will abort and the uploaded file will be deleted on the target system.

Restrictions

• File Stream restore is not supported yet
• In-Memory DB import is not supported yet (because of File Stream)
• Contained DB's and users are not supported
• Read-only mode for import is not supported *
• Single-user mode for import is not supported *
• The compatibility level of the database will not be changed
• The recovery model of the database will not be changed
• Orphaned users that own a schema but don't have a login won't be dropped neither be fixed

*Read-only and single-user modes are supported in the SQL service but not for the import. It is therefore recommended to change the mode before importing in order to avoid errors. The mode can be changed after a successful import.

Export DB

The export routine is very similar to the import functionality.

The database will be backed up on the target system and then copied to your S3 bucket.

Backup / Restore from share

There are reasons customers won't use S3 technologies, for example that it is not possible to use S3 storage because a proxy has to be configured.

Alternatively to S3 technology you can use SQL native backup / restore T-SQL commands with a network share as source or target . Please note that it's not allowed to map a share as network drive on a SQL Managed server (RDP is not allowed). The following conditions have to be met:

• Network connectivity between source system and target SQL server has to be open (port 445)
• The share has to be accessible from the resource domain, i.e. the source VM is joined in the same domain of the SQL system
• The SQL service account has access to the network share

Please note that the same restrictions described in the chapter above apply. Note: Restore with override of an existing database doesn't work for the Managed DBMS (but it does for DBA Mode if the user is granted with the needed permissions).

Details are explained here.

Additional data import/export methods

The same prerequisites needed for the SQL native backup/restore method apply in order to use onboard SQL server functionalities.

BacPac or DacPac (Documentation) files can be applied through SSMS or SSDT to import database objects and/or data.

Please note that the usage of the above mentioned techniques depend on the granted user permissions (Permissions Model).

Furthermore the built-in Import and Export Data in SSMS can be used to export or import data to and from table to flat files (i.e. flat files, excel sheets etc.) from and to network shares.

Refer please to Microsoft development documentation for details.

Security note

Importing or exporting data from or to a share is under customer control.

Support note

The day 2 actions as automated processes are supported by Swisscom in case of failure.

The usage of other import/export techniques are not supported by Swisscom as incident process. If you need assistance in these topics you can request Swisscom to provide dedicated support.

Important: exporting a database with a backup to share without the hint copy_only will break the backup chain. In case of database recovery this can lead to data loss. Therefore it is recommended to export database by backup using the above mentioned hint.

It is recommended to have a least a basic understanding how SQL backup and restore work.