August 2, 2004 at 9:48 am
Haven't been able to find what I am looking for and am under a time constraint. I have a fully functioning SQL7 DB on a W2K server and need to copy the entire DB structure over to new hardware with W2K3 and SQL2000 for TESTING PURPOSES ONLY, at least initially. No down time (if it can be avoided) and users stil in the SQL7 DB (if possible but it appears this may be asking a bit much). Is the copy Database Wizard the way to go here? If so what is the process to keep the 7 database entirely intact?
Thanks in advance for any help.
August 2, 2004 at 10:15 am
why not just script out the objects by right clicking on the database and clicking on generate scripts.
you can then select all objects (including users) and your database is scripted up for you. then just run the script on the instlled SQL on the 2k3 machine and Hey Presto - a new database (no data of course)..
or alternatively - why not just use a backup from your SQL7 database and RESTORE to your SQL2000 server ? no need to take the system off line at all
MVDBA
August 2, 2004 at 10:20 am
The backup/restore alternative sounds like the answer. I do have good backups of all DB's (user, master, etc.). Will there be no interference from the SQL2000 install if the W2K3/SQL2K install is on a member server of the same AD as the SQL7 DB? It would seem obvious that all pointers in the code are toward the other instance on an entirely different machine but I just want to make absolutely sure.
Thanks
August 2, 2004 at 10:24 am
no problem at all.
you although only restore your user databases - the master database controls all sorts of info about your server and a SQL2000 and SQL7 master databases are slightly different.
if you use the generate script option to script up your server logins first then you should be ok with your restore. otherwise you'll get lots of orphaned log-ins that you'll need to rebuild.
MVDBA
August 2, 2004 at 10:46 am
I am showing my newbie-ness here but, again, just to be sure. I would only want to restore the user DB then? NOT the master, msdb and all others? It seems I had read in the many references that the others would be upgraded in the process. Maybe not. I really orphaning my logins will be okay at this point or do you feel I should run the script in anticipation of making the test production at some future point?
Thanks again
August 2, 2004 at 10:57 am
I thought I also saw something that said the script would not be necessary if you were copying DB's within the same AD (domain) as SID's would nto change. Please correct me if wrong because I have read so much it is all starting to run together a bit.
Thanks
August 3, 2004 at 2:09 am
just restore your user databases.
you can then script up your user log-ins and grant them permissions
all you have to do is clean up your orphans first.
MVDBA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply