Potential data loss for table rebuild with transactions off

Compares and synchronizes SQL Server databases, backups and scripts.

Moderators: JonathanWatts, Chris Auckland, David Atkinson, eddie davis, Anu Deshpande, Michelle Taylor, alice.easey, james.billings, chengvoon.tong

Potential data loss for table rebuild with transactions off

Postby matt_hosking » Thu Oct 04, 2012 8: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 2:43 am
Location: Australia

Postby Manfred.Castro » Mon Oct 08, 2012 6: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 2:49 pm

Return to SQL Compare 10

Who is online

Users browsing this forum: No registered users and 1 guest