locked
Restore full Db backup on another server and transaction log? RRS feed

  • Question

  • If I want to migrate a Db to a new server, and i have the location where the full Db backups and transaction log backups  are being kept, can I use both the Db backup as well as the transaction log backup to restore on the other server?

    However, if there has been new records added since the last full backup, then I should probably just do a full (copy only) backup and take that as my starting point? 

    Tuesday, October 9, 2018 5:11 PM

Answers

  • Yes you can use full and transaction log backups to restore the database. To get changes done, you have to take a log backup again and put database in read_only mode after this restore full backup and all the log backups taken.

    If you do not want to put db in read only mode configure log shipping and do a cut over


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Proposed as answer by Teige Gao Wednesday, October 10, 2018 2:05 AM
    • Marked as answer by Olaf HelperMVP Saturday, November 10, 2018 6:22 AM
    Tuesday, October 9, 2018 5:19 PM
  • I don't really see the point with a copy-only backup in this case. Copy-only is good when taking a backup to restore in test and you don't want to wreck any diff-backup schemes. But since you are migrating, you could just as well take a regular full backup. Or use existing full backup + translog dumps. Whatever, don't throw the older backups away. As always, make sure you have old backups around in case you need to go back for some reason.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Proposed as answer by SQLContent Wednesday, October 10, 2018 2:26 AM
    • Marked as answer by Olaf HelperMVP Saturday, November 10, 2018 6:22 AM
    Tuesday, October 9, 2018 9:14 PM
  • Or, just take full, copy-only backup and use that? 

    Assuming of course, as soon as I take the backup, i set the source Db offline so no other users sneak a new record in.

    Take a full backup and restore on to the target server with No recovery followed by subsequent log backups (or Differentials if you prefer) with no recovery. When it's time to do the cut-over, take a final T-log backup of the source and as you prefer, switch the source DB offline and restore the final log backup on to the target this time, with recovery. Of course, you'd need to migrate the logins and any other instance level objects such as Jobs etc. that rely on this DB being moved. 


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    • Proposed as answer by SQLContent Wednesday, October 10, 2018 2:26 AM
    • Marked as answer by Olaf HelperMVP Saturday, November 10, 2018 6:22 AM
    Tuesday, October 9, 2018 10:11 PM

All replies

  • Yes you can use full and transaction log backups to restore the database. To get changes done, you have to take a log backup again and put database in read_only mode after this restore full backup and all the log backups taken.

    If you do not want to put db in read only mode configure log shipping and do a cut over


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Proposed as answer by Teige Gao Wednesday, October 10, 2018 2:05 AM
    • Marked as answer by Olaf HelperMVP Saturday, November 10, 2018 6:22 AM
    Tuesday, October 9, 2018 5:19 PM
  • Or, just take full, copy-only backup and use that? 

    Assuming of course, as soon as I take the backup, i set the source Db offline so no other users sneak a new record in.

    Tuesday, October 9, 2018 6:42 PM
  • I don't really see the point with a copy-only backup in this case. Copy-only is good when taking a backup to restore in test and you don't want to wreck any diff-backup schemes. But since you are migrating, you could just as well take a regular full backup. Or use existing full backup + translog dumps. Whatever, don't throw the older backups away. As always, make sure you have old backups around in case you need to go back for some reason.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Proposed as answer by SQLContent Wednesday, October 10, 2018 2:26 AM
    • Marked as answer by Olaf HelperMVP Saturday, November 10, 2018 6:22 AM
    Tuesday, October 9, 2018 9:14 PM
  • Or, just take full, copy-only backup and use that? 

    Assuming of course, as soon as I take the backup, i set the source Db offline so no other users sneak a new record in.

    Take a full backup and restore on to the target server with No recovery followed by subsequent log backups (or Differentials if you prefer) with no recovery. When it's time to do the cut-over, take a final T-log backup of the source and as you prefer, switch the source DB offline and restore the final log backup on to the target this time, with recovery. Of course, you'd need to migrate the logins and any other instance level objects such as Jobs etc. that rely on this DB being moved. 


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    • Proposed as answer by SQLContent Wednesday, October 10, 2018 2:26 AM
    • Marked as answer by Olaf HelperMVP Saturday, November 10, 2018 6:22 AM
    Tuesday, October 9, 2018 10:11 PM
  • Hi shiftbit,

    >>Assuming of course, as soon as I take the backup, i set the source Db offline so no other users sneak a new record in.

    Generally, we will do this when few people access this database, for example in the midnight, then it will not affect the product. If taking database offline affects a lot, it would be suggested to use the log shipping.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, October 10, 2018 2:29 AM
  • Or, just take full, copy-only backup and use that? 

    Assuming of course, as soon as I take the backup, i set the source Db offline so no other users sneak a new record in.

    Like others said copy only backup hardly has any use here it is used when you dont want to break LSN chain or don't want to disturb current backup plan. Your priority here is how to move all data with minimum downtime and for that either go with backups or configure LS in both scenarios there would be small downtime always

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Wednesday, October 10, 2018 6:49 AM