MS-SQL Database Restore - Only one Filegroup-CodePudding

I'm currently struggeling with the MS-SQL Backup/Restore.

Situation: I have a windows-service running that creates frequent (every 15 min) automatic backups that are shipped to a network storage.

The problem: The database contains many temporal tables, where I'm not interisted in making backup of the history data.

Therefore I have moved the history tables to a second filegroup.

What I need is a backup that includes the database schema and the data of the primary filegroup only.

From that backup I need to restore a fully functional database again.

Furthermore the database is in simple recovery model and as I already mentioned I need to do it by sql commands and cannot use the SSMS to script the schema / data as I'm doing it from a windows-service programatically.

Thank you for any idea.

CodePudding user response：

I need to do it by sql commands and cannot use the SSMS to script the schema / data as I'm doing it from a windows-service programatically.

You can use the SMO API to script schema/data programmatically, which is what SSMS uses.

CodePudding user response：

This is a bad idea. You should use full recovery and log backups if you need the data backed-up frequently.
In SIMPLE recovery all read-write filegroups must be included in a backup.
In FULL recovery what you propose is technically possible (the best kind of possible). You can take a backup of the PRIMARY filegroup, restore it with recovery, which will make all the secondary filegroups defunct. Then turn system_versioning off on your temporal tables to make them updatable.

Here's a sample:

use master
drop database piecemealtest
go
/****** Object:  Database [piecemealtest]    Script Date: 12/15/2021 7:16:57 AM ******/
CREATE DATABASE [piecemealtest]
 ON  PRIMARY 
( NAME = N'piecemealtest', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\piecemealtest.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB ), 
 FILEGROUP [fg2] 
( NAME = N'fg2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\fg2.ndf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'piecemealtest_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\piecemealtest_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
 
GO

ALTER DATABASE [piecemealtest] SET RECOVERY full
go
backup database piecemealtest to disk='nul'
GO

go
use piecemealtest



go
CREATE TABLE DepartmentHistory
(
    DeptID INT NOT NULL
  , DeptName VARCHAR(50) NOT NULL
  , ManagerID INT NULL
  , ParentDeptID INT NULL
  , SysStartTime DATETIME2 NOT NULL
  , SysEndTime DATETIME2 NOT NULL
) on fg2;
GO

CREATE CLUSTERED COLUMNSTORE INDEX IX_DepartmentHistory
    ON DepartmentHistory;
CREATE NONCLUSTERED INDEX IX_DepartmentHistory_ID_PERIOD_COLUMNS
    ON DepartmentHistory (SysEndTime, SysStartTime, DeptID);
GO

CREATE TABLE Department
(
    DeptID int NOT NULL PRIMARY KEY CLUSTERED
  , DeptName VARCHAR(50) NOT NULL
  , ManagerID INT NULL
  , ParentDeptID INT NULL
  , SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL
  , SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL
  , PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory));



go

insert into Department(DeptID,DeptName,ManagerId,ParentDeptID)
select object_id, 'dept'   name, 0,0
from sys.objects 

update Department set managerid = 4
update Department set managerid = 5

delete from Department where deptid  = 3

backup database piecemealtest filegroup='primary' to disk = 'piecemeal.primary.bak'

use master 
drop database piecemealtest

restore database piecemealtest filegroup='PRIMARY' from disk= 'piecemeal.primary.bak' with partial, recovery
--Msg 3127, Level 16, State 1, Line 71
--The file 'fg2' of restored database 'piecemealtest' is being left in the defunct state because the database is using the simple recovery model and the file is marked for read-write access. Therefore, only read-only files can be recovered by piecemeal restore.
--RESTORE DATABASE ... FILE=<name> successfully processed 393 pages in 0.010 seconds (306.396 MB/sec).

use piecemealtest 

select * from Department

update Department set managerid = 8 -- fails
--Msg 8653, Level 16, State 1, Line 78
--The query processor is unable to produce a plan for the table or view 'DepartmentHistory' because the table resides in a filegroup that is not online.
go
alter table Department set (system_versioning=off)
go
update Department set managerid = 8 -- works

CodePudding user response：

I would consider creating another database to replicate data and objects from the target database, and then performing the mentioned backup (every 15 minutes) for this new database.