February 14, 2002 at 9:01 am
Do I have to run the Database Copy Wizard, or can I use a backup copy to restore a database to SQL 2000?
February 14, 2002 at 9:21 am
Easiest way is to detach/copy/attach - basically what the copy wizard does. Attaching a 7.0 mdf to SQL2K automatically upgrades the data structures.
Andy
February 14, 2002 at 9:27 am
Thanks for the quick response. I guess I realize which is easiest. I only want to make sure I do not cause a meta data catalog problem with the restore of a .BAK file from SQL 7 to SQL 2000.
February 14, 2002 at 10:13 am
I did a test restore from a SQL 7 Server to 2000 and had almost no trouble (only server differences in file structure I had to account for). You most likely will need to drop any roles and users from the database after a restore then readd but I did not have a small enough database to test this. Restore also changes compatibility to 8.0 when done (I think that is a default and of course you can set it back to 7.0 if you have need to). I did not see any metadata problems.
February 14, 2002 at 10:59 am
Why would you need to drop roles and users?
Andy
February 14, 2002 at 11:34 am
As I said I did not test that need. But when you add a database to another server where it did not exist before if you have any user logins they will not always show up properly and when you go to add them or grant access to the copy of the ones on the new server it will bark about already existing. If this happens then you will need to drop them and recreate them. I would assume if you are restoring a single database and not the master it will work the same as sp_attach_db in this regards. Hope I explained this right.
February 14, 2002 at 12:18 pm
on sql server 2000, the sp_change_users_login will help fix user and logins.
Steve Jones
February 14, 2002 at 1:04 pm
I was under the opinion, that SQL Server 2000 database catalog table structures may have changed from SQL Server 7. When I restored a SQL Server 7 .BAK file to SQL Server 2000, I did experience problems with userids and logins. This was easily taken care of by dropping and recreating them. However, I have not experienced any problems with the autual tables. Is this database structurally compatible with SQL Server 2000?
February 14, 2002 at 1:50 pm
Sp_change_users_login existed in SQL 7 as well. Its just a matter of getting the ID's to match between logins and users. Couple articles posted on the site about it (one of them by me).
Andy
February 14, 2002 at 5:14 pm
Thanks I never saw that SP, gotta try it next time, will save me oh so much hassel.
February 15, 2002 at 5:48 am
I do not think anyone has really answered my question. Please, could someone tell me if the database is structurally sound under the above mentioned conditions.
February 15, 2002 at 5:52 am
Yes.
February 15, 2002 at 2:32 pm
Though it's never been an issue for me, I tried this on a test server that I just butcher with restoring, dropping, and attaching various databases. I have one database where dbo is out of sync. What sort of problem could this present in a production database? --And, how could this be avoided/corrected? Thanks for appeasing my curiosity.
-Ken
February 15, 2002 at 3:03 pm
The problem is while a user will be able to connect, essentially they will have no permissions - this because the id in sysxlogins in master doesn't match the uid in sysusers. All the proc does is join on name instead of ID, then update the ID's to match. It's a potential security disaster if it matches wrongly, but no much chance of it happening. Hard to avoid it since the id created in syslogin on one server will be different if you attach a db from a different server (or add a new login). You can work to keep it from happening but honestly the easiest way is to just do the attach, then run the proc to fix up the ID's.
Andy
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply