I was recently asked about whether SQL Server data can move between architectures, say from x64 to x86.
Yes, you can move SQL Server data back and forth between x64, x86, and IA64 architectures. The data and log files themselves do not store anything that indicates the architecture and work the same on either 32-bit or 64-bit. The same applies to the backup files. Given those facts it becomes clear that we can easily move data between architectures. You can backup on x86 and restore to x64. Detach/attach works fine. Log shipping works because it is basically backup/restore with some scheduling. Mirroring and transactional replication take data from the transaction log and push the data to another system so again they work across architectures. Merge replication is basically just another application sitting on top of SQL Server, it moves data by reading tables in one location and modifying data in another location. Again, this can all be done across architectures.
Hopefully you are not installing new x86 boxes, 64-bit handles memory so much better. If you have legacy x86 boxes you can easily do a backup or detach from that old system and restore or attach on the new x64 instance. You can also reverse the process and copy data from x64 back to x86. The same logic applies to the other technologies listed above.
Per BOL (I used the SQL 2008 R2 version):
· The SQL Server on-disk storage format is the same in the 64-bit and 32-bit environments. Therefore, a database mirroring session can combine server instances that run in a 32-bit environment and server instances that run in a 64-bit environment.
· Because the SQL Server on-disk storage format is the same in the 64-bit and 32-bit environments, a replication topology can combine server instances that run in a 32-bit environment and server instances that run in a 64-bit environment.
· The SQL Server on-disk storage format is the same in the 64-bit and 32-bit environments. Therefore, a log shipping configuration can combine server instances that run in a 32-bit environment and server instances that run in a 64-bit environment.
If you’re doing SAN level replication you’ll need to talk to your SAN vendor about their support across platforms.
Some x64 info:
24 thoughts on “Moving data between 32-bit and 64-bit SQL Server instances”
Cindy, I do appreciate this Info, I was in a dilema, you lifted the cloud.
I try to restore a database backed up from a X64 server to X86 server, but it fails with the following error.
Here are the server configuration, I'm very sure there is no issue with the backup. I thought my back up would be corrupt, but it restores well on the same server where I had the backup. So the backup is clean (bkup.bak).
Destination server (restored to)
Microsoft SQL Server 2008 (SP1) – 10.0.2531.0 (Intel X86) Mar 29 2009 10:27:29 Copyright (c) 1988-2008 Microsoft Corporation Express Edition on Windows NT 6.1 <X86> (Build 7600: )
Source server (backedup from)
Microsoft SQL Server 2008 (RTM) – 10.0.1600.22 (X64) Jul 9 2008 14:17:44 Copyright (c) 1988-2008 Microsoft Corporation Express Edition with Advanced Services (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)
There are differences in the versions but I'm not sure if that's the cause for the error. Here is the error message below when I tried to restore.
The media family on device 'C:…/bkup.BAK' is incorrectly formed. SQL Server cannot process this media family.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3241)
Senthil – that is very unlikely to have anything to do with 32bit vs. 64bit. Perhaps the file is being corrupted during the copy to the other server. Try RESTORE FILELISTONLY and HEADERONLY to see if the files appear valid and if there are multiple backups in the files. If you are restoring a system database you need to make sure the versions are EXACTLY the same.
Great blog. Is there anything special I need to do to ensure log shipping starts back up cleanly after I upgrade my source SQL server from 2005 32-bit to 2005 64-bit, or is it as easy as stop log shipping, detach source database, rebuild source server (I am upgrading to 2003 64-bit as well), re-attach database and execute create log shipping script that was created before the source server was rebuilt?
Thank you, Cindy, what about the system databases (master, model, msdb…). I succeeded with migrating all these databases from a SQL Server 2005 32 bit to a SQL Server 2005 64 bit. I just had way to many jobs and logins to reconfigure if that was not the case. Will I run into any issues down the road ? Thank you!
This is the only potential issue I can think of with regards to msdb moving:
Error message when you restore or attach an msdb database or when you change the syssubsystems table in SQL Server 2005: "Subsystem % could not be loaded"
Need clarification on :
Is it possible to restore DB'S from SQL Server 2005 32-Bit Enterprise to SQL Server 2008 R2 64-Bit?
Is there is a way where we can perform upgrade SQL Server 2005 32-Bit Enterprise to SQL Server 2008 R2 64-Bit?
Thanks in advance.
No, see msdn.microsoft.com/…/ms143393.aspx
i have SQL 64 bit on windows 64bit server and i want to restore the database to SQL 32bit on windows 32bit server…
is this possible?
Hi Mohs – Yes, you can backup a database from a 64bit instance and restore it to a 32bit instance. The normal versioning rules apply – for system databases the versions must match exactly and for user databases the restore must be to an instance that is the same version or higher. BOL outlines the limits on when a much older version can be restored to a newer version.
How do I migrate SQL Server 2000 x86 to SQL Server 2008 64bit. Will there be any issue with the data and is there any compatibility issue for store procedure.
Any documentation that I can reference to on migration.
Thanks for the question Johnny!
You need to do your own testing to see how your code, including stored procedures, will run from both a functionality and performance standpoint on the newer version. Tools like Upgrade Advisor, SQLDiag + SQLNexus, and SQL trace / profiler can help you with this testing.
The version/edition upgrade matrix for SQL 2008 R2 can be found here (there's a link at the top for changing to other versions):
Basically if you're on a cluster you should reinstall but on a standalone system you have the choice to upgrade in place (within the edition restrictions listed).
I'm going to guess that something still on SQL 2000 is on an older OS and older hardware so you'll likely want to move to a new system anyway. You can do a backup/restore or detach/attach to move the databases to the new instance. Then do all the basic config stuff like setting sp_configure options and moving things from the system databases like logins/passwords, dts/ssis packages, etc. See blogs.msdn.com/…/checklist-for-upgrading-to-a-new-version-of-sql-server.aspx
Your article is good
but it will be very good if you enlist some of the steps to restore 64 bit DB back to 32 Bit DB
hope you will enlist it.
on my customers system SQL Server 2008 R2 64 Bit Database is there
now i want to work on it but my machine is having 32 BIT sql server 2008 could you please help me to achieve it ?
SK – moving between 32bit and 64bit is a simple detach/attach or backup/restore. However, you cannot take a backup from a newer version (SQL 2008 R2 in your case) and restore it to a lower version (SQL Server 2008). You can upgrade the instance with 2008 to 2008 R2 or you can manually move the data such as with BCP or SSIS.
Thank you very much Cindy – just the information I was looking for.
Great info. How about upgrading MSDE to SQL Server 2008 R2 Express Edition 64 bit by just restoring the backup of the 2000 database(s) excluding system dbs. I understand this does not trully upgrade because everything that is in master will not be upgraded and the users will have to be created manually both in master and in the restored DB but other than that do you see any issues with such approach. Thanks
Thank you Cindy. This was just the information I needed!
My question is if we move sql 2008 32 bit database on sql 2008 64 bit machine. Configuration of both machine is same windows server 2008 64 bit. Then will it impact on speed of application due to database movement from 32 bit to 64 bit. ? Will my application get slow or any performance issue will happen ?
Please help me in this.
Om – it is extremely rare for any two machines to be identical, there is usually some setting or configuration and/or hardware component that is different. If you see slower performance after moving to 64bit I would not put the 64bit change anywhere near the top of the list of things to investigate. If all things are equal and if the systems really are the same the most likely scenario is that either your performance will stay the same or improve – depending on how your code uses memory on the machine. Check out blogs.msdn.com/…/all-the-magic-knobs.aspx.
Superb article. Thanks for this Cindy
we have sql 2005 32bit standard on 32 bit os. we are trying to migrate all databases to sql 2012 64 bit standard on 64 bit os. do we have to face any issues further moving forward.
Thank you so much for the information! Your explanation have lifted clouds, but I have couple more questions for you. We would have to deal with MS SQL Server Standard 2012 32bit and 64bit, and following are it environment details:
Development Environment has Windows Server Enterprise 2008 32bit with MS SQL Server Standard 2012 32bits
Production Environment will have Windows Server Enterprise 2008 R2 (64bit) with MS SQL Server Standard 2012 64bit.
My questions are as follow:
1. I know, we cannot take backup from SQL Server 2012 64bit and restore in SQL Server 2012 32bit, but will it be same case with data import and export when done from SQL Server 2012 32bit to SQL Server 2012 64bit? If Yes, then:
a. Will import and export of views and other 'programmability,' like Store Procedures and Functions, is possible? If yes, then will it be smooth?
b. Will other functions, like 'scheduled jobs,' 'policy management,' and 'database mail,' will have drastic differences on performance or functionality?
Thank you in advance! 🙂
I would like to perform an upgrade from MS SQL 2008 r2 x32 to 2014 x64 but it cannot be in-place. My idea was to back-up the files from production, copy the back up files to my computer — x64, run the set up for MS SQL 2014 and perform the upgrade in-place. Then I would just copy the upgraded files to the new server using the restore or reattach methods.
Before running the upgrade run the Upgrade Advisor, the Best Practices Analyzer, and CHECKDB on all the databases that we are going to upgrade.
After the upgrade run the CHECKDB.