Execute table change script deletes Azure SQL table

Question

I am new to the Azure platform and am testing using Azure SQL. I am using a local SQL Server instance for development, saving table changes as a script, then executing a query using that script on the Azure SQL Mgmt window.

Specifically, in one test I added a couple of columns to a PODetail table. From SQL Server I saved a script of the table changes. I then executed that script in the Azure SQL management window. It produced some errors and also just deleted the PODetail table itself as a result. I also don't see the TMP_PODetail table that the script creates...so the net affect is the total loss of a table by running a simple script to add a couple of columns.

This seems very dangerous! Am I missing something? In attempting to recreate the table, I stumbled through the fact that you can't specify the table options in the create statement, so I am guessing that was one of the root causes, but shouldn't it handle this scenario more gracefully then leaving the table dropped? Is there a way to specify the table script creation in sql server to produce Azure SQL compliant statements? Is there another approach to accomplish this that would work better?

Here is the script I ran:

/*
   Monday, June 16, 20149:50:57 AM
   User: 
   Server: 8675309_LT2
   Database: OTIS
   Application: 
*/

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.PODetail
 DROP CONSTRAINT FK_PODetail_Customers
GO
ALTER TABLE dbo.Customers SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
select Has_Perms_By_Name(N'dbo.Customers', 'Object', 'ALTER') as ALT_Per, Has_Perms_By_Name(N'dbo.Customers', 'Object', 'VIEW DEFINITION') as View_def_Per, Has_Perms_By_Name(N'dbo.Customers', 'Object', 'CONTROL') as Contr_Per BEGIN TRANSACTION
GO
ALTER TABLE dbo.PODetail
 DROP CONSTRAINT FK_PODetail_Items
GO
ALTER TABLE dbo.Items SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
select Has_Perms_By_Name(N'dbo.Items', 'Object', 'ALTER') as ALT_Per, Has_Perms_By_Name(N'dbo.Items', 'Object', 'VIEW DEFINITION') as View_def_Per, Has_Perms_By_Name(N'dbo.Items', 'Object', 'CONTROL') as Contr_Per BEGIN TRANSACTION
GO
ALTER TABLE dbo.PODetail
 DROP CONSTRAINT FK_PODetail_UserProfile_CreatedBy
GO
ALTER TABLE dbo.PODetail
 DROP CONSTRAINT FK_PODetail_UserProfile_ModifiedBy
GO
ALTER TABLE dbo.UserProfile SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
select Has_Perms_By_Name(N'dbo.UserProfile', 'Object', 'ALTER') as ALT_Per, Has_Perms_By_Name(N'dbo.UserProfile', 'Object', 'VIEW DEFINITION') as View_def_Per, Has_Perms_By_Name(N'dbo.UserProfile', 'Object', 'CONTROL') as Contr_Per BEGIN TRANSACTION
GO
ALTER TABLE dbo.PODetail
 DROP CONSTRAINT FK_PODetail_POHeader
GO
ALTER TABLE dbo.POHeader SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
select Has_Perms_By_Name(N'dbo.POHeader', 'Object', 'ALTER') as ALT_Per, Has_Perms_By_Name(N'dbo.POHeader', 'Object', 'VIEW DEFINITION') as View_def_Per, Has_Perms_By_Name(N'dbo.POHeader', 'Object', 'CONTROL') as Contr_Per BEGIN TRANSACTION
GO
ALTER TABLE dbo.PODetail
 DROP CONSTRAINT FK_PODetail_OrderHeader
GO
ALTER TABLE dbo.OrderHeader SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
select Has_Perms_By_Name(N'dbo.OrderHeader', 'Object', 'ALTER') as ALT_Per, Has_Perms_By_Name(N'dbo.OrderHeader', 'Object', 'VIEW DEFINITION') as View_def_Per, Has_Perms_By_Name(N'dbo.OrderHeader', 'Object', 'CONTROL') as Contr_Per BEGIN TRANSACTION
GO
ALTER TABLE dbo.PODetail
 DROP CONSTRAINT DF_PODetail_Billable
GO
ALTER TABLE dbo.PODetail
 DROP CONSTRAINT DF_PODetail_QtyOrdered
GO
ALTER TABLE dbo.PODetail
 DROP CONSTRAINT DF_PODetail_QtyReceived
GO
ALTER TABLE dbo.PODetail
 DROP CONSTRAINT DF_PODetail_UnitCost
