May 8, 2008 at 6:29 pm
HI,
I have to restore database in to a new server. But my database has 3 datafiles and I want restore them to a new server as a single datafile, without loosing data. Is it possible ? I know the otherway is possible, splitting one datafile to many. But this one I am not sure how ?
Thanks,
-V
May 8, 2008 at 10:35 pm
You cannot do this with a restore - all 3 files will need to be restored initially.
Once you have the files restored, the data in the 2 files that you don't want can be moved the the other file using DBCC SHRINKFILE by specifying the "EMPTYFILE" option. Make sure you only allow the one file to automatically grow. I would also suggest you remove free space from the file that you wish to remove prior to doing the first shrinkfile - otherwise, this will move data into that data file which will then have to be moved again when you do the second shrinkfile.
For info on this have a look in BOL for "DBCC SHRINKFILE".
May 9, 2008 at 8:10 am
Also, after you delete (or add) files: MAKE SURE you take a full backup of the db before you take any tran log or differential backups:
http://msdn.microsoft.com/en-us/library/aa933089(SQL.80).aspx
May 9, 2008 at 9:14 am
I'd follow the advice above, especially Robert's, but understand that you can only get back to the MDF file. You can't remove that one since it has system information in it.
The other thing you can do it restore this as a different db, then transfer the schema and data over to a new database that's configured as you want it.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 9, 2008 at 5:04 pm
Thank you for your answers. I will try, any way its a brand new server !!!
-V
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply