For a one time the backup restore would be my choice. No reason to bring the database over row by row when you can do it in one quick step.Chuck

Hello all, When migrating data from sql2000 to sql2008 we come across one problem. One of the databases contains only one table and the table size is 300GB with 300k rows. the table is to store image (binary) files so I am not supprised with the size. We cannot accept the 'backup and restore' solution because in this way we can only get a database with compatibility level 80 which is not an 'upgrade'. So the SSIS should be right thing to try. I tried to use the 'SQL Server Import and Export wizard' in SSMS but failed with the error message as 'no enough disk space'. I suppose the SSIS first copied the data to my C drive as a temp file and then transfer to sql2008. the problem is I don't have a 300GB C drive. Did anyone come across this problem? Is SSIS the best solution for this? or 'select ...into...' with OPENROWSET is better? Please share your thoughts. thanks. Regards, cn2500

The no disk space is likely because your log expanded too much because you attempted a 300GB transaction You need to set the commit size on your SSIS package so that it does not try to do it all in one transaction. Run through the import/export wizard again and chose save package instead of run immediately. Then open up the resulting package in BIDS and modify the Destination component. Set the data access mode to fast load and set rows per batch to 50,000. Then run the package. Also - your backup restore solution will work. Once you have restored the database, just change the compatability level on the DB to SQL 2008 Chuck

Thanks for your reply. If you say so I am assuming that the detach-attach is the most efficient way to do this if the mdf file is in the SAN. What I am not sure is whether or not the detach-attache is an official way to upgrade databases from sql 2000 to sql 2008. Is there any potential compatible issue? Cheers cn2500

Can't say that I have ever tried to detach from SQL 2000 and attach to 2008, but it is a documented upgrade path from MS: http://msdn.microsoft.com/en-us/library/ms189625(v=sql.105).aspxChuck

Thanks Chuck. The link is very helpful.

Thanks oldjeep, Both solutions work for me. You saved my life. btw, in this case, which solution do you prefer, ssis or backup-restore? Or which one is more efficient? cheers cn2500