March 10, 2002 at 2:16 pm
I am going to upgrade a SQL6.5 server with 4G of Data to SQL7.0 sp3. What would be the best procedure to do it??? One of my friend did a straight 6.5 to 7 upgrade and it stalled halfway through it and damage all the data.
March 10, 2002 at 3:21 pm
Backup the 6.5 server in full first and restore to another location for safety and to be sure your backup does work. Next I would go with builder another server with 7 on it and transfer the tables and data structure and objects over. The build the indexes and user accounts. My reason for this and not the standard upgrade path is just like upgrading Windows between version when you upgrade SQL between versions you can end up with a lot of crap just taking up space or damaged objects (data) or like your friend you might stall. I did take a simple database up thru the upgrade path but it was only tables, data and logins (no view, procedures, triggers, etc.) and had no issues but rebuilding a 6.5 backup is simply worse than just migratting the data to a new server. Anyone disagree or have a better suggestion.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 10, 2002 at 6:12 pm
I took the path of making a full backup of SQL and the server, then running the upgrade wizard (no problems encountered). Once the upgrade was complete, I moved the mdf's/ldf's to another machine, then formatted and rebuilt it completely. Once I had SQL installed and running, I just stopped the service and moved all the files back, done deal. I see no reason not to use the upgrade wizard especially if you're doing the entire server.
Andy
March 12, 2002 at 2:50 am
thanks for all the reply. I was thinking on the line of having another server running 7.0 and do a full data transfer across to the new server and make sure all the triggers, view and data are in the new server and they are working. then I will uninstall sql6.5 and install 7.0. Then transfer the data back onto the original server. what does you guys think about that?
March 12, 2002 at 9:40 am
If you have a second server, here's what I did:
1. Install SQL 7 on server B
2. Script all objects on Server A, execute this on Server B
3. bcp all data out of server A and bcp into server B. I had a script to automate this from each table and it worked like a charm.
4. Verify things look good on server B (row counts, object counts, etc.)
I never went back to Server A because Server B was new with better hardware, but if you want to
5. Wipe the disk if you can, otherwise, uninstall and reinstall SQL on Server A.
6. I like backup restore, but detach/attach works just as well to move the data back to Server A.
Steve Jones
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply