Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Can any one suggest variou ways of migrating a database from SQL server 2000 to 2005.

How do I make sure all constraints, primary keys and indexes are preserved. Is there any migration tool that preserves them.

share|improve this question

3 Answers 3

up vote 3 down vote accepted

There are several ways to do this, each of which carries all aspects of the database over to the new or upgraded server, the easiest of which are

  • Detach and attach
  • Backup and restore

I prefer the detach and attach method. Another thing to watch out for, specific to the databases themselves, is making sure that the logins get carried over to the new server. Use sp_help_revlogin to copy logins to the new server.

share|improve this answer
1  
"copy database wizard" will fail because of the version incompatability but "copy tables via SSIS/DTS will work". –  djangofan Aug 14 '09 at 15:21
    
Oh, right... Friday, need more coffee... Edited to remove that one. –  squillman Aug 14 '09 at 15:23
    
I think using DTS will not preserve the constraints and indexes. Please correct me if I am wrong. I want to use the easiest way. Does detach and attach preserve everything? –  rboorgapally Aug 14 '09 at 17:16
    
DTS/SSIS will copy everything if done through the copy database wizard (and instructed to do so), but as djangofan pointed out that only works between 2 servers of the same version. Detach and attach preserves everything since you are literally copying the physical storage of the source database. –  squillman Aug 14 '09 at 17:25
1  
@Albert Yes, then just attach using the new location of the files –  squillman May 4 '11 at 11:51

In SQL Server, constraints, keys, and indexes are all part of the database file, so you won't lose them by moving the database between versions. Just use detach/attach or backup/restore as squillman said; SQL Server will take care of upgrading the database to the new version.

share|improve this answer

I use robocopy from microsoft if u install gui it will very easy to copy all file config etc..

share|improve this answer
    
Robocopy is a great tool, but it doesn't add anything here. Everything that SQL Server needs for the database is contained within the .mdf and .ldf files, so you can use any copy program to move the files. –  Ed Leighton-Dick Aug 14 '09 at 18:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.