Results 1 to 5 of 5

Thread: alter a table from a bk file which is the publication, msg 4929

  1. #1
    Join Date
    Mar 2003
    Posts
    383

    alter a table from a bk file which is the publication, msg 4929

    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

  2. #2
    Join Date
    Mar 2003
    Posts
    383
    My friend has helped to solve this issue, via a system sp of removereplication.

    Thanks
    -D

  3. #3
    Join Date
    Mar 2003
    Posts
    383
    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

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    If replication is not configured on target server, restore will not bring over replication settings unless you specify KEEP_REPLICATION in restore statement.

  5. #5
    Join Date
    Mar 2003
    Posts
    383
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •