I have a 20TB SQL Server 2005 database that I would like to move to SQL Server 2008. The database has around 700 files.
Our production support team does not want a long downtime (1-2 days maximum) and they are also unable to allocate another 20TB storage for the new instance.
Under these circumstances, I am thinking of the detach / attach approach for migration. I will perform the following steps.
An sp_detach_db on the old production database.
use master go Alter Database ProdDB2005 Set SIGLE_USER; exec sp_detach_db @dbname='ProdDB2005', @skipchecks='true'
Create a new db on the SQL 2008 instance using the Create Database for Attach command.
Create Database ProdDB2008 On (FILENAME = 'x:\..\..\primary.mdf'), (FILENAME = 'x:\..\..\log.ldf'), (FILENAME = 'x:\..\..\secondary_001.ndf'), (FILENAME = 'x:\..\..\secondary_002.ndf'), .... .... (FILENAME = 'x:\..\..\secondary_700.ndf') For Attach;
I have tried this command on a 1GB development DB and it worked in about 1 minute on a development PC (3GB RAM, Dual Core CPU). Our production server has 32GB of RAM, 8 CPUs and the database files are mounted on SAN.
I would like to estimate how long the two above mentioned steps might take to run so an appropriate downtime can be planned. I would also like your expert suggestions on whether there are better ways to migrate this DB.