Skip to content

data-ingestion-restore-database.md

Latest commit

 

History

History
112 lines (80 loc) · 4.1 KB

data-ingestion-restore-database.md

File metadata and controls

112 lines (80 loc) · 4.1 KB
title Restore a database
titleSuffix SQL Server big data clusters
description This article shows how to restore a database into the master instance of a [!INCLUDE[big-data-clusters-2019](../includes/ssbigdataclusters-ver15.md)].
author MikeRayMSFT
ms.author mikeray
ms.reviewer mihaelab
ms.date 08/21/2019
ms.topic conceptual
ms.prod sql
ms.technology big-data-cluster

Restore a database into the SQL Server big data cluster master instance

[!INCLUDEtsql-appliesto-ssver15-xxxx-xxxx-xxx]

This article describes how to restore an existing database into the master instance of a [!INCLUDEbig-data-clusters-2019]. The recommended method is to use a backup, copy, and restore approach.

Backup your existing database

First, backup your existing SQL Server database from either SQL Server on Windows or Linux. Use standard backup techniques with Transact-SQL or with a tool like SQL Server Management Studio (SSMS).

This article shows how to restore the AdventureWorks database, but you can use any database backup.

Tip

Download the AdventureWorks backup.

Copy the backup file

Copy the backup file to the SQL Server container in the master instance pod of the Kubernetes cluster.

kubectl cp <path to .bak file> master-0:/tmp -c mssql-server -n <name of your big data cluster>

Example:

kubectl cp ~/Downloads/AdventureWorks2016CTP3.bak master-0:/tmp -c mssql-server -n clustertest

Then, verify that the backup file was copied to the pod container.

kubectl exec -it master-0 -n <name of your big data cluster> -c mssql-server -- bin/bash
cd /var/
ls /tmp
exit

Example:

kubectl exec -it master-0 -n clustertest -c mssql-server -- bin/bash
ls /tmp
exit

Restore the backup file

Next, restore the database backup to master instance SQL Server. If you are restoring a database backup that was created on Windows, you will need to get the names of the files. In Azure Data Studio, connect to the master instance and run this SQL script:

RESTORE FILELISTONLY FROM DISK='/tmp/<db file name>.bak'

Example:

RESTORE FILELISTONLY FROM DISK='/tmp/AdventureWorks2016CTP3.bak'

Backup file list

Now, restore the database. The following script is an example. Replace the names/paths as needed depending on your database backup.

RESTORE DATABASE AdventureWorks2016CTP3
FROM DISK='/tmp/AdventureWorks2016CTP3.bak'
WITH MOVE 'AdventureWorks2016CTP3_Data' TO '/var/opt/mssql/data/AdventureWorks2016CTP3_Data.mdf',
        MOVE 'AdventureWorks2016CTP3_Log' TO '/var/opt/mssql/data/AdventureWorks2016CTP3_Log.ldf',
        MOVE 'AdventureWorks2016CTP3_mod' TO '/var/opt/mssql/data/AdventureWorks2016CTP3_mod'

Configure data pool and HDFS access

Now, for the SQL Server master instance to access data pools and HDFS, run the data pool and storage pool stored procedures. Run the following Transact-SQL scripts against your newly restored database:

USE AdventureWorks2016CTP3
GO
-- Create the SqlDataPool data source:
IF NOT EXISTS(SELECT * FROM sys.external_data_sources WHERE name = 'SqlDataPool')
  CREATE EXTERNAL DATA SOURCE SqlDataPool
  WITH (LOCATION = 'sqldatapool://controller-svc/default');

-- Create the SqlStoragePool data source:
IF NOT EXISTS(SELECT * FROM sys.external_data_sources WHERE name = 'SqlStoragePool')
   CREATE EXTERNAL DATA SOURCE SqlStoragePool
   WITH (LOCATION = 'sqlhdfs://controller-svc/default');
GO

Note

You will have to run through these setup scripts only for databases restored from older versions of SQL Server. If you create a new database in SQL Server master instance, data pool and storage pool store procedures are already configured for you.

Next steps

To learn more about the [!INCLUDEbig-data-clusters-2019], see the following overview: