-------------------------------------------------------
-- Range Partitions Exercise 
-- FILE: Script1 - AddFilegroups.sql

-- SQL Server 2005
-- Scripts tested on SQL Server 2005
-- 	9.00.1187.07 (June CTP)

-- Partitioned Tables Hands-on Lab Exercises
-- July 2005
-------------------------------------------------------

-------------------------------------------------------
-- Lab Setup: Step One
-- You must have a subdirectory of C:\AdventureWorks
-- OR REPLACE ALL references of this path.
-------------------------------------------------------
!!mkdir C:\AdventureWorks
GO

-------------------------------------------------------
-- Lab Setup: Step Two
-- Create a backup of AdventureWorks. If you need to 
-- revert back to this use the RESTORE command below.
-------------------------------------------------------

--BACKUP DATABASE AdventureWorks
--	TO DISK = 'C:\TechReady\AdventureWorksBackup.BAK'
--	WITH INIT
--
--RESTORE DATABASE AdventureWorks
--	FROM DISK = 'C:\TechReady\AdventureWorksBackup.BAK'
--	WITH FILE = 1
--
go

-------------------------------------------------------
-- Adding Filegroups and Files

-- This script adds 4 FILEGROUPS and 4 FILES
-- (one for each filegroup) to the AdventureWorks 
-- database. 
-------------------------------------------------------
GO

ALTER DATABASE AdventureWorks
ADD FILEGROUP [2003Q3]
GO

ALTER DATABASE AdventureWorks
ADD FILEGROUP [2003Q4]
GO

ALTER DATABASE AdventureWorks
ADD FILEGROUP [2004Q1]
GO

ALTER DATABASE AdventureWorks
ADD FILEGROUP [2004Q2]
GO

-------------------------------------------------------
-- For RANGE Partitions
-- Add Files to each Filegroup
-------------------------------------------------------
ALTER DATABASE AdventureWorks
ADD FILE 
  (NAME = N'RPFile1',
  FILENAME = N'C:\AdventureWorks\RPFile1.ndf',
  SIZE = 5MB,
  MAXSIZE = 100MB,
  FILEGROWTH = 5MB)
TO FILEGROUP [2003Q3]
GO

ALTER DATABASE AdventureWorks
ADD FILE 
  (NAME = N'RPFile2',
  FILENAME = N'C:\AdventureWorks\RPFile2.ndf',
  SIZE = 5MB,
  MAXSIZE = 100MB,
  FILEGROWTH = 5MB)
TO FILEGROUP [2003Q4]
GO

ALTER DATABASE AdventureWorks
ADD FILE 
  (NAME = N'RPFile3',
  FILENAME = N'C:\AdventureWorks\RPFile3.ndf',
  SIZE = 5MB,
  MAXSIZE = 100MB,
  FILEGROWTH = 5MB)
TO FILEGROUP [2004Q1]
GO

ALTER DATABASE AdventureWorks
ADD FILE 
  (NAME = N'RPFile4',
  FILENAME = N'C:\AdventureWorks\RPFile4.ndf',
  SIZE = 5MB,
  MAXSIZE = 100MB,
  FILEGROWTH = 5MB)
TO FILEGROUP [2004Q2]
GO

-------------------------------------------------------
-- Verify all files and filegroups
-------------------------------------------------------
USE AdventureWorks
go

sp_helpfile
go