/*============================================================================
  File:     TestFGDB-Setup.sql

  Summary:  This script sets up a database used in a variety of testing.

  Date:     June 2005

  SQL Server Version: 9.00.1116.08 (April CTP)
------------------------------------------------------------------------------
  Copyright (C) 2005 Kimberly L. Tripp, SYSolutions, Inc.
  All rights reserved.

  For more scripts and sample code, check out 
    http://www.SQLskills.com

  This script is intended only as a supplement to demos and lectures
  given by Kimberly L. Tripp.  
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

-- Create the initial version of the database. You *might* need to change pathes in this 
-- script.

-- DROP DATABASE [TestFGDB]

CREATE DATABASE [TestFGDB]
	ON PRIMARY 
	(NAME = N'TestFGDBData', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\mssql\data\TestFGDB2Data.MDF' , 
	SIZE = 10,
	FILEGROWTH = 10%,
	MAXSIZE = 15)
LOG ON 
	(NAME = N'TestFGDBLog', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\mssql\data\TestFGDBLog.LDF' , 
	SIZE = 10, 
	FILEGROWTH = 10%)
go

-- To set a collation (the is different from the server default) use the COLLATE option 
-- on CREATE DATABASE
--COLLATE collation name

-- To review the list of supported collations in SQL 
--SELECT * FROM ::fn_helpcollations()

-- The filegroup MUST be added FIRST. A file's filegroup choice cannot be changed after
-- the files have been added to the database.
ALTER DATABASE [TestFGDB] ADD FILEGROUP [fg] 
go

-- Adding a file requires the same settings as the file's settings used in create database.
ALTER DATABASE [TestFGDB] 
	ADD FILE
		(NAME = N'TestFGDBFile1', 
		FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\mssql\data\TestFGDBFile1.NDF' , 
		SIZE = 20, FILEGROWTH = 10%) 
		TO FILEGROUP [fg]
go

ALTER DATABASE [TestFGDB] 
	ADD FILE
		(NAME = N'TestFGDBFile2', 
		FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\mssql\data\TestFGDBFile2.NDF' , 
		SIZE = 20, FILEGROWTH = 10%) 
		TO FILEGROUP [fg]
go

ALTER DATABASE [TestFGDB] 
	ADD FILE
		(NAME = N'TestFGDBFile3', 
		FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\mssql\data\TestFGDBFile3.NDF' , 
		SIZE = 20, FILEGROWTH = 10%) 
		TO FILEGROUP [fg]
go

-- Change over the to newly created database and verify the files:
USE TestFGDB
go

-- To set a user-defined filegroup to be the default use:
ALTER DATABASE TestFGDB
	MODIFY FILEGROUP [fg] DEFAULT
go