Post reply

Best way to refresh a copy of a database on a weekly basis?

  • May 21, 2004 at 3:51 pm

    #102028

    I've got a copy of a production database in the DMZ that's available for web access and  I need to refresh the contents on a weekly basis.  The same users exist in both databases because of the application.  I assumed snapshot replication would do the trick, but permissions and identity properties are not preserved in the snapshot (I've tried all the different Article properties).  Am I doing something wrong with snapshot replication or is there a better solution?

    Neil

  • May 23, 2004 at 6:20 pm

    #507393

    When you create your Publication, go to the properties of the Article and select the Snapshot tab. From there choose the option "keep the existing table unchanged". By doing this, you are not dropping any object and permissions are maintained!

    Got it?

    Kindest Regards,

  • May 24, 2004 at 9:13 am

    #507528

    replication is for data movement, not schema, permissions, etc.

    I use backup/restore.

  • May 24, 2004 at 10:49 am

    #507537

    Yeah, I tried that. There are constraints that prevent data refresh when the "keep the table unchanged" option is set.  I was surprised that the "drop the existing table and recreate it" option does not preserve permissions or identity columns.

  • May 24, 2004 at 5:13 pm

    #507556

    How on earth do you expect to retain permissions when you DROP a table? SQL Server isn't some magical toy that can pull a rabit out of a hat!

    Think about it! What happens when you DROP an object?

    Kindest Regards,

  • May 24, 2004 at 5:50 pm

    #507560

    I appreciate the assistance, but the condencending tone is neither helpful nor appropriate. I was merely stating my expectation that when a given table was recreated via the snapshot , it would be recreated with the same properties as the source table.  It makes sense for SQL server to not expect the same user accounts to exist on the target server, but it would be nice to have that option, something like "drop the existing table and recreate it with the same permissions, identity columns, etc."

    I took Steve's advice and used backup and restore.

    Thanks.

     

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

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