locked
Log Shipping and Setting up a Debug Environment SQL 2005 RRS feed

  • Question

  • Hello

    I have log shipping setup for a database right now and want to set it up so it can be used as a READ ONLY debug database while still receiving log restores from production.

    The server is SQL SERVER 2005 and the Prod server is in another domain and pulling down a full backup takes 2 days.

    Here are the things I would like to do to the database in between log restores.

    • Set database to READ_ONLY
    • DELETE sensitive information from certain tables like passwords and SSN's
    • Set database to simple recovery to keep logs from getting bigger.

    What I have tried

    I tried running the following SQL after a log restore

    --Restore database to RECOVERY to allow editing
    RESTORE DATABASE databasename WITH RECOVERY
    --Delete statements on tables
    --Set database to readonly and simple recovery
    ALTER DATABASE databasename SET RECOVERY SIMPLE
    ALTER DATABASE databasename SET READ_ONLY

    When the log restores run again I get the following error "The database is already fully recovered." trying to set it to norecovery.  If I don't set it to norecovery the log files fail to restore.

    I am guessing once you set it to RECOVERY mode you break the log shipping and need to restore from a full backup again.

    How can I set it up so that I can keep a fresh readonly copy of data from production while being able to clean out senstive data?

    Tuesday, September 18, 2012 5:47 PM

Answers

  • 1) Here you need to restore your full backup with norecovey mode & than enable your log shipping again. The only solution.

    2) Diffrential backup having all the changes from last full backup. If you have full backup on sunday & then diffrential backup on monday , tuesday & wednesday. In thsi case wednesday diffrential backup will have all changes from sunday full backup including minday & tuesday.

    3) No, you can not copy database while in norecovry mode. You cna log shipping while keeping your database into stand by mode. It will alllows you to read level option on secondary server.


    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    • Marked as answer by GreenWaterBoy Wednesday, September 19, 2012 1:35 PM
    Tuesday, September 18, 2012 9:13 PM

All replies

  • When you bring your database into recovry mode, you are not able to restore any backup after that. You need to restore with replcae option as database is up & runing fine.

    To setup log shipping again, you need restore the backup from production again in norecoveyr mode.


    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    Tuesday, September 18, 2012 7:09 PM
  • Due to the size of our database backups we can only do a full backup once.

    I did notice that unlike T-Logs you can restore differentials regardless of if you have made changes.

    Would either of the following solutions work?

    Do a full backup once, then from now on once a day grab a differential backup and restore from that.  If the full backup was taken on Sunday would the differential taken on Tuesday contain changes made on Monday and Tuesday meaning I eventually will want to grab another full backup to cut down differential size?

    OR

    Is it possible to copy the secondary database while it is in NORECOVERY mode?  As in keep the secondary setup doing log shipping, but copy it so there is a second database that can be recovered from the secondary on a regular basis?


    Tuesday, September 18, 2012 8:54 PM
  • 1) Here you need to restore your full backup with norecovey mode & than enable your log shipping again. The only solution.

    2) Diffrential backup having all the changes from last full backup. If you have full backup on sunday & then diffrential backup on monday , tuesday & wednesday. In thsi case wednesday diffrential backup will have all changes from sunday full backup including minday & tuesday.

    3) No, you can not copy database while in norecovry mode. You cna log shipping while keeping your database into stand by mode. It will alllows you to read level option on secondary server.


    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    • Marked as answer by GreenWaterBoy Wednesday, September 19, 2012 1:35 PM
    Tuesday, September 18, 2012 9:13 PM