I am trying to get a local copy of our remote database. I cannot do a "copy database" because it will not copy to SQLExpress. So I tried to script with data to recreate on my local, it got all the way to the "end" 3 times and then just said "generate script failed". So I scripted just the schema. All tables are empty, but now I am getting "duplicate key" errors (of course at the very end), like this:
Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "Violation of PRIMARY KEY constraint 'PK__tbl_AICC__3214EC07D26FCB1E'. Cannot insert duplicate key in object 'dbo.tbl_AICC_Results'. The duplicate key value is (1).". (SQL Server Import and Export Wizard)
I tried checking off "delete rows in table" I tried checking "delete and recreate tables", then I get a cannot truncate because the table is a foreign key, but I disabled all of the constraints using this:
EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
for those suggesting backup...It is not listed as an option:
That database is hosted on Azure, so I am not sure if that is why I cannot do a backup and restore.
because I was getting "cannot insert into read only Id", I am not getting that anymore so I could only assume that the removal worked. Any assistance is greatly appreciated.
If I am reading this correctly - you want to move/copy from your remote Azure SQL Database to a local instance. If so, then you need to create a BACPAC in Azure, copy that locally and use that for your local system.
User contributions licensed under CC BY-SA 3.0