27

What are my options if I need to move a database from SQL Server 2012(32bit) to SQL Server 2005(64bit)?

I know I cannot:

  • restore a backup of the database on SQL Server 2005
  • detach & attach

I know I can:

  • use import data wizard, and I tried it on one database, but it only moves data, and even that was troublesome as I needed to do a lot of work creating temp tables to maintain identity columns, recreating all FKs, indexes etc.

Is there an easier option?

  • 12
    The easier option would be to upgrade the destination to 2012, to be honest. – Aaron Bertrand Dec 15 '13 at 15:59
38

You can follow any method below :

Note: If you are using any new features like new data types, etc then you have to test out as it will throw errors.

METHOD 1: Using Native Tools

  1. Script out the database SCHEMA_ONLY and recreate an empty database on the destination server. Below are the screenshots :

    enter image description here

    enter image description here

    enter image description here

  2. Use BCP OUT and BULK INSERT to insert data.

Below is the script that will help you with Part 2.

/************************************************************************************************************************************************
Author      :   KIN SHAH    *********************************************************************************************************************
Purpose     :   Move data from one server to another*********************************************************************************************
DATE        :   05-28-2013  *********************************************************************************************************************
Version     :   1.0.0   *************************************************************************************************************************
RDBMS       :   MS SQL Server 2008R2 and 2012   *************************************************************************************************
*************************************************************************************************************************************************/

-- save below output in a bat file by executing below in SSMS in TEXT mode
-- clean up: create a bat file with this command --> del D:\BCP_OUT\*.dat 

select '"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe" '-- path to BCP.exe
        +  QUOTENAME(DB_NAME())+ '.'                                    -- Current Database
        +  QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+'.'            
        +  QUOTENAME(name)  
        +  ' out D:\BCP_OUT\'                                           -- Path where BCP out files will be stored
        +  REPLACE(SCHEMA_NAME(schema_id),' ','') + '_' 
        +  REPLACE(name,' ','') 
        + '.dat -T -E -SSERVERNAME\INSTANCE -n'                         -- ServerName, -E will take care of Identity, -n is for Native Format
from sys.tables
where is_ms_shipped = 0 and name <> 'sysdiagrams'                       -- sysdiagrams is classified my MS as UserTable and we dont want it
and schema_name(schema_id) <> 'some_schema_exclude'                     -- Optional to exclude any schema 
order by schema_name(schema_id)                         



--- Execute this on the destination server.database from SSMS.
--- Make sure the change the @Destdbname and the bcp out path as per your environment.

declare @Destdbname sysname
set @Destdbname = 'destination_database_Name'               -- Destination Database Name where you want to Bulk Insert in
select 'BULK INSERT '                                       -- Remember Tables **must** be present on destination Database
        +  QUOTENAME(@Destdbname)+ '.'
        +  QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+'.' 
        +  QUOTENAME(name) 
        + ' from ''D:\BCP_OUT\'                             -- Change here for bcp out path
        +  REPLACE(SCHEMA_NAME(schema_id),' ','') + '_'
        +  REPLACE(name,' ','') 
        +'.dat'' 
        with (
        KEEPIDENTITY,
        DATAFILETYPE = ''native'',  
        TABLOCK
        )'  + char(10) 
        + 'print ''Bulk insert for '+REPLACE(SCHEMA_NAME(schema_id),' ','') + '_'+  REPLACE(name,' ','')+' is done... '''+ char(10)+'go' 
from sys.tables
where is_ms_shipped = 0 and name <> 'sysdiagrams'           -- sysdiagrams is classified my MS as UserTable and we dont want it
and schema_name(schema_id) <> 'some_schema_exclude'         -- Optional to exclude any schema 
order by schema_name(schema_id)

Method 2 : Using Third Party Tools

Create a blank database on destination server. Use Redgate's schema compare and data compare to create and load data into the destination server.

Note: I have used Redgate's schema and data compare and they are the best tools for such type of task and hence if you are using 3rd party tools, then my recommendation would be Redgate.

  • 7
    +1 but keep in mind that there are certain things that just won't work at the destination (and I'm not sure how graceful 3rd party tools will handle all of that). Objects like SEQUENCEs, code like OFFSET / FETCH, LAG / LEAD, etc. – Aaron Bertrand Dec 15 '13 at 20:17
  • Once you have the schema created by scripting and running those scripts against the older database, another option for transferring the data is to copy directly from one instance to another by setting up a linked server. It shouldn't be difficult to script up a loop that does all the work. To deal with FK constraints you'll need to make sure you copy the tables in an appropriate order or you could remove the FKs temporarily and reenable them after the copy. Similarly you'll want to disable any triggers in the destination during the copy. – David Spillett Dec 8 '15 at 13:06
1

In addition to the methods already suggested here, you could also try creating a BACPAC file and import it into your destination. This is similar to how Microsoft recommends migrating your databases from on-prem to Azure cloud databases.

The benefit of it is that it's a combination of exporting schema as well as data, and it's not dependent on the database version, so you could theoretically import databases from any version to any version.

The downside of it is that before it generates the BACPAC file at the source, it runs some sort of strict validation process which can easily fail if you have references to objects outside your database (be it user or system databases), or if you have encrypted objects. But if you're lucky and it doesn't fail, then it can be a rather easy solution.

All you need is one of the newer SSMS versions (17 or 18): https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

To start creating a BACPAC file, right-click on a database and select "Export Data-tier Application..." (be sure not to be confused with "Extract Data-tier Application..." which is something different):

Export Data-tier Application...

You'll be presented with a simple wizard to guide you through the steps. When you're done, you can use "Import Data-tier Application..." in your destination server, which you can see by right-clicking on the "Databases" node (again, don't be confused with "Deploy Data-tier Application..."):

Import Data-tier Application...

This, too, will show you a simple wizard to guide you through the steps.

-1

To decrease the version of Sql Server is very hard work.
There are some options to decrease like;
First of all, create all database object script and run at destination server.
After that you can use;

  • SSIS,
  • Import Data Tool,
  • Third party tool like RedGate or other.

But for RedGate Data Compare tool,

you must consider that it compares tables only which has primary keys . So if your tables that has not primary key, you must use other ways

  • 7
    I don't think this answer improves on Kins answer – James Anderson Dec 8 '15 at 11:27
-3

It's impossible to downgrade a database on SQL Server. However, a workaround is to script all DB objects and apply the script on the destination database. SSIS is a great way to help perform this.

  • 3
    Do you think this adds anything important to Kin's answer? – dezso May 10 '17 at 13:56

protected by Kin Shah Aug 10 '15 at 15:49

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.