Restore File & Filegroup

SQL Server Database Files

SQL Server Database has three types of files listed as below:

1. Primary Data File (.mdf) – It contains all the information about the database. It also points to the other files in the database.
2. Secondary Data File (.ndf) – NDF files are user defined files and also called the optional file that is used to store the user data.
3. Transaction Log (.ldf)–  It contains all the log information related to the database.

Backup SQL Server Database File and Filegroups

Here we will discuss how to backup the SQL Server database file and filegroups using SQL Server Management Studio and Transact-SQL.
Via Transact-SQL

TSQL Script to Create Database with Multiple Data Files in Primary and Read Only File in Secondary File Group

CREATE DATABASE [SQLDoseFG]

ON PRIMARY

( NAME = N'SQLDoseFG', FILENAME = N'F:\MSSQL\DATA\SQLDoseFG.mdf', SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),

( NAME = N'SQLDoseFG_Data2', FILENAME = N'F:\MSSQL\DATA\SQLDoseFG_Data2.ndf', SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),

FILEGROUP [Secondary]

( NAME = N'SQLDoseFG_Secondary', FILENAME = N'F:\MSSQL\DATA\SQLDoseFG_Secondary.ndf', SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

FILEGROUP [ReadOnly]

( NAME = N'SQLDoseFG_ReadOnly', FILENAME = N'F:\MSSQL\DATA\SQLDoseFG_ReadOnly.ndf', SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

LOG ON

( NAME = N'SQLDoseFG_log', FILENAME = N'F:\MSSQL\DATA\SQLDoseFG_log.ldf', SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10% )

GO

Create a FULL FILE backup Using TSQL command

In this example we will backup Secondary Data File which is available in Primary File Group.
BACKUP DATABASE [SQLDoseFG] FILE = N'SQLDoseFG_Data2' TO DISK = N'C\DBBackups\SQLDoseFG_SQLDoseFG_Data2.bak' WITH NOFORMAT, NOINIT, COMPRESSION, STATS = 25
GO

Backup Secondary Filegroup:

USE [master]

GO

BACKUP DATABASE SQLDoseFG

FILEGROUP = 'Secondary'

TO DISK = N'C:\SQLServer\SQLDoseFG_Secondary.bak'

WITH INIT, Stats=5

Restore Secondary Filegroup:

RESTORE DATABASE SQLDoseFG 
  FILEGROUP = 'SECONDARY'
  FROM DISK = 'C:\SQLServer\SQLDoseFG_Secondary.bak'
  WITH PARTIAL, RECOVERY

Restore File:
Step 1: Check the logical file names with the help of the following command:

RESTORE FILELISTONLY FROM DISK = 'E:\DBBackups\SQLDoseFG.bak'

Step 2: Use the logical names you get from the above query in the below query:

RESTORE DATABASE [SQLDoseFG_new] FILE = N'<MDFLogicalName>' FROM DISK = N'E:\DBBackups\SQLDoseFG.bak' WITH FILE = 1, NOUNLOAD, STATS = 10, MOVE N'<MDFLogicalname>' TO N'E:\DBBackups\SQLDoseFG_new.mdf', MOVE N'<LDFLogicalName>' TO N'E:\DBBackups\SQLDoseFG_new_0.ldf'

After running the above commands with the correct values you will see the output like this:

10 percent processed.

20 percent processed.

30 percent processed.

40 percent processed.

50 percent processed.

60 percent processed.

70 percent processed.

80 percent processed.

90 percent processed.

100 percent processed.

Processed 7672 pages for database 'SQLDoseFG_new', file '<MDFLogicalname>' on file 1.

Processed 5 pages for database 'SQLDoseFG_new', file '<LDFLogicalName>' on file RESTORE DATABASE ... FILE=<name> successfully processed 7677 pages in 0.780 seconds (76.893 MB/sec).

Completion time: 2019-10-20T11:35:31.8343787+05:30