GO
ALTER TABLE dbo.PODetail
 DROP CONSTRAINT DF_PODetail_ReceivedVendorInvoice
GO
ALTER TABLE dbo.PODetail
 DROP CONSTRAINT DF_PODetail_CreatedOn
GO
ALTER TABLE dbo.PODetail
 DROP CONSTRAINT DF_PODetail_ModifiedOn
GO
CREATE TABLE dbo.Tmp_PODetail
 (
 Id int NOT NULL IDENTITY (1, 1),
 POHeaderId int NOT NULL,
 ItemId int NOT NULL,
 NonInventoryItemDesc nvarchar(50) NULL,
 CustomerId int NULL,
 OrderId int NULL,
 OrderDetailId int NULL,
 Billable bit NOT NULL,
 QtyOrdered decimal(18, 4) NOT NULL,
 QtyReceived decimal(18, 4) NOT NULL,
 UnitCost money NOT NULL,
 ReceivedVendorInvoice bit NOT NULL,
 Notes nvarchar(MAX) NULL,
 CreatedById int NOT NULL,
 CreatedOn datetime NOT NULL,
 ModifiedById int NOT NULL,
 ModifiedOn datetime NOT NULL
 )  ON [PRIMARY]
  TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_PODetail SET (LOCK_ESCALATION = TABLE)
GO
ALTER TABLE dbo.Tmp_PODetail ADD CONSTRAINT
 DF_PODetail_Billable DEFAULT ((0)) FOR Billable
GO
ALTER TABLE dbo.Tmp_PODetail ADD CONSTRAINT
 DF_PODetail_QtyOrdered DEFAULT ((0)) FOR QtyOrdered
GO
ALTER TABLE dbo.Tmp_PODetail ADD CONSTRAINT
 DF_PODetail_QtyReceived DEFAULT ((0)) FOR QtyReceived
GO
ALTER TABLE dbo.Tmp_PODetail ADD CONSTRAINT
 DF_PODetail_UnitCost DEFAULT ((0)) FOR UnitCost
GO
ALTER TABLE dbo.Tmp_PODetail ADD CONSTRAINT
 DF_PODetail_ReceivedVendorInvoice DEFAULT ((0)) FOR ReceivedVendorInvoice
GO
ALTER TABLE dbo.Tmp_PODetail ADD CONSTRAINT
 DF_PODetail_CreatedOn DEFAULT (getdate()) FOR CreatedOn
GO
ALTER TABLE dbo.Tmp_PODetail ADD CONSTRAINT
 DF_PODetail_ModifiedOn DEFAULT (getdate()) FOR ModifiedOn
