Potential data loss for table rebuild with transactions off

Forum for users of SQL Compare schema synchronization utility

Potential data loss for table rebuild with transactions off

Postby matt_hosking » Thu Oct 04, 2012 7:02 am

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
INSERT INTO [dbo].[tmp_rg_xx_tblPerson]([fldPersonID], ...) SELECT [fldPersonID], ... FROM [dbo].[tblPerson]
SET IDENTITY_INSERT [dbo].[tmp_rg_xx_tblPerson] OFF
SELECT @idVal = IDENT_CURRENT(N'[dbo].[tblPerson]')
DBCC CHECKIDENT(N'[dbo].[tmp_rg_xx_tblPerson]', RESEED, @idVal)
DROP TABLE [dbo].[tblPerson]

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?
Posts: 14
Joined: Wed Sep 07, 2011 1:43 am
Location: Australia

Postby Manfred.Castro » Mon Oct 08, 2012 5:26 pm

You can request features for this product in our uservoice forums.
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.
Manfred Castro
Product Support
Red Gate Software
Posts: 209
Joined: Mon Apr 23, 2012 1:49 pm

Return to SQL Compare Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests