This is from an MS KB, and only applies to SQL 7:
Moving File Groups to a New Disk
With SQL Server 7.0, what do I do when I run out of disk space? Can I
move the file to a new location? How do I do that?
Answer
When you run out of disk space you just add another file to the file
group—you don't have to move the file to a larger disk.
If you want to move a file, the easiest way to move do that is
to take the database offline:
Take the database offline with sp_detach_db
Move the file.
Restart the database with sp_attach_db specifying the path to the
primary file and the moved file. If you must move the file without
taking the database down, here's how to do that:
Create the new file on the new disk.
Shrink the original file to zero size—thus forcing data to move.
Drop the original file.
Note that adding/dropping a file invalidates the log backup chain,
and thus you must backup the entire database (using file or database
backups) before taking log backups again.
Code Sample
Set nocount on
Use Master
go
Drop database Archive
go
CREATE DATABASE Archive
ON
PRIMARY (NAME = Arch1,
FILENAME = 'c:\mssql7\data\archdat1.mdf',
SIZE = 1MB,
MAXSIZE = 200,
FILEGROWTH = 1 ),
(NAME = Arch2,
FILENAME = 'c:\mssql7\data\archdat2.ndf',
SIZE = 1MB,
MAXSIZE = 200,
FILEGROWTH = 1)
LOG ON
(NAME = Archlog1,
FILENAME = 'c:\mssql7\data\archlog1.ldf',
SIZE = 1MB,
MAXSIZE = 200,
FILEGROWTH = 20)
go
Use Archive
go
Create table foo (c1 char(7000))
Declare @giro int
Select @giro = 0
While @giro < 400
begin
Insert foo values (convert(char(5),@giro))
Select @giro=@giro+1
end
---- Have a look to the space allocated using Enterprise Manager.
---- To create a new file using EM with dimension egual or grater then
archdat2
-----run shrinkfile and look what happened to archdat2
-----DBCC SHRINKFILE (arch2, emptyfile)
--Dale E. Reed Jr. Emerald and RadiusNT/X__________________________________________IEA Software, Inc. www.iea-software.com
For more information about this list (including removal) go to:http://www.iea-software.com/support/maillists/liststart