USE [master]

CREATE DATABASE [BIGDB]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'BIGDB_PRIMARY', FILENAME = N'C:\MSSQLData\BIGDB_PRIMARY.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 5120KB ), 
 FILEGROUP [DATA]  DEFAULT
( NAME = N'BIGDB_DATA1', FILENAME = N'C:\MSSQLData\BIGDB_DATA1.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 5120KB ), 
 FILEGROUP [PART_A001] 
( NAME = N'BIGDB_PART_A001', FILENAME = N'C:\MSSQLData\BIGDB_PART_A001.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 5120KB ), 
 FILEGROUP [PART_A002] 
( NAME = N'BIGDB_PART_A002', FILENAME = N'C:\MSSQLData\BIGDB_PART_A002.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 5120KB ), 
 FILEGROUP [PART_A003] 
( NAME = N'BIGDB_PART_A003', FILENAME = N'C:\MSSQLData\BIGDB_PART_A003.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 5120KB ), 
 FILEGROUP [PART_A004] 
( NAME = N'BIGDB_PART_A004', FILENAME = N'C:\MSSQLData\BIGDB_PART_A004.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 5120KB ), 
 FILEGROUP [PART_A005] 
( NAME = N'BIGDB_PART_A005', FILENAME = N'C:\MSSQLData\BIGDB_PART_A005.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 5120KB ), 
 FILEGROUP [PART_A006] 
( NAME = N'BIGDB_PART_A006', FILENAME = N'C:\MSSQLData\BIGDB_PART_A006.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 5120KB ), 
 FILEGROUP [PART_A007] 
( NAME = N'BIGDB_PART_A007', FILENAME = N'C:\MSSQLData\BIGDB_PART_A007.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 5120KB ), 
 FILEGROUP [PART_A008] 
( NAME = N'BIGDB_PART_A008', FILENAME = N'C:\MSSQLData\BIGDB_PART_A008.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 5120KB ), 
 FILEGROUP [PART_A009] 
( NAME = N'BIGDB_PART_A009', FILENAME = N'C:\MSSQLData\BIGDB_PART_A009.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 5120KB ), 
 FILEGROUP [PART_A010] 
( NAME = N'BIGDB_PART_A010', FILENAME = N'C:\MSSQLData\BIGDB_PART_A010.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 5120KB ), 
 FILEGROUP [PART_A011] 
( NAME = N'BIGDB_PART_A011', FILENAME = N'C:\MSSQLData\BIGDB_PART_A011.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 5120KB ), 
 FILEGROUP [PART_A012] 
( NAME = N'BIGDB_PART_A012', FILENAME = N'C:\MSSQLData\BIGDB_PART_A012.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 5120KB ), 
 FILEGROUP [PART_A013] 
( NAME = N'BIGDB_PART_A013', FILENAME = N'C:\MSSQLData\BIGDB_PART_A013.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 5120KB ), 
 FILEGROUP [PART_A014] 
( NAME = N'BIGDB_PART_A014', FILENAME = N'C:\MSSQLData\BIGDB_PART_A014.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 5120KB ), 
 FILEGROUP [PART_A015] 
( NAME = N'BIGDB_PART_A015', FILENAME = N'C:\MSSQLData\BIGDB_PART_A015.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 5120KB ), 
 FILEGROUP [PART_A016] 
( NAME = N'BIGDB_PART_A016', FILENAME = N'C:\MSSQLData\BIGDB_PART_A016.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 5120KB ), 
 FILEGROUP [PART_A017] 
( NAME = N'BIGDB_PART_A017', FILENAME = N'C:\MSSQLData\BIGDB_PART_A017.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 5120KB ), 
 FILEGROUP [PART_A018] 
( NAME = N'BIGDB_PART_A018', FILENAME = N'C:\MSSQLData\BIGDB_PART_A018.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 5120KB ), 
 FILEGROUP [PART_A019] 
( NAME = N'BIGDB_PART_A019', FILENAME = N'C:\MSSQLData\BIGDB_PART_A019.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 5120KB ), 
 FILEGROUP [PART_A020] 
( NAME = N'BIGDB_PART_A020', FILENAME = N'C:\MSSQLData\BIGDB_PART_A020.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 5120KB ), 
 FILEGROUP [PART_A021] 
( NAME = N'BIGDB_PART_A021', FILENAME = N'C:\MSSQLData\BIGDB_PART_A021.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 5120KB ), 
 FILEGROUP [PART_A022] 
( NAME = N'BIGDB_PART_A022', FILENAME = N'C:\MSSQLData\BIGDB_PART_A022.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 5120KB ), 
 FILEGROUP [PART_A023] 
( NAME = N'BIGDB_PART_A023', FILENAME = N'C:\MSSQLData\BIGDB_PART_A023.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 5120KB ), 
 FILEGROUP [PART_A024] 
( NAME = N'BIGDB_PART_A024', FILENAME = N'C:\MSSQLData\BIGDB_PART_A024.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 5120KB ), 
 FILEGROUP [PART_B001] 
( NAME = N'BIGDB_PART_B001', FILENAME = N'C:\MSSQLData\BIGDB_PART_B001.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 5120KB ), 
 FILEGROUP [PART_B002] 
( NAME = N'BIGDB_PART_B002', FILENAME = N'C:\MSSQLData\BIGDB_PART_B002.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 5120KB ), 
 FILEGROUP [PART_B003] 
( NAME = N'BIGDB_PART_B003', FILENAME = N'C:\MSSQLData\BIGDB_PART_B003.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 5120KB ), 
 FILEGROUP [PART_B004] 
( NAME = N'BIGDB_PART_B004', FILENAME = N'C:\MSSQLData\BIGDB_PART_B004.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 5120KB )
 LOG ON 
( NAME = N'BIGDB_log', FILENAME = N'C:\MSSQLData\BIGDB.ldf' , SIZE = 5120KB , MAXSIZE = 2048GB , FILEGROWTH = 5120KB )
GO
-- Mise en ReadOnly
USE [BIGDB]
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A001] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A002] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A003] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A004] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A005] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A006] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A007] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A008] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A009] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A010] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A011] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A012] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A013] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A014] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A015] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A016] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A017] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A018] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A019] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A020] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A021] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A022] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A023] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A024] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_B001] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_B002] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A001] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A002] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A003] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A004] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A005] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A006] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A007] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A008] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A009] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A010] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A011] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A012] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A013] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A014] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A015] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A016] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A017] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A018] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A019] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A020] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A021] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A022] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A023] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_A024] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_B001] READONLY
	ALTER DATABASE [BIGDB] MODIFY FILEGROUP [PART_B002] READONLY



