Share via

Lesson 7: Move your data files to Azure Storage

  • Article

In this lesson, you will learn how to move your data files to Azure Storage (but not your SQL Server instance). To follow this lesson, you do not need to complete Lesson 4, 5, and 6.

To move your data files to Azure Storage, you can use the ALTER DATABASE statement as it helps to change the location of the data files.

This lesson assumes that you already completed the following steps:

  • You have an Azure Storage account.

  • You have created a container under your Azure Storage account.

  • You have created a policy on a container with read, write, and list rights. You also generated a SAS key.

  • You have created a SQL Server credential on the source machine.

Next, use the following steps to move your data files to Azure Storage:

  1. First, create a test database in the source machine and add some data to it.

    
USE master;   
CREATE DATABASE TestDB1Alter;   
GO   
USE TestDB1Alter;   
GO   
CREATE TABLE Table1 (Col1 int primary key, Col2 varchar(20));   
GO   
INSERT INTO Table1 (Col1, Col2) VALUES (1, 'string1'), (2, 'string2');   
GO

  2. Run the following code:

    
-- In the following statement, modify the path specified in FILENAME to   
-- the new location of the file in Azure Storage container.   
ALTER DATABASE TestDB1Alter    
    MODIFY FILE ( NAME = TestDB1Alter,    
                FILENAME = 'https://teststorageaccnt.blob.core.windows.net/testcontaineralter/TestDB1AlterData.mdf');   
GO

  3. When you run this, you will see this message: "The file "TestDB1Alter" has been modified in the system catalog. The new path will be used the next time the database is started."

  4. Then, set the database offline.

    
ALTER DATABASE TestDB1Alter SET OFFLINE;   
GO

  5. Now, you need to copy the data files to Azure Storage by using one of the following methods: AzCopy Tool, Put Page, Storage Client Library Reference, or a third-party storage explorer tool.

    Important: When using this new enhancement, always make sure that you create a page blob not a block blob.

  6. Then, set the database online.

    
ALTER DATABASE TestDB1Alter SET ONLINE;   
GO

Next Lesson:

Lesson 8. Restore a database to Azure Storage