Hi
Need to alter a table, which is from a bk file (repl publish db).
Msg 4929, Level 16, State 1, Line 1
Cannot alter the table 'tableA' because it is being published for replication.
Look table property: table is replicated : True
This is on the SQL2005 STD sp2, no replication at all.
How could I get rid of the error to alter the table?
Thanks
David
My friend has helped to solve this issue, via a system sp of removereplication.
Thanks
-D
Is there a way to make the source db at Publishing replication side, to be available to alter for its bk file to be restored on an QA server which has no replication?
Otherwise, I have to run that remove repl proc everytime when the db bk file to be restored on any SQL server (even they are not in repl mode at all).
thanks
-D
If replication is not configured on target server, restore will not bring over replication settings unless you specify KEEP_REPLICATION in restore statement.
Hi Ramiao:
Following info and solution are also from the same database restoring which later prevented us to alter table to drop a pk.
drop a view is not allowed
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
Msg 3165, Level 16, State 1, Line 1
Database 'ABC' was restored, however an error was encountered while replication was being restored/removed. The database has been left offline. See the topic MSSQL_ENG003165 in SQL Server Books Online.
Msg 3167, Level 16, State 1, Line 1
RESTORE could not start database 'ABC'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
--From topic MSSQL_ENG003165 SET ONLINE and change the single user to multiple user
alter database ABC SET ONLINE
use ABC
sp_dboption 'ABC', 'single user', false
--============================================
Here is my restore script:
RESTORE DATABASE ABC FROM disk = 'E:\DB_Backups\ABC_201001160400.bak ' With Replace, MOVE 'ABC_Data' to 'E:\SQL_Data1\ABC.mdf', MOVE 'ABC_Log' to 'D:\SQL_Logs1\ABC_log.ldf'.
the same script for all the QA\Staging\Test server, never used the option of 'keep_replication'
The root cause might be related to the production replication setttings (only 2 databases to be published in replication, with pull from subscribers, have the above problems). Other dbs from production bk and restore to QA\Test are fine. Need to find the root cause and replace the current bandage solution.
--another check on the backup statement (from maintenance plan) --
BACKUP DATABASE [ABC] TO DISK = N'H:\DB_Backups\Weekly\ABC_backup_201002051630.bak ' WITH NOFORMAT, NOINIT, NAME = N'ABC_backup_20100205163021', SKIP, REWIND, NOUNLOAD, STATS = 10
Thanks for your help. Your answer confirms me that a bk file from replication published db, should not carry over its publishing anymore, unless there is a flag to 'keep_replication' to the bk file.
David