Post reply

change/move of Data file without dropping subcription

  • March 22, 2007 at 4:24 am

    #69538

    I have to move the databae data file to another drive.

    but i my database server is already cofigured transactional and merged replication with 66 branch servers

    dear all, is there any way to move data file without dropping replication?

     

    Thanks

    Jahangir Hossen

     

  • March 23, 2007 at 6:36 am

    #696589

    I cannot remember if you can do with this a replicated table, but it is worth trying.

    Rather than moving your existing file, create a second file and filegroup on the new drive.  You can move a table from one file group to another by moving it's clustered index (or creating one for the table on the new filegroup if it does not have one).  You then have to move all of the other indexes.

    It may take you awhile, but you can move all of your tables and indexes to the new filegroup and either shrink and leave the original (if you are just dealing with a space issue), or remove it.  I recently discovered that replication subscriptions do not care about the source filegroup - the snapshot agent simply puts all objects into the default filegroup in the subscriber database.

  • March 25, 2007 at 2:00 pm

    #696783

    Yes, its possibile, you execute update in systabases alter the value of column "category" for 0, move database and restore the value of 1.

     




    Cesar Justi - DBA

  • March 25, 2007 at 9:43 pm

    #696800

    Why not stop the replication agents (distribution/logreader etc), backup the database and then restore it specifying the new location for the data file and then start the replication agents again. There should be no need to update system tables if you do it this way.

    Another option is to detach the database, copy the data file and the re-attach. You will still need to stop the replication agents whilst doing this.

  • March 26, 2007 at 2:58 am

    #696821

    Happycat is right - backing up and then restoring to a different location is the best way.  However, I don't think detaching and re-attaching will work, since I believe this isn't allowed for replicated tables... unless you hack the system table as cjusti mentioned.

    John

  • July 2, 2007 at 12:13 am

    #716445

    Thanks all

    Happycat is right - backing up and then restoring to a different location is the best way. and all have followed it

    stop all replication agent and take backups all databases and restore to new server. started all related agent/services and looking very fine

     

     

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply