Moving database from Azure SQL to localdb

I recently moved an Azure SQL database back to a local development database and ran into a few issues. I took these notes so that they might help the next person that hits the problem.

In my local SSMS, I use the Import/Export wizard with the datasource using SQL Server native client 11.0. This moves the tables and data. Any destination tables will not have IDENTITY as the source tables did. 

Solution # 1 

Move away from INT Identity to use GUIDS. This requires work in the database and client code but is the better choice if you need to move data out of the source datatabase then back into the source database. 

Solution #2

More immediate fix to get past the block that your inserts don't autoincrement. 


The following steps are completed in SSMS in the destination (local) database and should have all the data but not the IDENTITY column.

  • Rename the mytable to mytable2.
  • Generate CREATE, INSERT, and SELECT scripts for the table.
  • Modify the CREATE script to use the table name (change mytable2 to mytable) and change the PK row to include the identity requirement.

CREATE TABLE [dbo].[mytable](
[Id] [int] IDENTITY (1,1) NOT NULL,
[Text] [nvarchar](40) NULL,

  • Run the creation script.
  • Create second script from the INSERT/SELECT scripts

INSERT INTO [dbo].[mytable]
  FROM [dbo].[mytable2]     

Popular posts from this blog

Yet once more into the breech (of altered programming logic)

Simple WP7 Mango App for Background Tasks, Toast, and Tiles: Code Explanation

How to convert SVG data to a Png Image file Using InkScape