SSMS: How to restore differential backups

There are two ways to restore a differential backup in SQL Server:

  • Directly use TSQL statements as described in this MSDN page
  • Use SQL Server Management Studio user interface as described here.

If you are using SQL Management Studio to restore differential backups, and you have restored full backups several time using SSMS, but this is your first time to restore a differential backup then you are likely to encounter the following error:

Restore failed for Server 'servername'. (Microsoft.SqlServer.SmoExtended)
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: The log or differential backup cannot be restored because no files are ready to rollforward. (Microsoft.SqlServer.Smo)

This is because you tried to restore a differential backup on an available/operational/functional/running database which is not allowed.

In order to restore a differential backup, you will first need to restore the last full backup with NO RECOVERY option. So, in SSMS you need to select the appropriate full-backup and choose Restore With NoRecovery option from the Options page as depicted in the following screenshot.

restore-full-backup-with-norecovery

Once restored, the database will be shown in the Object Explorer as Restoring.

database-in-restore

Notice that the database is non-available/non-functional at this time and is waiting for a differential backup to be applied. Now, restore the appropriate differential backup and choose Restore With Recovery from the Options page:

restore-differential-backup-with-recovery

That’s it. You have successfully restored a differential backup.

A Final Note:

Note that differential backups are cumulative and each differential backup contain changes since the last full backup, not the last differential backup. So if you have a full backup of 2009-01-01 and have differential backups for each day, and you want to restore your database to 2009-01-10, then you just need to restore the full backup (with no recovery) of 2009-01-01 followed by the differential backup of 2009-01-10. For more information, read the following MSDN articles from SQL Server books online:

Advertisements

13 Responses to “SSMS: How to restore differential backups”

  1. Randy Says:

    So if you do the steps above but also have transaction log backups since the differential, you restore full backup with no recovery AND restore differential with no recovery, then restore all transaction log backups with no recovery except the most recent one, right?

  2. Rasmus Says:

    So nice. Taks…

  3. srini Says:

    Thanks Syed, this is exactly I was looking for, it helped me a lot.

  4. veronicamaslim.com - share.learn.life Says:

    […] : smehrozalam   Tags: restore database, restore differential backup, sql server, sql server […]

  5. qubec 3 Says:

    Thank you so much

    This is what the detailed information I needed

  6. shafiq Says:

    Thanks so much.
    very help full post

  7. Amitabh Says:

    Great Article

  8. Hanif Says:

    Hi thanks a lot for your valuable article, but I have still a problem is restoring the database please look at the below script :

    ALTER DATABASE ABM SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

    –Restoring Full
    RESTORE DATABASE [ABM] FROM DISK = N’C:\SBUP\F1′
    WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
    GO

    –Restoring Transactional
    RESTORE DATABASE [ABM] FROM DISK = N’C:\SBUP\D1′
    WITH FILE = 1, NoRECOVERY, NOUNLOAD, STATS = 10
    GO

    RESTORE DATABASE [ABM] with RECOVERY

    ALTER DATABASE ABM SET MULTI_USER

    After T-SQL compiles the Differential section it shows the below error message :

    “This differential backup cannot be restored because the database has not been restored to the correct earlier state.”

    Do you know what is the workaround ?

    Thanks a lot !

  9. Laxmman Reddy Says:

    Restoring Full
    RESTORE DATABASE [ABM] FROM DISK = N’C:\SBUP\F1′
    WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
    GO

    –Restoring Transactional
    RESTORE DATABASE [ABM] FROM DISK = N’C:\SBUP\D1′
    WITH FILE = 1, NoRECOVERY, NOUNLOAD, STATS = 10
    GO
    Above command is wrong.

    If you want restore database with Tlog backup use below command

    –Restoring Transactional
    RESTORE LOG FROM WITH RECOVERY;
    GO

    RESTORE DATABASE [ABM] with RECOVERY

    ALTER DATABASE ABM SET MULTI_USER

    After T-SQL compiles the Differential section it shows the below error message :

    “This differential backup cannot be restored because the database has not been restored to the correct earlier state.”

    Do you know what is the workaround


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: