SQL Server Performance Forum – Threads Archive

best way to copy database

I need to move a database from sql 2000 to sql 2005. My research suggests that I can backup a sql 2000 db from enterprise manager and restore it with sql 2005 management studio. Are there step by step directions for this or can someone tell me how to do this?
what about logins? Are they transfered in this process. My research also suggests the I use copy database wizard by this failed on me and I can’t find anything in the event log or anywhere else to tell me why. Please help. Thanks

First: do you confirm about compatibility whit 2005? If yes, backup and restore is the best way. Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Ok. Backup and restore. Do I follow exactly as decribed in Books Online? It suggest that I just backup in enterprise on server 2000 and restore it on server 2005. What about the logins and permissions? Do I have to recreate them?

Logins will not be move, you need to move them manually…
And make sure run update statistics or DBCC DBREINDEX against the migrated database also along with chaging cmpt level as Luis mentioned. http://support.microsoft.com/kb/246133
How to transfer logins and passwords between instances of SQL Server MohammedU.
Moderator
SQL-Server-Performance.com

]]>