I am trying to transfer a database from SQL Server 2012 to SQL 2016 by restoring from a 2012 backup. The restore runs without any displayed errors, but no tables are created. The same backup/restore works properly on 2012. Owner is SA with the same password on both computers.

Copy database doesn't work properly.

Export data correctly creates and populates the tables except that is doesn't transfer the ID column properties.

It is possible to set the ID column to primary key after the transfer. ID column properties lets you set Identity to yes, but the change is rejected when you try to save the table changes.

I am aware of the dropping and recreating of a table in order to get around the Identity problem.

Any suggestions about the best way to transfer a database from 2012 to 2016 will be greatly appreciated.

I found one way to transfer a database from SQL Server 2014 to SQL Server 2016.

First, the databases are on two separate computers that are on the same LAN.

Open the SQL Server 2016 SQL Server Management Studio. Left click on Databases to highlight the choice. Then right click and select New Database. Type the name of the database in "Database Name". The name should be the same as the one on the 2014 system. Left click on OK.

Open Server Management Studio on the 2014 computer. Connect to the SQL engine. Expand databases and left click on the name of the database to be transferred to highlight it. Then right click on the name. Select tasks from the ladder and then select Export Data from the next ladder that pops-up. The import/export wizard pops-up. Click on Next. Drop down the Combobox Data Source choices and select SQL Server Native Client 11.0. Enter the 2014 Server Name. Check Use Windows Authorization, enter the database name and left click on Next.

For destination select SQL Server Native Client 11.0. Enter the server and database names. Click on next.

Select copy data. Click next. You will now see a display of the names of the Source and Destination servers and the names of all of the tables. Select all the tables by clicking on the check box that is located to the left of the name "Source". The names of all of the tables will be seen on both the left and right sides of the display.

Click on Edit Mappings. Destination schema will be dbo. Click on the check boxes for Drop, Delete and Enable. Click OK then click Next twice. The transfers should then be displayed as they are in progress.

The database schema and data should now have been copied from SQL 2014 to 2016. The only catch is that the transfer doesn't include the settings that show that the ID column has been set to primary index and IDENTITY. The database editor lets you set the primary index, but not the IDENTITY. One way to do this is to delete the ID column and then use New Query to execute the following as was described by shree.pat18 in this forum:

ALTER TABLE <your table name>

ADD ID INT IDENTITY(1,1) NOT NULL

You will now have a new ID field that is located at the end of the column of field names. It is possible to drag this field back to the start of the names, but SQL will not permit saving this change in field location. The new field location will not affect fields that are accessed by name. Code will have to be re-written in those instances in which data are accessed by their location within a table's column.

Obviously the best choice is to be able to use Backup/Restore or Copy. I don't know why these didn't work properly. It may be that all that is needed is a simple change in procedure.

Hi RERThird,

Glad to hear that the issue is resolved. I marked your reply as answer, that way, other community members could benefit from your sharing.


Thanks,
Lydia Zhang