0

I am trying to migrate a SQL Server 2016 database to a SQL Server 2019 server. The database is huge at about 500 GB. It takes about 3 hours to create a Full compressed Backup of about 300 GB and 2.5 hours to restore the database. The latest differential backup is about 400MB.

I have tied restoring the database, but I am not sure how can I just restore the differential without having to go through the full backup.

Improve this question
1

1 Answer 1

Reset to default
0

Take the full backup and then recover the full backup and leave it in NORECOVERY mode. This will then allow you to restore the differential backup when you are ready. Check out this article. https://www.sqlmvp.org/restore-database-with-norecovery/ The article explains how this all works. It shows it from the respective of a full and log backup. The process is the same with a differential backup. If you have log backups too, then you can restore the full, restore the differential, restore the log(s).

Definitely also read the article given in the comments. That will give you more to think about and give you a potentially better approach next time.

Improve this answer
2
  • I ran the full restore as NoRecovery. I ran an earlier differential as NoRecovery as well and ran fine, but when running the latest differential, I got the error: "This differential backup cannot be restored because the database has not been restored to the correct earlier state."
    – Rick
    Commented Aug 20, 2021 at 16:59
  • Most likely this is due to another FULL backup that has happened in between your DIFF backups.
    – user103243
    Commented Sep 13, 2021 at 22:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.