When you divide a database into filegroups, you can control its distribution across multiple disks, which can help performance. You can achieve similar performance benefits with a stripe set of disks (a RAID array), but adding a file to a filegroup is easy and expanding a stripe set isn't. The Primary filegroup contains the primary file, and always contains the system tables. The Primary filegroup also contains any files that you don't specifically place into a filegroup. So if your database has a primary file and one or two secondary files and you haven't specified any filegroups, all files reside on the Primary filegroup. The transaction log files aren't part of a filegroup.
The Define File Groups listing (online at http://www.sqlmag .com) shows the script for defining filegroups according to Books Online (BOL). We used the <filespec> in the Create Database code. The difference in these methods is that to use filegroups, you add the FILEGROUP keyword and the name of the filegroup before specifying the file or files for that filegroup. Suppose you want to split the Budget database into filegroups. You might run the code in the Split Database file, online at http://www.sqlmag.com. Note that only the primary file has the .mdf extension. The Primary filegroup doesn't require the FILEGROUP keyword. I split the database into the Primary filegroup, the Projections filegroup (for budget estimates), and the History filegroup (for historical data). The log is on another disk for recoverability. You also get benefits from moving the data off the filegroup that contains the system files and from spreading the data across multiple disks. These benefits include increased performance and the ability to back up and restore only selected filegroups, thus speeding the backup process.
You get another benefit when you perform a backup. The budget projection data, which changes rapidly, is on one filegroup, and the historical data, which doesn't change, is on another filegroup. You can back up the volatile filegroups regularly and back up the static filegroups infrequently. Because the historical data probably comprises a substantial percentage of the total data set, you significantly reduce your backup times. (For more about database backup and recovery, see Wayne Snyder, "Ensuring Up-to-the-Minute Database Recovery," October 1999.)
Other Options
You can specify two other options; the first is for backward compatibility. The FOR LOAD option is necessary in SQL Server 6.5 because during a restore, you first rebuild the database and then restore the backup. FOR LOAD sets the database to dbo use only so that nobody except the sa or DBA can access it while it's being restored. SQL Server 7.0 recreates the database and loads the data from the backup in one operation, so the dbo use only option isn't necessary. However, you can use the FOR LOAD option to rebuild the database as dbo use only, then reload the data in a separate step.
The FOR ATTACH option is new in SQL Server 7.0. You use this option if a set of database files is already present. Just let SQL Server know they exist, so that it can add this database to its list of available databases.
Moving a Database
Also new in SQL Server 7.0 is the ability to detach a database and move it to another server. The command is simple:
EXEC sp_detach_db 'Budget'
The current server doesn't show this database, but the files are still on the disk. Now you can move the files to another server. Then run the sp_attach_db statement to tell SQL Server what this database's files are and where they are. Suppose you moved the Budget database to a new server, copying the data to a new directory on the E drive and the log to a new directory on the F drive. You then run sp_attach_db as follows:
EXEC sp_attach_db 'budget',
@filename1 = 'e:\sqldata\Budgetdata.mdf',
@filename2 = 'f:\sqllog\Budgetlog.ldf'
The new server places an entry in the sysdatabases table for the database, and the Budget database transfer is complete.
Removing a Database
The syntax for removing a database is still DROP DATABASE plus the name of the database or databases you want to drop. You can't drop a database that's in use, including any published for replication. But in SQL Server 7.0, you don't need to worry about removing the devices—there are none—and SQL Server cleans up the files from the hard disk.
Even if you always use the GUI to create databases, knowing the T-SQL code gives you a feel for what is happening behind the scenes. And generating the code for any database is always a good idea. (In Enterprise Manager, right-click the database, select All Tasks, Generate SQL Scripts, and then generate and save the code.) Remember, always back up the Master database after creating or dropping databases.