/********************************** 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 :
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
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:
The results show recovery is pending for the secondary filegroup files:
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):
The query above now shows the filegroup as defunct rather than recovery pending:
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.