Partager via

Moving data between 32-bit and 64-bit SQL Server instances

  • Article

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:

https://blogs.msdn.com/cindygross/archive/tags/x64/default.aspx

Comments

  • Anonymous
    December 13, 2010
    Cindy, I do appreciate this Info, I was in a dilema, you lifted the cloud.Many thanks

  • Anonymous
    February 15, 2011
    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)ThanksSenthil

  • Anonymous
    February 16, 2011
    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.

  • Anonymous
    March 11, 2011
    The comment has been removed

  • Anonymous
    March 24, 2011
    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!

  • Anonymous
    March 24, 2011
    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"support.microsoft.com/.../914171

  • Anonymous
    September 11, 2011
    Hi Cindy,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.

  • Anonymous
    September 13, 2011
    Yes No, see msdn.microsoft.com/.../ms143393.aspx

  • Anonymous
    October 08, 2011
    Hi cindy,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?Thank you

  • Anonymous
    October 09, 2011
    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.

  • Anonymous
    January 30, 2012
    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.

  • Anonymous
    January 31, 2012
    The comment has been removed

  • Anonymous
    May 27, 2012
    Your article is goodbut it will be very good if you enlist some of the steps to restore 64 bit DB back to 32 Bit DBhope you will enlist it.

  • Anonymous
    May 27, 2012
    on my customers system SQL Server 2008 R2 64 Bit Database is therenow i want to work on it but my machine is having 32 BIT sql server 2008 could you please help me to achieve it ?

  • Anonymous
    June 07, 2012
    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.

  • Anonymous
    August 11, 2012
    Thank you very much Cindy - just the information I was looking for.

  • Anonymous
    October 17, 2012
    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

  • Anonymous
    December 04, 2012
    Thank you Cindy. This was just the information I needed!

  • Anonymous
    December 18, 2014
    Hi Cindy,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.Thanks

  • Anonymous
    December 19, 2014
    The comment has been removed

  • Anonymous
    December 23, 2014
    Superb article. Thanks for this Cindy

  • Anonymous
    February 19, 2015
    Hi 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.

  • Anonymous
    July 02, 2015
    Hello, Cindy! 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! :) -KD
  • Anonymous
    November 17, 2015
    Hi Cindy, 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. Regards, Rick