SQL Server – Backup and Restore FileGroup (PRIMARY) Issues

backuprestoresql server

/********************************** CREATE DATABASE PiecemealDB *************************************/
use [master]
go
if exists (select * from sys.databases where database_id = DB_ID('PiecemealDB'))
    drop database [PiecemealDB]
go
CREATE DATABASE [PiecemealDB]
    CONTAINMENT = NONE
    ON  PRIMARY 
( NAME = N'PiecemealDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\PiecemealDB.mdf' , SIZE = 4096KB , FILEGROWTH = 1024KB )
    LOG ON 
( NAME = N'PiecemealDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\PiecemealDB_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
    COLLATE Latin1_General_100_CI_AS
GO

USE PiecemealDB
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') 
ALTER DATABASE [PiecemealDB] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO
-----------------------------------------------------------------------------------------------------
use PiecemealDB
go
/* Creating table in PRIMARY FG & init data*/
if OBJECT_ID('dbo.MainFG') is not null
    drop table dbo.MainFG;
go
create table dbo.MainFG (Col1 int identity(1,1),Col2 varchar(100)) ON [PRIMARY];
go

insert into dbo.MainFG(Col2)values ('Primary FG')
go 100
/*******************************ADDING NEW FileGroup [ReadOnlyFG] ***********************************/
USE [master]
GO
ALTER DATABASE [PiecemealDB] ADD FILEGROUP [ReadOnlyFG]
GO
ALTER DATABASE [PiecemealDB] ADD FILE 
    ( NAME = N'ReadOnlyDF_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\ReadOnlyDF_Data.ndf' , SIZE = 4096KB , FILEGROWTH = 1024KB ) TO FILEGROUP [ReadOnlyFG]
GO
/******************************* create table under the new filegroup [ReadOnlyFG] ******************/
use PiecemealDB
go
if OBJECT_ID('dbo.ReadOnlyFG') is not null
    drop table dbo.ReadOnlyFG;
go
create table dbo.ReadOnlyFG (Col1 int identity(1,1),Col2 varchar(100)) ON [ReadOnlyFG];
go
/*************** Insert data into table under FileGroup [ReadOnlyFG] *******************************/
use PiecemealDB
go
insert into dbo.ReadOnlyFG(Col2)values ('ReadOnlyFG FG')
go 100
/********************************** SET ReadOnlyFG FileGroup to READ ONLY **************************/
USE [PiecemealDB]
GO
ALTER DATABASE [PiecemealDB] MODIFY FILEGROUP [ReadOnlyFG] READONLY
GO
/********************************** BACKUP PRIMARY FILEGROUP **************************************/
BACKUP DATABASE [PiecemealDB] 
    FILEGROUP = N'PRIMARY' TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\PiecemealDB.bak' 
    WITH NOFORMAT, NOINIT,  NAME = N'PiecemealDB-Full Filegroup Backup', 
    SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
GO
/********************************** RESTORE FILEGROUP PRIMARY  TO [PiecemealDB_NEW] **********************************/
use [master]
go

RESTORE DATABASE [PiecemealDB_NEW] READ_WRITE_FILEGROUPS 
FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\PiecemealDB.bak' WITH  FILE = 2, RECOVERY, PARTIAL, 
MOVE N'PiecemealDB' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\PiecemealDB_NEW.mdf',  
MOVE N'PiecemealDB_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\PiecemealDB_NEW_1.ldf',  
NOUNLOAD,  REPLACE,  STATS = 10
GO

** After you exec this batch,Go to PiecemealDB_NEW properties >> Filegroup TAB :
enter image description here

Question: How can I drop/delete the Other Filegroup [ReadOnlyFG].
1. Is there any way to restore only the PRIMARY filegroup , without the ReadOnlyFG ?.
2. If not, How can I get rid of the ReadOnlyFG after the restore ?.

10X

Best Answer

Is there any way to restore only the [PRIMARY] filegroup?

That is what you have already done. The other filegroups exist only in metadata at this stage. Piecemeal restore allows you to restore filegroups in stages, or mark them defunct if they are no longer needed. There is no way to delete them from the system metadata though.

After the restore operation in the question, the data on the secondary filegroup is not accessible, because that filegroup has not been restored (yet). You can't see everything in the SSMS GUI only. For example:

SELECT 
    [FilegroupName] = F.name,
    F.data_space_id,
    F.is_default,
    F.is_read_only,
    DF.[file_id],
    [FileName] = DF.name,
    DF.state_desc,
    DF.is_read_only
FROM sys.filegroups AS F
JOIN sys.database_files AS DF
    ON DF.data_space_id = F.data_space_id;

The results show recovery is pending for the secondary filegroup files:

Query results

If not, How can I get rid of the ReadOnlyFG after the restore?

You can't delete it from the metadata, but you can make it defunct by dropping it (something that is not normally possible for a filegroup that still contains files):

ALTER DATABASE PiecemealDB_NEW
REMOVE FILEGROUP ReadOnlyFG;

The query above now shows the filegroup as defunct rather than recovery pending:

Defunct filegroup

Objects that were stored on the defunct filegroup cannot be accessed, modified, or dropped. The only way to get to a database state as if the defunct filegroup had never existed is to create a new database and copy the data you want to keep across to it.

Related Question