-- Backups
-- 1. FileGroups d'Archives readonly
BACKUP DATABASE BIGDB FILEGROUP='PART_A001', FILEGROUP='PART_A002', FILEGROUP='PART_A003', FILEGROUP='PART_A004', FILEGROUP='PART_A005', FILEGROUP='PART_A006', FILEGROUP='PART_A007', FILEGROUP='PART_A008', FILEGROUP='PART_A009', FILEGROUP='PART_A010', FILEGROUP='PART_A011', FILEGROUP='PART_A012', FILEGROUP='PART_A013', FILEGROUP='PART_A014', FILEGROUP='PART_A015', FILEGROUP='PART_A016', FILEGROUP='PART_A017', FILEGROUP='PART_A018', FILEGROUP='PART_A019', FILEGROUP='PART_A020', FILEGROUP='PART_A021', FILEGROUP='PART_A022', FILEGROUP='PART_A023', FILEGROUP='PART_A024' TO DISK = 'C:\temp\BIGDB_PARTIAL_RO_FG_201610041735.bak' WITH INIT
BACKUP DATABASE BIGDB FILEGROUP='PART_B001', FILEGROUP='PART_B002' TO DISK = 'C:\temp\BIGDB_PARTIAL_RO_FG2_201610041735.bak' WITH INIT
-- 2. Readwrite FileGroups
BACKUP DATABASE BIGDB READ_WRITE_FILEGROUPS TO DISK = 'c:\temp\BIGDB_RW_FULL.bak'
BACKUP LOG BIGDB TO DISK = 'c:\temp\BIGDB_LOG1.trn'
BACKUP DATABASE BIGDB READ_WRITE_FILEGROUPS TO DISK = 'c:\temp\BIGDB_RW_DIFF.bak'
BACKUP LOG BIGDB TO DISK = 'c:\temp\BIGDB_LOG2.trn'

-- Restore
-- 1. ReadWrite
RESTORE DATABASE BIGDB READ_WRITE_FILEGROUPS FROM DISK = 'c:\temp\BIGDB_RW_FULL.bak' WITH PARTIAL,NORECOVERY
RESTORE DATABASE BIGDB READ_WRITE_FILEGROUPS FROM DISK = 'c:\temp\BIGDB_RW_DIFF.bak' WITH PARTIAL,NORECOVERY
RESTORE LOG BIGDB FROM DISK = 'c:\temp\BIGDB_LOG2.trn' WITH RECOVERY
-- 2. Archives readonly
RESTORE DATABASE BIGDB FILEGROUP='PART_B001', FILEGROUP='PART_B002' FROM DISK = 'C:\temp\BIGDB_PARTIAL_RO_FG2_201610041735.bak' WITH RECOVERY
RESTORE DATABASE BIGDB FILEGROUP='PART_A001', FILEGROUP='PART_A002', FILEGROUP='PART_A003', FILEGROUP='PART_A004', FILEGROUP='PART_A005', FILEGROUP='PART_A006', FILEGROUP='PART_A007', FILEGROUP='PART_A008', FILEGROUP='PART_A009', FILEGROUP='PART_A010', FILEGROUP='PART_A011', FILEGROUP='PART_A012', FILEGROUP='PART_A013', FILEGROUP='PART_A014', FILEGROUP='PART_A015', FILEGROUP='PART_A016', FILEGROUP='PART_A017', FILEGROUP='PART_A018', FILEGROUP='PART_A019', FILEGROUP='PART_A020', FILEGROUP='PART_A021', FILEGROUP='PART_A022', FILEGROUP='PART_A023', FILEGROUP='PART_A024' FROM DISK = 'C:\temp\BIGDB_PARTIAL_RO_FG_201610041735.bak' WITH RECOVERY


