This demonstrates 2005's ability to bring back filegroups one at a time,

/* SQL Server 2005 Testing */

/* Create our test database */


CREATE DATABASE [MultipleFilesDB] ON  PRIMARY
( NAME = N'MultipleFilesDB_Primary', FILENAME = N'e:\mssql.1\MSSQL\data\MultipleFilesDB_Primary.mdf' , SIZE = 4096KB , FILEGROWTH = 10%),
FILEGROUP [SECONDARY]
( NAME = N'MultipleFilesDB_Secondary', FILENAME = N'e:\mssql.1\MSSQL\data\MultipleFilesDB_Secondary.ndf' , SIZE = 4096KB , FILEGROWTH = 10%)
LOG ON
( NAME = N'MultipleFilesDB_log', FILENAME = N'e:\mssql.1\MSSQL\data\MultipleFilesDB_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'MultipleFilesDB', @new_cmptlevel=90
GO

ALTER DATABASE [MultipleFilesDB] SET RECOVERY FULL
GO



USE [MultipleFilesDB]
GO

IF NOT EXISTS (SELECT groupname FROM dbo.sysfilegroups WHERE (status & 0x10) != 0 AND groupname = N'PRIMARY') ALTER DATABASE [MultipleFilesDB] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO

/* Create a test table on each filegroup */

USE [MultipleFilesDB]
GO

/****** Object:  Table [dbo].[Orders]    Script Date: 01/12/2009 19:47:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[OrdersPrimary](
[OrderID] [int] IDENTITY(1,1) NOT NULL,
[OrderDate] [datetime] NULL,
CONSTRAINT [PK_OrdersPrimary] PRIMARY KEY CLUSTERED
(
[OrderID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO



CREATE TABLE [dbo].[OrdersSecondary](
[OrderID] [int] IDENTITY(1,1) NOT NULL,
[OrderDate] [datetime] NULL,
CONSTRAINT [PK_OrdersSecondary] PRIMARY KEY CLUSTERED
(
[OrderID] ASC
) ON [SECONDARY]
) ON [SECONDARY]
 GO



/* Populate the first order in each table */

INSERT INTO dbo.OrdersPrimary (OrderDate) VALUES (GETDATE())
INSERT INTO dbo.OrdersSecondary (OrderDate) VALUES (GETDATE())

/* Do a full backup */

BACKUP DATABASE [MultipleFilesDB] TO  DISK = N'e:\mssql.1\MSSQL\BACKUP\MultipleFilesDB.bak' WITH NOFORMAT, INIT,
NAME = N'MultipleFilesDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

/* Add another set of rows so we can tell if the t-logs have been applied */

INSERT INTO dbo.OrdersPrimary (OrderDate) VALUES (GETDATE())
INSERT INTO dbo.OrdersSecondary (OrderDate) VALUES (GETDATE())



/* Do a transaction log backup */

BACKUP LOG [MultipleFilesDB] TO  DISK = N'e:\mssql.1\MSSQL\BACKUP\MultipleFilesDB.trn' WITH NOFORMAT, INIT,
NAME = N'MultipleFilesDB-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO


/* Restore just the primary filegroup */

USE master
GO

RESTORE DATABASE [MultipleFilesDB]
FILE = N'MultipleFilesDB_Primary'
FROM  DISK = N'E:\MSSQL.1\MSSQL\Backup\MultipleFilesDB.bak'
WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
GO

/* You'll get an error saying more data is required to come completely online. That's OK.  Now restore the second filegroup: */

RESTORE DATABASE [MultipleFilesDB]
FILE = N'MultipleFilesDB_Secondary'
FROM  DISK = N'E:\MSSQL.1\MSSQL\Backup\MultipleFilesDB.bak'
WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
GO

/* Refresh your list of databases in SSMS, and presto, the database is online.  This is different than 2000 handled it. */

/* Now let's try it again, but we'll add the new "PARTIAL" parameter at the end telling SQL that we're doing a piecemeal restore: */

RESTORE DATABASE [MultipleFilesDB]
FILE = N'MultipleFilesDB_Primary'
FROM  DISK = N'E:\MSSQL.1\MSSQL\Backup\MultipleFilesDB.bak'
WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10, PARTIAL
GO


/* Refresh your list of databases in SSMS, and the database is already online! Let's do a select; */

SELECT * FROM MultipleFilesDB.dbo.OrdersPrimary



/* It shows one row returned because we didn't get our transaction logs.  Now select from the secondary filegroup: */

SELECT * FROM MultipleFilesDB.dbo.OrdersSecondary



/* You'll get an error that the table is in a filegroup that isn't online yet. So let's bring it online. */

RESTORE DATABASE [MultipleFilesDB]
FILE = N'MultipleFilesDB_Secondary'
FROM  DISK = N'E:\MSSQL.1\MSSQL\Backup\MultipleFilesDB.bak'
WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
GO


/* And do another select */
SELECT * FROM MultipleFilesDB.dbo.OrdersSecondary


/* Presto, we're back online. This demonstrates 2005's ability to bring back filegroups one at a time,

but it only works if you restore the primary first.  Watch what happens if we try to restore the second one by itself: */

RESTORE DATABASE [MultipleFilesDB]
FILE = N'MultipleFilesDB_Secondary'
FROM  DISK = N'E:\MSSQL.1\MSSQL\Backup\MultipleFilesDB.bak'
WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
GO

/* Uh oh - it warns us that additional roll forwards are required, and sure enough, if we try a select - */

SELECT * FROM MultipleFilesDB.dbo.OrdersSecondary


/* It fails. The filegroup is offline until we bring it up to speed with matching transaction logs - even though we didn't change it!

So we can't just go restoring filegroups at random without doing some t-log syncing.  The database will be online, but the

restored filegroup may not be online yet. */