SQL 7 to SQL 2000 Upgrade - Move Install Directories?
-
I am testing an upgrade from SQL 7.0 to SQL 2000 on a test server and the upgrade appeared to work with one problem. I was not given the option to define where the directories will go. It automatically used the current SQL 7 directories for SQL 2000. Unfortunately, the directories are on teh root and include MSSQL7, which will be very confusing. I have looked at the steps to move DBs, including the master, ..., but there is more things associated with that directory structure. Is there any way to move everything to a new directory structure, short of moving all SQLQ 7 DBs to a different server, uninstalling SQL 7, installing SQL 2000 and using the tools provided to copy the DBs from SQL 7 to SQL 2000? I would like to use the default directory structure suggested in the SQL 2000 documentation (x:\Program Files\Microsoft SQL Server\MSSQL\). Any suggestions would be appreciated.
Thx
-
Did I put this in the wrong forum group? Any suggestions would be appreciated.
Thx,
-
My preference when upgrading from SQL 7 has actually been to:
1. Make a backup of all the databases.
2. Script out the logins.
3. Move all the user databases and the msdb database to the directories I want them in.
--I put all my databases in directories that following this format though.
--Data Files: drive:\mssql\data
--Log Files: drive:\mssql\log
4. Uninstall SQL 7.
5. Install SQL 2000 and apply latest service pack and patches.
6. Use this article to get my databases attached and moved properly.
--http://support.microsoft.com/default.aspx?scid=kb;en-us;224071
7. Apply the login script to the new installation.
Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA. -
Thanks for the suggested process. I was hoping that there would be a way to move everything after the upgrade, but have not had any luck finding a clean way to do it. Your process is where I was headed. The one thing I was concerned about, was the SQL logins. I believe they are stored in the master DB. When you restore a DB, I do not believe it restores the SQL logins.
The way I was thinking was that I would use my test server as a temporary storage for the SQL 7 data.
- Transfer data from production to the test server (backup/restore or detach/attach)
- make sure all the SQL based applications are working on test server
- uninstall SQL 7 from the production server
- install SQL 2000 on the production server
- Use the SQL 2000 Copy/Move Wizard to copy the DBs from the test server (SQL 7) to the production server (SQL 2000). I need to test this to see what impact it has on the source server.
- Verify that all SQL applications are working
- Open it up to all users.
Unfortunately, this looks like a very time consuming process, but it looks like this method (or your suggestion) is the only way to attack this problem. I definetely do not want to be stuck with the existing directory path.
Thanks again for your reply. It helped confirm that I am heading in the right direction.
-
Just a hint from experience....
Try not to use spaces in database names or paths. All they seem to do for me (and everyone else) is cause headaches. If you need a space use an underscore.
I have one custom sp that pulls stats on the DB sizes and then writes the info to a table. This joker M$ consultant, who believes Gates is god, comes in and throws a DB on my server, w/o telling me, with spaces in the name.
My stats sp fails the next time it runs. This is because I didn't use the brackets "[]" in my sp. I grant it was semi-poor programming, but it just hacks me that I have to modify all my stuff on some consultants whim! :p Same thing with pathnames. You have to use quote marks and other things to get around the spaces in pathnames. Just extra work.Just a thought.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply