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.
The fastest, simplest solution is to restore your last good backup. If that isn't an option you can try a workaround to make MSSQL think the database already exists:
http://sqlskills.com/BLOGS/PAUL/post/Disaster-recovery-101-hack-attach-a-damaged-database.aspx
Then you can run DBCC and see if it can repair the database, but even in that case you may still have data loss. You could also call Microsoft (maybe you already have a support contract), but the bottom line is that if you have a damaged database and no backup then there's not much that anyone can do.
Best Answer
Here is a process that you need to go through for upgrading from SQL Server 2000 to SQL Server 2014.
http://sqlmag.com/sql-server-2014/sql-select-steps-migrate-sql-server-2000-sql-server-2014
You will see that you need to
If you only have .mdf files (not a full backup of the database) then you will need to create .ldf (log) files for the instance. This is not a recommended method for restores, but may be what you need. Perhaps use marc_s script:
Or, there are several posts out there about attaching a database without a transaction log file. One random choice:
http://www.mytechmantra.com/LearnSQLServer/How-to-attach-database-without-a-transaction-log-file-in-SQL-Server.html
If your SQL Server 2000 .mdf was from SQL Server 2000 SP4 then you may be able to attach it to a SQL Server 2008 server. If the file has been corrupted, of course, then there is little to be done.
From this location you can apparently download the MSDE engine and SP 4. The MSDE engine will only handle a 2GB database.
https://msdn.microsoft.com/en-us/sqlserver/bb895925
MSDE has many limitations, including only 2GB databases. (And it has been so long since I used it that I do not remember all the problems and limitations.) See:
https://technet.microsoft.com/en-us/library/ms165672(v=sql.90).aspx
If you need a full version of SQL Server 2000, then I would suggest that you talk to other SQL Server users in the area and find one who still has retained an original install disk.
There are websites that say they have the media, but I cannot recommend them.