sql - 执行表更改脚本删除 Azure SQL 表

Question

我是 Azure 平台的新手，正在使用 Azure SQL 进行测试。我使用本地 SQL Server 实例进行开发，将表更改保存为脚本，然后在 Azure SQL 管理窗口上使用该脚本执行查询。

具体来说，在一项测试中，我向 PODetail 表添加了几列。我从 SQL Server 保存了表更改的脚本。然后，我在 Azure SQL 管理窗口中执行该脚本。它产生了一些错误，并且还删除了 PODetail 表本身。我也没有看到脚本创建的 TMP_PODetail 表...所以最终影响是通过运行一个简单的脚本添加几列来完全丢失表。

这看起来很危险!我错过了什么吗？在尝试重新创建表时，我偶然发现您无法在创建语句中指定表选项，所以我猜测这是根本原因之一，但它不应该更优雅地处理这种情况然后离开 table 掉下来了？有没有办法指定 sql server 中的表脚本创建以生成符合 Azure SQL 的语句？是否有另一种方法可以更好地实现此目的？

这是我运行的脚本:

/*
   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

这里是初始结果消息的部分列表(它们的管理窗口仅允许您一次复制一条消息):

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

当您从 SSMS 创建用于 Azure SQL 的脚本时，您需要告诉它它用于 Azure SQL。在用于创建脚本的向导中，向导的第三步(设置脚本选项)上有一个“高级”按钮。单击“高级”按钮将允许您将“数据库引擎类型的脚本”选项更改为 SQL Azure 数据库。当您执行此操作时，请确保其他选项设置正确；例如，默认情况下，脚本只会重新创建表 - 但您可能还想导出数据(查看“脚本的数据类型”选项)。如果没有看到我提到的引擎类型选项，则说明你使用的 SSMS 版本并非设计用于与 Azure SQL 配合使用。希望这会有所帮助。