Copying SQL Server 2008 Express database from 32 bit Express to 64 Bit R2 Express
I have two computers with SQL Server Express running on them
System A is a 32 bit system running Sql Server 2008. This SQL Server instance has a database that I wish to copy to system B.
System B is a 64 bit system running Sql Server 2008 R2.
Both systems have management studio running on them. I can't copy with the Copy Database Wizard, because the Express version does not support it. What I tried is I did a backup on system A within Management Studio producing a file. I took the file to system B and tried to do a restore, but I got an access denied error message. I don't think a detach/ copy file/ reattach would work because it's going from a 32 bit Express instance to a 64 bit Express R2 instance on another system. Any ideas on how to do this?
System A is a 32 bit system running Sql Server 2008. This SQL Server instance has a database that I wish to copy to system B.
System B is a 64 bit system running Sql Server 2008 R2.
Both systems have management studio running on them. I can't copy with the Copy Database Wizard, because the Express version does not support it. What I tried is I did a backup on system A within Management Studio producing a file. I took the file to system B and tried to do a restore, but I got an access denied error message. I don't think a detach/ copy file/ reattach would work because it's going from a 32 bit Express instance to a 64 bit Express R2 instance on another system. Any ideas on how to do this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Probably would be helpful to attach the db too.
http://msdn.microsoft.com/en-us/library/ms190209.aspx
http://msdn.microsoft.com/en-us/library/ms190209.aspx
ASKER
"32/64 bit-ness of the SQL Server edition does not even come into play - there is no issue with compatibility."
Could it also be said that there is no issue with compatibility between the 2008 and the 2008 R2? And this is the case whether I did the transfer using the backup/restore method or detach/reattach? I would think that detach/reattach would only work to transfer between instances of SQL server that are the same version. Could there not be something in a 2008 R2 .mdb file that a 2008 would not understand?
With regard to what exactly I did. I set up my backup on the SQL Server 2008 instance to write to a USB thumb drive. Then I carried the USB thumb drive over to my other computer and tried to do a restore from the file on the USB thumb drive. It is then that I got my "access denied". Do I need to copy my file to a specific place on the destination machine?
Could it also be said that there is no issue with compatibility between the 2008 and the 2008 R2? And this is the case whether I did the transfer using the backup/restore method or detach/reattach? I would think that detach/reattach would only work to transfer between instances of SQL server that are the same version. Could there not be something in a 2008 R2 .mdb file that a 2008 would not understand?
With regard to what exactly I did. I set up my backup on the SQL Server 2008 instance to write to a USB thumb drive. Then I carried the USB thumb drive over to my other computer and tried to do a restore from the file on the USB thumb drive. It is then that I got my "access denied". Do I need to copy my file to a specific place on the destination machine?
I have successfully detached/attached between version of MSSQL (2000/2005) without any issues. The collation between the two would have to be the same.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
"(1) the source (backup) file was not in the right folder or the SQL Server service account does not have access to the location"
Where do I configure where the SQL Server instance has access to what filespace? Can that be done within SQL Server Management Studio? If so, where do I go within SQL Server Management Studio?
Where do I configure where the SQL Server instance has access to what filespace? Can that be done within SQL Server Management Studio? If so, where do I go within SQL Server Management Studio?
The SQL Server service will not normally see mounted USB drives of the small flash variety (can't confirm, never tried from SQL).
You are better off copying to a local disk. The location suggested by acperkins is perfect, since it is where SQL Server is writing backups to, it must be able to access it!
Here are some links to find the backup folder if you have any trouble finding it
http://www.mytechmantra.com/LearnSQLServer/Changing_Default_Backup_Location.html#axzz1mJCjYfsK
http://www.mssqltips.com/sqlservertip/1583/changing-the-default-sql-server-backup-folder/
You are better off copying to a local disk. The location suggested by acperkins is perfect, since it is where SQL Server is writing backups to, it must be able to access it!
Here are some links to find the backup folder if you have any trouble finding it
http://www.mytechmantra.com/LearnSQLServer/Changing_Default_Backup_Location.html#axzz1mJCjYfsK
http://www.mssqltips.com/sqlservertip/1583/changing-the-default-sql-server-backup-folder/
ASKER
I might add that there was also a problem with the destination of my restore pointing to the wrong location. How it got that way is another issue I will need to work out. But for now, I have copied my database and am up and running.<br /><br />Thanks to all.
I would detach/reattach databases between instances. Works great for me.
http://msdn.microsoft.com/en-us/library/ms191491.aspx