Restore SQL 2000 Database on SQL 2008 and consolidate 5 mdf files

Question

Restore SQL 2000 Database on SQL 2008 and consolidate 5 mdf files

Roger Abram

Hall of Fame

Points: 3683
More actions
April 14, 2011 at 5:57 am

#248867

Hi,
I'm trying to restore a SQL 2000 database on a SQL 2008 server. However, this SQL 2000 database has 5 mdf files associated with it (I don't know why!) and I'd like to get it down to one.
Below is my code for trying this. Not sure if this is even the best method. However, when I run it I get a message that the backup set holds a backup of a database other than the existing database.
Here is what I'm tyring to do:
RESTORE DATABASE customers
FROM DISK = 'E:\testdatabase_20110414.bak'
WITH MOVE 'SystemData' TO 'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\customers.mdf',
MOVE 'Data1' TO 'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\customers.mdf',
MOVE 'Data2' TO 'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\customers.mdf',
MOVE 'Data3' TO 'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\customers.mdf',
MOVE 'Data4' TO 'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\customers.mdf',
MOVE 'data_log' TO 'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\customers_log.ldf'
Again, I'm trying just to have one mdf file on the new SQL 2008 server. Error message I get is backup set holds a backup of a database other than the existing database. Is this even the best method to consolidate the mdf files in the backup?
Any help would be appreciated.
Thank you.
Rog

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

You cannot consolidate files during a restore. A restore will recreate the database exactly how it was at the time of the backup, file size and file numbers included. Once you have restored it, then you can go about moving objects and/or shrinking with EmptyFile so that you can remove the other files.

btw, there many valid reasons to have more than one data file, especially on larger databases. Why do you want to reduce the DB to just one data file?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Roger Abram Hall of Fame Points: 3683 More actions · Answer 2

Thanks for the input. There are coming off an old server and SQL 2000 to a brand new machine with SQL 2008. The mdf files aren't really that large with the whole database being at just 4 gigs. Don't see why they should continue to be split up in the new environment.

Roger

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 3

I wouldn't go down to a single file or filegroup on the new system. 5 seems like overkill, but I'd still have at least 2.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

Roger Abram (4/14/2011)
Thanks for the input. There are coming off an old server and SQL 2000 to a brand new machine with SQL 2008. The mdf files aren't really that large with the whole database being at just 4 gigs. Don't see why they should continue to be split up in the new environment.
Roger

I doubt you will see any benefit from consolidating the files, so it's probably a waste of time.

If the files are in different file groups, consolidating them could be a major effort and even bigger waste of time.