Potential data loss for table rebuild with transactions off
matt_hosking
Posts: 20
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?
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?
Comments
http://redgate.uservoice.com/forums/141379-sql-compare-feature-suggestions
These forums are monitored by our developers and allow our users to request features and vote on them.
If a request recieves significant support the feature may be included in a future release.
Product Support
Red Gate Software