restore DB on SQL 2005 multiple files

Hello,

I have a SQL 2000 DB. Current tables in sql 2000 DB are in single file. I am planningto migrate the DB to sql 2005. I am going to partition the tables in sql 2005 and have multiple files. What is the best way to do this? Would backup/restore work? If I restore onto sql 2005 from sql 2000 backup, will the tables spread over different files automatically or not? Any ideas will be appreciated...

Thanks........

Using Backup/Restore may be the 'safest' method to migrate a database. Be sure to rebuild the indexes and update the statistics after restoring.

Once restored, you can add new files for the database.

Refer to this link for the steps to move tables to the new files.

http://sqljunkies.com/HowTo/B9F7F302-964A-4825-9246-6143A8681900.scuk

(And most likely, you really don't want tables to 'stripe' across multiple files.)

Restore Database with Multiple Files

All
I have a database with multiple files ie. an mdf and two ndf. I have
performed a full database backup using BACKUP DATABASE dbname to DISK =
'filename' and I would like to restore the database to another server and
only have one file ie. PRIMARY mdf instead of the three files. Can anyone
advise how this can be achieved.
Thanks
That is not possible. A SQL Server backup is more or less a binary dump if the data pages, so SQL
Server need to put each page in the same file and on the same page address (as other pages might be
pointing to this page). Restore as original, and do the shuffling after the restore. Or do the
shuffling before the backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"David" <David@.discussions.microsoft.com> wrote in message
news:89EB659B-C421-4DB2-BB1B-17D30C6CFB8A@.microsoft.com...
> All
> I have a database with multiple files ie. an mdf and two ndf. I have
> performed a full database backup using BACKUP DATABASE dbname to DISK =
> 'filename' and I would like to restore the database to another server and
> only have one file ie. PRIMARY mdf instead of the three files. Can anyone
> advise how this can be achieved.
> Thanks
>

Restore Database with Multiple Files

All
I have a database with multiple files ie. an mdf and two ndf. I have
performed a full database backup using BACKUP DATABASE dbname to DISK =
'filename' and I would like to restore the database to another server and
only have one file ie. PRIMARY mdf instead of the three files. Can anyone
advise how this can be achieved.
ThanksThat is not possible. A SQL Server backup is more or less a binary dump if t
he data pages, so SQL
Server need to put each page in the same file and on the same page address (
as other pages might be
pointing to this page). Restore as original, and do the shuffling after the
restore. Or do the
shuffling before the backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"David" <David@.discussions.microsoft.com> wrote in message
news:89EB659B-C421-4DB2-BB1B-17D30C6CFB8A@.microsoft.com...
> All
> I have a database with multiple files ie. an mdf and two ndf. I have
> performed a full database backup using BACKUP DATABASE dbname to DISK =
> 'filename' and I would like to restore the database to another server and
> only have one file ie. PRIMARY mdf instead of the three files. Can anyone
> advise how this can be achieved.
> Thanks
>

Restore Database with Multiple Files

All
I have a database with multiple files ie. an mdf and two ndf. I have
performed a full database backup using BACKUP DATABASE dbname to DISK = 'filename' and I would like to restore the database to another server and
only have one file ie. PRIMARY mdf instead of the three files. Can anyone
advise how this can be achieved.
ThanksThat is not possible. A SQL Server backup is more or less a binary dump if the data pages, so SQL
Server need to put each page in the same file and on the same page address (as other pages might be
pointing to this page). Restore as original, and do the shuffling after the restore. Or do the
shuffling before the backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"David" <David@.discussions.microsoft.com> wrote in message
news:89EB659B-C421-4DB2-BB1B-17D30C6CFB8A@.microsoft.com...
> All
> I have a database with multiple files ie. an mdf and two ndf. I have
> performed a full database backup using BACKUP DATABASE dbname to DISK => 'filename' and I would like to restore the database to another server and
> only have one file ie. PRIMARY mdf instead of the three files. Can anyone
> advise how this can be achieved.
> Thanks
>sql