Migrate SQL Sever 2000 bak file to SQL server 2005
Posted on 2009-06-29
I have been busy getting my new servers up and running and ran into a small problem.
My current databases are in SQL Server 2000 and on servers in Virginia that I access remotely. They are fully functional and backed up every night.
My new database servers are in Texas and running SQL Server 2005 that I access remotely.
Here is the problem. The databases are about 1G apiece and I tried scripting the entire database in SQL Server 2000 and running in the 2005 but there were some errors in some of the scripts.
So next I ftped the bak file from the 2000 machine to the 2005 machine.
I tried to restore, but I get an error as soon as I try, The backup set holds a backup of a database other than the existing database name here database.
I also tried restore files or file groups and got the same error.
How can I take the bak file from 2000 and get it running under 2005? Is there a better way to go about this?
I already copied the logins from 2000 to 2005. I found a white paper from Microsoft that helped migrate all the user names.
If I create a script in 2000 for each type of database objects, that seems to work, but it iis very slow and doesn't get the data from 2000 to 2005. Thoughts?
I have been googling for solutions and they say to use the bak fille, but it doesn't seem to work for me. I am at a loss and am turning to EE.