SQL Server Performance

copying a log shipped database

Discussion in 'SQL Server Log Shipping' started by Martin Walter, May 2, 2007.

  1. Martin Walter New Member

    I've got to copy a 600GB database.

    I'm not able to restore the backup, (created by data protector which I've got to sort out with HP - and the good thing out of that is the boss has finally realised that tape backups aren't 100% reliable and should be tested) so I was thinking the best/safest idea would be to copy the dr database.

    Ideally I would like to do this without breaking the log shipping. Can this be done, or do I have to stop the log shipping, bring the database on line and then do the copy?

    Thanks for your help.

    Martin
  2. techbabu303 New Member

    Since the database is huge, probably can test this though cumbersome but effective

    step 1 : Take complete back up of database, probably may have to stop log shipping during this process unless GURUS say other wise.

    step 2 : Create a new database with same name on test server

    step 3 : Move the back up to test server

    step 4 : Restore with REPLACE option on test server.


    Cheers
    Sat
  3. satya Moderator

    It is better to pause the log shipping (say disable the job until the backup/restore finishes) and then follow what Sat is suggesting.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  4. Martin Walter New Member

    Hi

    I disabled log shipping but cant back up the database because its in a warm standby stat and it "can't be backed up until the entire load sequence is completed."

    So can I fix this without breaking the log shipping? What do I need to do?

    thanks again
    Martin
  5. MohammedU New Member

    You can't backup the database which is in standby mode...
    Satya and TechBabu might be thinking you are taking source db backup not destination (standby) database backup....

    If you want to use standby database, then
    1. pause the log shipping and
    2. stop the sql services of the server where standby db is
    3. then copy the .mdf and .ldf files
    4. Start the services stopped in step2
    5. Files copied in step3 can be attached using sp_attach_db....

    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  6. Martin Walter New Member

    Thanks Mohammed, I'll try that.
  7. satya Moderator

    http://support.microsoft.com/kb/314515 faq on LS

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Share This Page