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
- Perform a full backup of the database
- 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.
- Copy the full database and log backup files to the server that has SQL Server 2005
On SQL Server 2005
- Restore the full backup with NORECOVERY
Make sure you change the directories as mentionned in your post.
- 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
USE [mydb]sp_updatestats -- updates statistics
This is because statistics are not automatically updated during the upgrade process.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
EXEC sp_change_users_login 'Report' -- find orphaned users- 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.
Yes, SQL Server 2000 x86 can use AWE to buffer data pages above the 4GB limit. See How to configure SQL Server to use more than 2 GB of physical memory:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', ...
RECONFIGURE
GO
The article also explains how to enable /PAE in boot.ini, but if your OS is amd64 already you don't need it since the OS can already see the entire memory. So all you need is to enable AWE on the x86 SQL Server 2000 instance.
Best Answer
How to get the 8.00.194 (!!!!!!) server to 8.00.2040:
You can download Service Pack 4 (8.00.2039) from here, which you will need to install before you can install the 8.00.2040 hotfix.
How to restore a 2000 .bak to a 2008 server:
You may need to clarify your actual requirements and clean up the question, but if you are trying to ultimately restore a SQL Server 2000 .bak file from server 2 to a SQL Server 2008 instance somewhere else, there is no reason to restore to server 1 first - just restore the .bak directly on the SQL Server 2008 instance (regardless of the source, 8.00.194 or 8.00.2040).
How to resolve the invalid tape format message:
You may have an invalid
RESTOREstatement, or you may have a corrupt backup. If the former, add theRESTOREstatement to the question. If the latter, take a new backup (there are some tedious workarounds if you are restoring to 2005 or higher, but they include potentially allowing data loss). I don't believe this error message will be resolved simply by upgrading server 1. If@@VERSIONwere the issue (and it can be in some cases), you'd get a different error message than one about tape format.