Restore Database With Move

OK. I have a good one. I have the following setup:
ServerA
Data on: C:\Data
Log on: C:\Logs
ServerB
System Databases on: D:\SysData
Data on: E:\Data
Indexes on : D:\Indexes
Log on: F:\Logs
So, what I want to do is restore a database from ServerA to ServerB. Placing the
files and data in the correct locations. I want the data in a filegroup called
DatabaseName_Data. Problem is when I restore to ServerB, the data always goes to
PRIMARY filegroup during restore. The only thing I can find is to manually
change the location of the data for each table in Enterprise Manager (YUCK!)
Isn't there an easier way?
Thanks for the help
Darrell
You cannot change file or filegroup layout using backup/restore (which basically gives you an image
of your database). Either use EM to move or do it from QA using more or less clever/complicated TSQL
scripts. You change location of a table by (re)creating a clustered index. For an index, you rebuild
the index.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Darrell" <Darrell.Wright.nospam@.okc.gov> wrote in message
news:ehhWgpTvFHA.3740@.TK2MSFTNGP14.phx.gbl...
> OK. I have a good one. I have the following setup:
> ServerA
> Data on: C:\Data
> Log on: C:\Logs
> ServerB
> System Databases on: D:\SysData
> Data on: E:\Data
> Indexes on : D:\Indexes
> Log on: F:\Logs
> So, what I want to do is restore a database from ServerA to ServerB. Placing the files and data in
> the correct locations. I want the data in a filegroup called DatabaseName_Data. Problem is when I
> restore to ServerB, the data always goes to PRIMARY filegroup during restore. The only thing I can
> find is to manually change the location of the data for each table in Enterprise Manager (YUCK!)
> Isn't there an easier way?
> Thanks for the help
> Darrell
|||Refer to "How to restore files to a new location (Transact-SQL)" in BOL...
"Darrell" <Darrell.Wright.nospam@.okc.gov> wrote in message
news:ehhWgpTvFHA.3740@.TK2MSFTNGP14.phx.gbl...
> OK. I have a good one. I have the following setup:
> ServerA
> Data on: C:\Data
> Log on: C:\Logs
> ServerB
> System Databases on: D:\SysData
> Data on: E:\Data
> Indexes on : D:\Indexes
> Log on: F:\Logs
> So, what I want to do is restore a database from ServerA to ServerB.
> Placing the files and data in the correct locations. I want the data in a
> filegroup called DatabaseName_Data. Problem is when I restore to ServerB,
> the data always goes to PRIMARY filegroup during restore. The only thing I
> can find is to manually change the location of the data for each table in
> Enterprise Manager (YUCK!)
> Isn't there an easier way?
> Thanks for the help
> Darrell