Upgrade SQL Server 2000 database to 2008 R2 and enable new features

I have recently upgraded SQL Server 2000 database to 2008 R2. ------------------- **What I did was:** 1. Shutdown SQL Server 2000 (express) service on old machine, 2. Move datafiles (*mydatabase.mdf* and *mydatabase.ldf*) to new machine, 3. Run SQL Server Management Studio 2008, 4. Connect to local database engine, 5. Attach datafiles to database. 6. Change the compatibility level of database to SQL 2008 (100). ------------------- **Question: *What else should I do to make migration complete?*** I want to: 1. use new features like checksumming and full recovery model, 2. make this database to be exactly as it was created in SQL 2008 R2, 3. make this database to be fully compatibile, correct and be perfect suited for new, SQL 2008 R2 database engine. ------------------- *In other words:* I just want to know how to correctly and completly convert old SQL 2000 database to new 2008 R2 database, be calm that everything is done right and be happy with all new features. ------------------- I'm asking this question, because I've found a lot of sites on the Internet that says so many different things that makes me confused: some say that it is required to rebuild indexes, another says to do other things... and now I know nothing so I want to hear experienced person opinion and clear, step-by-step instructions. I work for very small company, I'm on my own and I don't want to screw things up. --- **Sir, I'm really impressed with your answer, I wasn't expecting so much.** --- **So some comments:** 1. The database is now in production. As I said, it was upgraded using deattach-attach method as I desribed in first post and as described on MSDN: http://msdn.microsoft.com/en-us/library/ms189625.aspx It had to be done quickly, so I was forced to do it that way. Let's forget about how inappropriate could it was and focus on current situation. 2. The users/persmission is not a problem here - there are only few and permissions are simple. 3. The application which use database is compatibile with SQL 2000 till 2012 so this is not a problem either. 4. The database file (MDF) isn't big - only about 1GB. --- **Few more questions:** 1. You recommend to use backup/restore method, but I did as written above, so can I encounter any problems now? Everything worked without any problem. 2. About checksumming and full recovery model: it wasn't available/enabled on SQL 2000, so I want to use them now. You said that the only thing I need to do is to enable those options in database properties? I've read somewhere, that it isn't enough and I should also rebuild indexes or something. I really don't know, I just asking. 3. I'm prepairing to migrate this database to SQL 2012 - so first it was from SQL 2000 to 2008 R2, now it will be from 2008 R2 to 2012 (it was impossible to do this directly because of lack of support of SQL 2000 databases in SQL 2012). So I understand that I should follow your guide: backup it in 2008 R2 and restore in 2012, then do the rest of your tips, right? 4. Please explain me the backup/restore method: Is it like a dump of database to SQL queries and then restoring it by executing a bunch of queries? Will this method by the way "defragment" my database? If not, how to defragment/optimize it manually? 5. As we were using SQL 2000 Express for years (no management interface), we were doing backups simply by stopping engine and RAR the DATA directory. For now, as we are on SQL 2008, isn't this still better than using backup function in Management Studio? 6. *Full recovery mode with frequent Transaction log backups* - Where is the Transaction log stored - is it the LDF file? How sould I backup it properly? --- I know that my questions may sound silly, I'm not professional database admin, but I am the only person here who can do such "hard core" task like upgrading the database engine. I'm also sure that your knowleadge will help a lot to other people like me. Thank you very much for your time and knowleadge, I really appreciate this.