SQL Server – Database Management – File Groups & Files – v2019/CTP

Background

Wanted to list out the steps to add filegroups and files to a database.

And, remove them.

Outline

  1. Create Database
  2. File Group Management
    • Add
      • Add Filegroup
      • Add file to filegroup
    • Remove
      • Remove file from filegroup
      • Remove Filegroup
  3. Drop Database

Tasks

Create Database

SQL

Syntax


create database [database-name];

Sample


create database [DBMO];

Filegroup Management

Filegroup Management – Add

Filegroup Management – Add Filegroup

SQL
Syntax

ALTER DATABASE [database]
      ADD FILEGROUP [filegroup]
      CONTAINS MEMORY_OPTIMIZED_DATA;

Sample

ALTER DATABASE [DBMO]
      ADD FILEGROUP [DBMO.MOD]
      CONTAINS MEMORY_OPTIMIZED_DATA;

Filegroup Management – Add File to Filegroup

SQL
Syntax

ALTER DATABASE [database]
ADD FILE
(
     NAME = '[symbolicName]'
   , FILENAME = '[filename]'
)
TO FILEGROUP [filegroup]

Sample

ALTER DATABASE [DBMO]
ADD FILE
(
     NAME = 'InMemoryContainer'
   , FILENAME = 'M:\Program Files\Microsoft SQL Server\MSSQL15.V2019\MSSQL\DATA\DBMO_InMemoryOLTPContainer'
)
TO FILEGROUP [DBMO.MOD]

Filegroup Management – Remove

Filegroup Management – Remove File from filegroup

SQL
Syntax

ALTER DATABASE [database]
REMOVE FILE [symbolicName]
;

Sample

ALTER DATABASE [DBMO]
REMOVE FILE [InMemoryContainer]
GO

 

Filegroup Management – Remove Filegroup

SQL
Syntax

ALTER DATABASE [database]
      REMOVE FILEGROUP [filegroup]
      ;

Sample

ALTER DATABASE [DBMO]
      REMOVE FILEGROUP [DBMO.MOD]
      ;

Version

Image

@@version_v2019_v.3.2

Text


Microsoft SQL Server 2019 (CTP3.2) - 15.0.1800.32 (X64)
Jul 17 2019 21:29:33
Copyright (C) 2019 Microsoft Corporation
Enterprise Evaluation Edition (64-bit) on Windows Server 2016 Datacenter 10.0  (Build 14393: ) (Hypervisor)

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s