December 17, 2002 at 4:18 pm
Situation:
I have to upgrade a SQL Server 6.5 production server to SQL Server 7.0. The recommended upgrade path requires 1.5 times as much space as the total disk space occupied by the 6.5 devices. The problem I have run into is that there is not enough additional hard drive space on the production server, so an upgrade is
not possible. The database devices are much larger than the actual amount of data stored in them. Is there A way to resize the devices - make them fit the actual amount of data - so that hard drive space can be reclaimed without losing data? I calculated that if there was a way to resize the devices without destroying data, that 3.5 GB Of hard drive space could be reclaimed.
Does SQL Server 6.5 permit me to do what I just described, or am I screwed?
December 18, 2002 at 3:52 am
Unfortunately as I remember you cannot shirnk the data. However restores on SQL 6.5 are such a pain I suggest bringing up a new server for SQL 7 and use the Data Import wizard to pull structures and data over to make sure it is consistant. This way if you run into issues you can find them and fix them without being down too long if you have to do so, then once fix you remove the data and pull it back in from 6.5. Once done then you can rebuild the old server cleanly with SQL 7 installed and move the databases via backup/restore or detach attach from the temporary one you made, you benifit mostly in that you have more time to consider what issues you run into and how to correct them.
You could also create new devices with same structues (just smaller) and move the data in from each one dropping the old device, this is one work around (I don't like it though as you really need a backout plan and again restores are a pain.
And still another option is script out the devices and there contents then bcp out all the data to flat files either locally or to another machine. Then rebuild the box or drop the devices and do the SQL 7 upgrade then build the databases and bcp in the data again.
I am sure there are many other ways but I personally would try to get the box clean and tidy as possible.
December 18, 2002 at 10:05 am
Antares is correct. I used to run into this problem with 6.5. The only solution I had was to create new dbs on 65, single device, and bcp out bcp in all the data to the new db. Then drop the old one.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply