SQL Compare decided to rebuild a table in our deployment script as it couldn't perform alters and since we don't have transactions enabled for the deployment script, we ended up with something like this:
SET IDENTITY_INSERT [dbo].[tmp_rg_xx_tblPerson] ON
GO
INSERT INTO [dbo].[tmp_rg_xx_tblPerson]([fldPersonID], ...) SELECT [fldPersonID], ... FROM [dbo].[tblPerson]
GO
SET IDENTITY_INSERT [dbo].[tmp_rg_xx_tblPerson] OFF
GO
DECLARE @idVal BIGINT
SELECT @idVal = IDENT_CURRENT(N'[dbo].[tblPerson]')
IF @idVal IS NOT NULL
DBCC CHECKIDENT(N'[dbo].[tmp_rg_xx_tblPerson]', RESEED, @idVal)
GO
DROP TABLE [dbo].[tblPerson]
GO
The problem here, which is fairly evident, is that this script will not care if the migration of records is successful when it drops the original table. I know that turning on transactions would help here, and databases should always be backed up before running scripts, but should SQL compare ever generate a script that will potentially destroy your data if an error occurs?
My proposal is that even when transactions are off, the insert to the drop should always be wrapped in a transaction. Is this a recorded issue somewhere? Otherwise, should I log it?
You can request features for this product in our uservoice forums.
SET IDENTITY_INSERT [dbo].[tmp_rg_xx_tblPerson] ON
GO
INSERT INTO [dbo].[tmp_rg_xx_tblPerson]([fldPersonID], ...) SELECT [fldPersonID], ... FROM [dbo].[tblPerson]
GO
SET IDENTITY_INSERT [dbo].[tmp_rg_xx_tblPerson] OFF
GO
DECLARE @idVal BIGINT
SELECT @idVal = IDENT_CURRENT(N'[dbo].[tblPerson]')
IF @idVal IS NOT NULL
DBCC CHECKIDENT(N'[dbo].[tmp_rg_xx_tblPerson]', RESEED, @idVal)
GO
DROP TABLE [dbo].[tblPerson]
GO
The problem here, which is fairly evident, is that this script will not care if the migration of records is successful when it drops the original table. I know that turning on transactions would help here, and databases should always be backed up before running scripts, but should SQL compare ever generate a script that will potentially destroy your data if an error occurs?
My proposal is that even when transactions are off, the insert to the drop should always be wrapped in a transaction. Is this a recorded issue somewhere? Otherwise, should I log it?