As far as I am concerned, we need to break up our big database (more than 500 GB) into a series of filegroups for better performance, easier management and faster disaster recovery.

– Filegroup with current active data
– Filegroup with old and obselete data

First of all, create a series of filegroup, and then move your tables to each filegrousp

— Script to move table to filegrup

ALTER DATABASE MyDatabase
ADD FILE ( name = MyTable1
, filename = ‘D:\MSSQL\Data\mytable1.ndf’
, size = 10
, maxsize = 50
, filegrowth = 15% )
TO FILEGROUP SECONDARY;

— Script to do a full backup database

BACKUP DATABASE [MyDatabase] TO  DISK = N’D:\MSSQL\Backup\MyDatabase.bak‘
WITH NOFORMAT, INIT,  NAME = N’MyDatabase-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

— Script to do a transaction log backup

BACKUP LOG [MyDatabase] TO  DISK = N’D:\MSSQL\Backup\MyDatabase.trn’ WITH NOFORMAT, INIT,  NAME = N’MyDatabase-Transaction Log  Backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

 –To restore a piece of Filegroup (secondary), keyword is PARTIAL!

RESTORE DATABASE [MyDatabase]
FILE = N’MyDatabase_Secondary’
FROM  DISK = N’D:\MSSQL\Backup\MyDatabase.bak’
WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10, PARTIAL
GO