SQL Server Performance Forum – Threads Archive
test upgradeI have been put incharge of migrating our current SQL 7 database to SQL 2000. I have the luxury of installing SQL 2000 on a new machine and testing for a few weeks. I would like to know how I can "migrate" the existing data to my test server without taking the existing database offline. The current SQL server is backing up the database to a .bak file and I also have all the transaction logs from the time that backup was created. Can that be if any use? Rich
I suggest you use the Copy Database wizard from your SQL2000 server to copy the database from your SQL 7.0 server.
but doesnt that take the databases offline while it is migrating?
DBA, Oxfam GB
When using backup/restore be careful of collations. The SQL7.0 databse will probably have the collation as SQL_Latin_General_CP1_CI_AS, wheras if you have used the default installation on your new box, the collation will the the windows collation NOT the SQL one.
This could hurt you if anybody uses a UNION opereator in conjunction with a select into, because the temporary table will have the default collation of the server, and the databse table will have the collation of the existing database, and thus the code will fail.
There a couple of workarounds if necessary, but it’s just a word of caution.
Review information from thishttp://www.sqlteam.com/item.asp?ItemID=9066 link about upgrade. Satya SKJ
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.
maybe I need to rephrase my question: I have a SQL 7 database currently backing up the databases into a directory on its local hard drive. I have a new box running SQL 2000 that I would like to put a copy of the database on just to test things. I would like to "copy" or "restore" my sql 7 databases onto my SQL 2000 server without changing or interrupting the current server. Will the Copy Databse Wizard effect (or is it affect) my original server? I would feel better if I could restore from those .bak files, but havnt gotten that to work. If I should be able to restore SQL 7 backups to SQL 2000 server I will trouble-shoot that.
what if you also install SQL7 on your test machine? Then you can backup restore to the test SQL7 instance and from there you can choose to use the copy database wizard or any other option that you want to explore to migrate the data Cheers