Post reply

LOG SHIPPING - What's included

  • October 28, 2007 at 8:30 pm

    #119129

    We intend to implement log shipping between Production and Backup servers. The databases on the Production server include a lot of SPs that create, drop and alter tables, and create and drop indexes, full text catalogs and fulltext indexes (as well as actually inserting, deleting and updating data). Before taking the log shipping route we need to be certain that the transaction logs that will be shipped will reflect all of these 'schema change' transactions ? Can't find this documented anywhere. Many thanks for any help.

    GCD

  • October 29, 2007 at 5:06 am

    #746162

    Yes the log shipping backups will contain all the data/schema affected by DDL and DML actions. It's just like a regular backup.

    This can be easily and quickly simulated by setting up log shipping between two small test databases on the same server.

    Also, on a side note, if the tables that are being created, dropped and altered are of a temporary nature, why do thye need to be log shipped?

    --------------------
    Colt 45 - the original point and click interface

  • October 29, 2007 at 6:33 am

    #746221

    Phil, Graham, full text indexes are not stored in the database, so am not sure they get shipped over? :unsure:

    Basically anything that gets recorded in the tran log will get shipped over.

    george

    ---------------------------------------------------------------------

  • October 29, 2007 at 7:53 am

    #746252

    Phil, George ...

    Thanks for your responses ... much apreciated ... clarification of the points you mention:

    1) The creation/dropping of tables, indexs, etc does not necessarily apply to the same tables (some tables are created and then populated and maintained indefinitely, some are dropped - but whether they are dropped or not is highly dependent upon data changes ... the 'architectures' of the databases are extremely dynamic and data dependent). This is why I needed to confirm that all DDL statements as well as DML statements would be included in the shipped logs.

    2) I do not need to ship full text indexes, since these will be created independently on the Backup server - but I DO need to know that statements to drop/create and/or populate such catalogs and indexs are shipped ...

    I think you've confirmed what I need to know - that, basically, ALL DDL and DML statements are shipped ...

    If the above indicates that I've 'not got it', please come back with any comments ...

    Thanks again ...

    Graham

  • October 29, 2007 at 4:15 pm

    #746422

    George, given that this is a SQL 2005 forum, I'd assume that Graham is using SQL 2005. If that is the case, then the fulltext catalogs are included in the backups.

    --------------------
    Colt 45 - the original point and click interface

  • October 30, 2007 at 12:33 am

    #746473

    If it is SQL 2005 then full text catalogs are stored in backups.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • October 30, 2007 at 3:34 am

    #746539

    ~~Sugesh Kumar (10/30/2007)

    If it is SQL 2005 then full text catalogs are stored in backups.

    Isn't that what I said 😉

    --------------------
    Colt 45 - the original point and click interface

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

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