CREATE DATABASE [TestDB] ON
PRIMARY ( NAME = N'PrimaryTest', FILENAME = N'D:\TestDB\TestDB.mdf' ),
FILEGROUP [FG1] ( NAME = N'TestDB_FG1', FILENAME = N'D:\TestDB\TestDB_FG1.ndf' ),
FILEGROUP [FG2] ( NAME = N'TestDB_FG2', FILENAME = N'D:\TestDB\TestDB_FG2.ndf' ),
FILEGROUP [FG3] ( NAME = N'TestDB_FG3', FILENAME = N'D:\TestDB\TestDB_FG3.ndf' )
LOG ON ( NAME = N'TestDB_log', FILENAME = N'D:\TestDB\TestDB_log.ldf')
GO
USE testdb
Go
CREATE TABLE T1
(ID INT)
ON FG1
Go
INSERT INTO T1 VALUES(1)
Go
CREATE TABLE T2
(ID INT)
ON FG2
Go
INSERT INTO T2 VALUES(1)
Go
CREATE TABLE T3
(ID INT)
ON FG3
Go
INSERT INTO T3 VALUES(1)
Go
CREATE TABLE T4
(ID INT)
ON [PRIMARY]
Go
INSERT INTO T4 VALUES(1)
Go
--BACKUP DATABASE [TestDB] TO
--DISK = N'D:\TestDB Backups\TestDBFullBackUp.bak' WITH NOFORMAT, NOINIT,
--NAME = N'TestDB-Full-Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
--GO
--Backup of Primary filegroup
BACKUP DATABASE [TestDB] FILEGROUP = N'PRIMARY'
TO DISK = N'D:\TestDB Backups\PrimaryFG.bak' WITH NOFORMAT, NOINIT,
NAME = N'PrimaryFG Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
--Backup of FG1 filegroup
BACKUP DATABASE [TestDB] FILEGROUP = N'FG1'
TO DISK = N'D:\TestDB Backups\fg1.bak' WITH NOFORMAT, NOINIT,
NAME = N'FG1 Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
--Backup of FG2 filegroup
BACKUP DATABASE [TestDB] FILEGROUP = N'FG2'
TO DISK = N'D:\TestDB Backups\fg2.bak' WITH NOFORMAT, NOINIT,
NAME = N'FG2 Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
--Backup of FG3 filegroup
BACKUP DATABASE [TestDB] FILEGROUP = N'FG3'
TO DISK = N'D:\TestDB Backups\fg3.bak' WITH NOFORMAT, NOINIT,
NAME = N'FG3 Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
INSERT INTO T1 VALUES(2)
INSERT INTO T2 VALUES(2)
INSERT INTO T3 VALUES(2)
INSERT INTO T4 VALUES(2)
BACKUP LOG [TestDB] TO
DISK = N'D:\TestDB Backups\translog1.trn' WITH NOFORMAT, NOINIT,
NAME = N'TransactionLog1 Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
INSERT INTO T1 VALUES(3)
INSERT INTO T2 VALUES(3)
INSERT INTO T3 VALUES(3)
INSERT INTO T4 VALUES(3)
Go
BACKUP LOG [TestDB] TO
DISK = N'D:\TestDB Backups\translog2.trn' WITH NOFORMAT, NOINIT,
NAME = N'TransactionLog2 Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
INSERT INTO T1 VALUES(4)
INSERT INTO T2 VALUES(4)
INSERT INTO T3 VALUES(4)
INSERT INTO T4 VALUES(4)
Go
BACKUP LOG [TestDB] TO
DISK = N'D:\TestDB Backups\translog3.trn' WITH NOFORMAT, NOINIT,
NAME = N'TransactionLog3 Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
INSERT INTO T1 VALUES(5)
INSERT INTO T2 VALUES(5)
INSERT INTO T3 VALUES(5)
INSERT INTO T4 VALUES(5)
Go
Use master
go
BACKUP LOG [TestDB] TO DISK = N'D:\TestDB Backups\translogtail.trn'
WITH NO_TRUNCATE , NOFORMAT, NOINIT,
NAME = N'TLogTail Backup',
SKIP, NOREWIND, NOUNLOAD, NORECOVERY , STATS = 10
Go
USE MASTER
Go
RESTORE DATABASE TestDB FILEGROUP='Primary' FROM DISK = N'D:\TestDB Backups\primaryfg.bak'
WITH PARTIAL, NORECOVERY
Go
RESTORE LOG TestDB FROM DISK = N'D:\TestDB Backups\translog1.trn' WITH NORECOVERY
Go
RESTORE LOG TestDB FROM DISK = N'D:\TestDB Backups\translog2.trn' WITH NORECOVERY
Go
RESTORE LOG TestDB FROM DISK = N'D:\TestDB Backups\translog3.trn' WITH NORECOVERY
Go
RESTORE LOG TestDB FROM DISK = N'D:\TestDB Backups\translogtail.trn' WITH RECOVERY
Go
SELECT [name], [state_desc]
FROM TestDB.sys.database_files;
GO
Msg 8653, Level 16, State 1, Line 1
The query processor is unable to produce a plan for the table or view 'T3' because the table resides in a filegroup which is not online.
use master
go
RESTORE DATABASE TestDB FILEGROUP='fg1' FROM DISK = N'D:\TestDB Backups\fg1.bak'
WITH NORECOVERY
Go
RESTORE LOG TestDB FROM DISK = N'D:\TestDB Backups\translog1.trn' WITH NORECOVERY
Go
RESTORE LOG TestDB FROM DISK = N'D:\TestDB Backups\translog2.trn' WITH NORECOVERY
Go
RESTORE LOG TestDB FROM DISK = N'D:\TestDB Backups\translog3.trn' WITH NORECOVERY
Go
RESTORE LOG TestDB FROM DISK = N'D:\TestDB Backups\translogtail.trn' WITH RECOVERY
Go
SELECT [name], [state_desc]
FROM TestDB.sys.database_files;
GO
use master
go
RESTORE DATABASE TestDB FILEGROUP='fg2' FROM DISK = N'D:\TestDB Backups\fg2.bak'
WITH NORECOVERY
Go
RESTORE LOG TestDB FROM DISK = N'D:\TestDB Backups\translog1.trn' WITH NORECOVERY
Go
RESTORE LOG TestDB FROM DISK = N'D:\TestDB Backups\translog2.trn' WITH NORECOVERY
Go
RESTORE LOG TestDB FROM DISK = N'D:\TestDB Backups\translog3.trn' WITH NORECOVERY
Go
RESTORE LOG TestDB FROM DISK = N'D:\TestDB Backups\translogtail.trn' WITH RECOVERY
Go
use master
go
RESTORE DATABASE TestDB FILEGROUP='fg3' FROM DISK = N'D:\TestDB Backups\fg3.bak'
WITH NORECOVERY
Go
RESTORE LOG TestDB FROM DISK = N'D:\TestDB Backups\translog1.trn' WITH NORECOVERY
Go
RESTORE LOG TestDB FROM DISK = N'D:\TestDB Backups\translog2.trn' WITH NORECOVERY
Go
RESTORE LOG TestDB FROM DISK = N'D:\TestDB Backups\translog3.trn' WITH NORECOVERY
Go
RESTORE LOG TestDB FROM DISK = N'D:\TestDB Backups\translogtail.trn' WITH RECOVERY
Go
SELECT [name], [state_desc]
FROM TestDB.sys.database_files;
GO
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (1)
Commented: