Maintenance and Monitoring - Microsoft SQL Server 2008 R2 Administration - page 326

Create a database with multiple file groups, tables, and insert data, and perform a full
database backup using the following TSQL statement:

1. Create a sample PieceMeal database:

CREATE DATABASE [PieceMealDB] ON PRIMARY
( NAME = N'PMDB_PRIMARY',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.
SQL2K8R2U\MSSQL\DATA\PMDB_Primary.mdf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB ),
FILEGROUP [Secondary]
( NAME = N'PMDB_Secondary',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.
SQL2K8R2U\MSSQL\DATA\PMDB_Secondary.ndf' ,
SIZE = 1072KB , FILEGROWTH = 1024KB ),
FILEGROUP [Tertiary]
( NAME = N'PMDB_Tertiary',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.
SQL2K8R2U\MSSQL\DATA\PMDB_Tertiary.ndf' ,
SIZE = 512KB , FILEGROWTH = 512KB )
LOG ON
( NAME = N'PMDB_log',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.
SQL2K8R2U\MSSQL\DATA\PMDB_log.ldf' ,
SIZE = 1024KB , FILEGROWTH = 20%)
GO
--Make SECONDARY file group as default
ALTER DATABASE [PieceMealDB] MODIFY FILEGROUP [Secondary] DEFAULT

2. Get the physical location of the newly created database:

USE master
go
SELECT name,physical_name from sys.master_files where name like
'%PMDB%'
GO

CLONE 2 tables from AdventureWorks2008R2 database; the sample
database can be downloaded from CodePlex site www.codeplex.com .
Extract the AdventureWorks2008DBScriptsR2.msi package file that
will create %\folder name\AdventureWorks2008R2-Samples\% folder.
USE PieceMealDB
GO

CREATE TABLE [dbo].[Address](
[AddressID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT
NULL,
[AddressLine1] [nvarchar](60) NOT NULL,