locked
log shipping RRS feed

  • Question

  • Can i add a data file in primary server for log shipping? Is it possible to add a data file in primary server for log shipping? if it's possible, how can i follow these steps.....
    Saturday, July 10, 2010 3:28 AM

Answers

  • Yes.  In a log shipping scenario, you only ever make changes to the primary.  If you want a new data file on the primary, then you create it as normal, and the same file will be created on the secondary.  It is not possible to create a file on the primary and not have it created on the secondary.

    If your disk layout is different between the two, you'll need to follow the process I mentioned to ensure that the new file is created in a different location.

    • Marked as answer by Tom Li - MSFT Sunday, August 8, 2010 6:42 AM
    Tuesday, July 13, 2010 5:00 AM
    Answerer

All replies

  • Can you elaborate a liitle bit, do you want to add a secondary data file to the principal database??


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, July 11, 2010 7:12 AM
  • Uri, I'd say that's what he's after.

    The problem with adding a data file, is that you might create the new data file on D: on the primary server, but the log shipping target server may not have a D:, or may not have sufficient space on the D: volume.  Due to the way log shipping works, blindly restoring a transaction log that had an operation of "Create new data file on D:" will cause the new data file to be created on D: on the log shipping target server.

    The trick here is to pause log shipping on the primary (so no more log backups are taken), create the new data file on the primary server, take a manual log backup, and restore the log backup onto the secondary machine using the WITH MOVE 'newdatafile' TO 'G:\newdatafile.ndf'.  Then re-enable log shipping's backup process.

    I've never tried this using the MS-supplied log shipping components, but the process should be much the same.  

    Monday, July 12, 2010 12:58 AM
    Answerer
  • no, ineed to add a data file to the primary server in log shipping? Is it possible..?
    Tuesday, July 13, 2010 4:31 AM
  • Yes.  In a log shipping scenario, you only ever make changes to the primary.  If you want a new data file on the primary, then you create it as normal, and the same file will be created on the secondary.  It is not possible to create a file on the primary and not have it created on the secondary.

    If your disk layout is different between the two, you'll need to follow the process I mentioned to ensure that the new file is created in a different location.

    • Marked as answer by Tom Li - MSFT Sunday, August 8, 2010 6:42 AM
    Tuesday, July 13, 2010 5:00 AM
    Answerer
  • As Jim pointed,   you can add a secondary file to the primary server


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, July 13, 2010 6:56 AM