Comments
2 comments
-
I see what happened.
1. To test SQL Compare I had removed a column (Customers.TourGroupName) from my local DB.
2. SQL Compare emitted T-SQL to remove the remote column, but at the end of the script it dropped me (mapsalive) as a user on the remote DB. The script is below.
3. With the user gone, I could no longer access my DB.
Is this a bug or a feature?
George
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Altering [dbo].[Customers]'
GO
ALTER TABLE [dbo].[Customers] DROP
COLUMN [TourGroupName]
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[AppPages]'
GO
CREATE TABLE [dbo].[AppPages]
(
[Id] [int] NOT NULL IDENTITY(1, 1),
[PageName] [nchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_AppPages] on [dbo].[AppPages]'
GO
ALTER TABLE [dbo].[AppPages] ADD CONSTRAINT [PK_AppPages] PRIMARY KEY CLUSTERED ([Id])
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO
PRINT N'Dropping users'
GO
DROP USER [OW\sqlservice]
GO
DROP USER [mapsalive]
GO -
Okay -- it's not a bug or a feature. It was user error, I think.
Having never seen the SQL Compare user interface before, I had been clicking around here and there to familiarize myself with the product. Somehow -- and this is the strange part -- I thought I had only one table checked for synchronization when in fact all User objects were checked. Because there were users in the remote DB (added by my web host) that did not exist in my local DB, SQL Compare dropped them.
I have since used the Filter feature to exclude and hide User objects so that this cannot happen I again.
As a happy ending, I used SQL Compare to synchronize all of my local asp_net tables and views with the remote versions (they differed in auto-generated contraint names) and it worked perfectly.
Add comment
Please sign in to leave a comment.
When I tried to sync one of my tables that had dependencies on the asp_net tables that are created by aspnet_reqsql.exe, SQL Compare got a persmissions error and the sync failed. I think the message said it would roll back the changes, but the target DB was modified anyway.
The target DB is hosted on a shared web server where I don't have full permissions. Using SQL Server Management Studion Express I can still see the DB, but I can no longer see any of my tables or stored procedures -- it appears that they are gone, but I don't know if they really are or if I just can't see them. If I try to create a table I now get an error saying "You are not logged on as the database owner or system administrator."
I recall that when I first ran SQL Compare it found two items (not sure what kind of objects they were) on the remote DB that did not exist in my local DB. They were both related to "owner". My guess is that these got modified or deleted during the sync.
At the moment, I am waiting for my host to restore my DB -- If I learn more from them I'll let you know. It does appear though that SQL Compare did not handle the permission error situation correctly and did some harm.
One other note that might be of interest. I had run aspnet_reqsql.exe locally to create the asp_net tables and had my web host run it on their side to create the tables on the remote DB (because I did not have permission to run them on the remote DB). SQL Compare found the schemas to be different based on some differences which I did not jot down. I don't understand why it thought the schemas were different, and I only mention it because this difference is what led to the sync which caused by DB to become unusable by me.
Regards,
George