DB Move Question

I a familiar with the command to move a database in this manner:
---
use master
go
sp_detach_db 'test'
go
"move files to new location"
use master
go
sp_attach_db 'test','D:\MSSQL\Data\test_data.mdf','D:\MSSQL\DATA\test_log.ldf'
go
---
However I have a situation where the database is mae up of several data
files and filegroups. For a long time a stored procedure was creating new
data files on the C drive of this server. I finally modified the SP to store
them on the desired data drive, but I have several *.ndf files still on the C
drive. So I am unsure how to get all the files in the same location on the
dedicated data drive.
Since the *.mdf files are already in the proper location and it is only the
DB_LOB filegroup that has files stored on the C drive, how exactly are these
moved?You should be able to move it by:
Detaching the database.
Move the files to the desired location.
Then attach the database, and specify the full path and file name for all the database files.
Just make sure you try above on a test db with similar file/filegroup set first, and also have a
proper backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Shon Miles" <ShonMiles@.discussions.microsoft.com> wrote in message
news:01B11C65-CC08-443B-A954-2A9A090D7FF5@.microsoft.com...
>I a familiar with the command to move a database in this manner:
> ---
> use master
> go
> sp_detach_db 'test'
> go
> "move files to new location"
> use master
> go
> sp_attach_db 'test','D:\MSSQL\Data\test_data.mdf','D:\MSSQL\DATA\test_log.ldf'
> go
> ---
> However I have a situation where the database is mae up of several data
> files and filegroups. For a long time a stored procedure was creating new
> data files on the C drive of this server. I finally modified the SP to store
> them on the desired data drive, but I have several *.ndf files still on the C
> drive. So I am unsure how to get all the files in the same location on the
> dedicated data drive.
> Since the *.mdf files are already in the proper location and it is only the
> DB_LOB filegroup that has files stored on the C drive, how exactly are these
> moved?|||Thanks I will try that.
"Shon Miles" wrote:
> I a familiar with the command to move a database in this manner:
> ---
> use master
> go
> sp_detach_db 'test'
> go
> "move files to new location"
> use master
> go
> sp_attach_db 'test','D:\MSSQL\Data\test_data.mdf','D:\MSSQL\DATA\test_log.ldf'
> go
> ---
> However I have a situation where the database is mae up of several data
> files and filegroups. For a long time a stored procedure was creating new
> data files on the C drive of this server. I finally modified the SP to store
> them on the desired data drive, but I have several *.ndf files still on the C
> drive. So I am unsure how to get all the files in the same location on the
> dedicated data drive.
> Since the *.mdf files are already in the proper location and it is only the
> DB_LOB filegroup that has files stored on the C drive, how exactly are these
> moved?