GO
SET IDENTITY_INSERT dbo.Tmp_PODetail ON
GO
IF EXISTS(SELECT * FROM dbo.PODetail)
  EXEC('INSERT INTO dbo.Tmp_PODetail (Id, POHeaderId, ItemId, NonInventoryItemDesc, CustomerId, OrderId, Billable, QtyOrdered, QtyReceived, UnitCost, ReceivedVendorInvoice, Notes, CreatedById, CreatedOn, ModifiedById, ModifiedOn)
  SELECT Id, POHeaderId, ItemId, NonInventoryItemDesc, CustomerId, OrderId, Billable, QtyOrdered, QtyReceived, UnitCost, ReceivedVendorInvoice, Notes, CreatedById, CreatedOn, ModifiedById, ModifiedOn FROM dbo.PODetail WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_PODetail OFF
GO
ALTER TABLE dbo.ReceiptDetail
 DROP CONSTRAINT FK_ReceiptDetail_PODetail
GO
DROP TABLE dbo.PODetail
GO
EXECUTE sp_rename N'dbo.Tmp_PODetail', N'PODetail', 'OBJECT' 
GO
ALTER TABLE dbo.PODetail ADD CONSTRAINT
 PK_PODetail PRIMARY KEY CLUSTERED 
 (
 Id
 ) WITH( PAD_INDEX = OFF, FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
CREATE NONCLUSTERED INDEX _dta_index_PODetail_7_759673754__K3_1_2_4_5_6_7_8_9_10_11_12_13_14_15_16_4364 ON dbo.PODetail
 (
 ItemId
 ) INCLUDE (Id, POHeaderId, NonInventoryItemDesc, ReceivedVendorInvoice, Notes, CreatedById, CreatedOn, ModifiedById, ModifiedOn, CustomerId, OrderId, Billable, QtyOrdered, QtyReceived, UnitCost) 
 WITH( PAD_INDEX = OFF, FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.PODetail ADD CONSTRAINT
 FK_PODetail_OrderHeader FOREIGN KEY
 (
 OrderId
 ) REFERENCES dbo.OrderHeader
 (
 Id
 ) ON UPDATE  NO ACTION 
  ON DELETE  SET NULL 

GO
ALTER TABLE dbo.PODetail ADD CONSTRAINT
 FK_PODetail_POHeader FOREIGN KEY
 (
 POHeaderId
 ) REFERENCES dbo.POHeader
 (
 Id
 ) ON UPDATE  CASCADE 
  ON DELETE  CASCADE 

GO
ALTER TABLE dbo.PODetail ADD CONSTRAINT
 FK_PODetail_UserProfile_CreatedBy FOREIGN KEY
 (
 CreatedById
 ) REFERENCES dbo.UserProfile
 (
 UserId
 ) ON UPDATE  NO ACTION 
  ON DELETE  NO ACTION 

GO
ALTER TABLE dbo.PODetail ADD CONSTRAINT
 FK_PODetail_UserProfile_ModifiedBy FOREIGN KEY
 (
 ModifiedById
 ) REFERENCES dbo.UserProfile
 (
 UserId
 ) ON UPDATE  NO ACTION 
  ON DELETE  NO ACTION 

GO
ALTER TABLE dbo.PODetail ADD CONSTRAINT
 FK_PODetail_Items FOREIGN KEY
 (
 ItemId
 ) REFERENCES dbo.Items
 (
 Id
 ) ON UPDATE  NO ACTION 
  ON DELETE  NO ACTION 

GO
ALTER TABLE dbo.PODetail ADD CONSTRAINT
 FK_PODetail_Customers FOREIGN KEY
 (
 CustomerId
 ) REFERENCES dbo.Customers
 (
 Id
 ) ON UPDATE  NO ACTION 
  ON DELETE  NO ACTION 

GO
COMMIT
select Has_Perms_By_Name(N'dbo.PODetail', 'Object', 'ALTER') as ALT_Per, Has_Perms_By_Name(N'dbo.PODetail', 'Object', 'VIEW DEFINITION') as View_def_Per, Has_Perms_By_Name(N'dbo.PODetail', 'Object', 'CONTROL') as Contr_Per BEGIN TRANSACTION
GO
ALTER TABLE dbo.ReceiptDetail ADD CONSTRAINT
 FK_ReceiptDetail_PODetail FOREIGN KEY
 (
 PODetailId
 ) REFERENCES dbo.PODetail
 (
 Id
 ) ON UPDATE  NO ACTION 
  ON DELETE  NO ACTION 

GO
ALTER TABLE dbo.ReceiptDetail SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
select Has_Perms_By_Name(N'dbo.ReceiptDetail', 'Object', 'ALTER') as ALT_Per, Has_Perms_By_Name(N'dbo.ReceiptDetail', 'Object', 'VIEW DEFINITION') as View_def_Per, Has_Perms_By_Name(N'dbo.ReceiptDetail', 'Object', 'CONTROL') as Contr_Per

And here is a partial list of the initial result messages (their management window only allows you to copy one message at a time):

Msg 40512, Level 16, State 1, Line 133
Deprecated feature 'Multiple table hints without comma' is not supported in this version of SQL Server.
Msg 1088, Level 16, State 11, Line 136
Cannot find the object "dbo.Tmp_PODetail" because it does not exist or you do not have permissions.
Msg 15248, Level 11, State 1, Procedure sp_rename, Line 499
Either the parameter @objname is ambiguous or the claimed @objtype (OBJECT) is wrong.

Answer 1

When you create a script for Azure SQL from SSMS, you need to tell it it's for Azure SQL. In the wizard that you used to create the script, there an Advanced button found on the third step of the Wizard (Set Scripting Options). Clicking on the Advanced button will allow you to change the 'Script for the database engine type' option to SQL Azure Database. While you are at it, make sure the other options are set correctly; for example by default the script will only recreate the table - but you may also want to export the data (look at the 'Types of data to script' option). If you do not see the option I mentioned for the engine type, you are using a version of SSMS that is not designed to work with Azure SQL. Hope this helps.