Sql-server – Upgrade process from SQL Server 2000 to SQL Server 2012

Since you are using the Backup/Restore method, you don't need to copy the database (mdf) and log file (ldf) to the new server, just the backup files. You do not need to recreate the database either. The database, database files and log file will all be created during the restore process. The upgrade process is done by SQL Server when restoring your database backup.

Here is the method I use:

On SQL Server 2000

1. Perform a full backup of the database
2. Perform a log backup of the database "captures records on the transaction log that were written since the last transaction log backup". Consider using the WITH NORECOVERY option, if you don't want people accessing the database after you're done.
3. Copy the full database and log backup files to the server that has SQL Server 2005

On SQL Server 2005

1. Restore the full backup with NORECOVERY Make sure you change the directories as mentionned in your post.
2. Restore the log backup with RECOVERY

Once the database is restored on the new server. I run the following commands in a new query window

1. USE [mydb]
2. sp_updatestats -- updates statistics This is because statistics are not automatically updated during the upgrade process.
3. DBCC CHECKDB WITH DATA_PURITY

Causes DBCC CHECKDB to check the database for column values that are not valid or out-of-range. This is important when upgrading from SQL Server 2000 to 2005 or 2008.

Associating database users with Logins

Now that your database has been restored, you need to associate its orphaned users with an actual login.

Method A: Fixing orphaned users

1. EXEC sp_change_users_login 'Report' -- find orphaned users
2. if the login exists already, you can asssociate the orphaned user with the login. Otherwise, you can create the login

-- Login exists

EXEC sp_change_users_login 'Auto_Fix', 'user' 

-- Login doesn't exist

EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

Method B: sp_help_revlogin

You also have an alternative called sp_help_revlogin which can help you to restore not only the login but the original password that went with it.

http://support.microsoft.com/kb/246133/en-us

It essentially entails creating a stored procedure on your old server to generate a script that will output all of your existing logins. You then copy the output of that stored procedure to your new server and restore the logins that are associated with the database you restored.

Compatibility level simply changes the language syntax that is supported by the SQL Statements. This will cause all the execution plans to be expired and force them to be recompiled. It has nothing to do with the data storage engine or the way that the data is written to the disk.

Once a database is attached to a SQL 2012 instance, it is in the SQL 2012 storage format and can't be moved back to an older version of SQL (no matter what setting the compatibility level is set to).

The link provided by @Shark (http://msdn.microsoft.com/en-us/library/bb510680%28v=sql.110%29.aspx) in the comments provides some additional reading on the subject.