How to restore a differential backup in SQL Server

Question

Hello,

I have a SQL Server 2017 server where I run this script to automatically back a database up:

sqlcmd -S SERVERVM -E -Q "BACKUP DATABASE MyDB TO MyDB WITH DIFFERENTIAL" -o MyDB.log  

I started it with a full backup first.

Well... I have monitored how the DB backup was incremented in file size after some days of backups. It currently has 80 MB.

Only for testing purposes, I have downloaded the backup to my PC, where I have also SQL Server 2017. Then I chose "Restore Database" option in Management Studio. Finally, I selected the Device source option and select the backup file.

In backup sets to restore listbox, only the last differential backup was shown. If I click on Timeline, I can see all differential backups and at the beginning, I can see the full backup.

When I click OK in that dialog, the following error is shown:

(That means the target database does not exist)

The timeline dialog does allow me to choose which backup to restore. Should I select the full back and nothing else? I have done it, and in that case, the backup sets listbox lists two entries: the full backup and the first differential backup.

That way database could be restored, however, I am not sure if it was restored completely. I think it wasn't.

Is Ok what I did?

Thanks
Jaime

Answer 1

I think the "Timeline ..." button is only used when you try to restore earlier disk backups to a point in time See example D at Restore a Database Backup Using SSMS. For your case, you just select the Source Device option and browse the backup file you downloaded. It should work.

You cannot restore your database using the full backup file downloaded from the other server with the differential backup files generated from your local database.

Answer 2

I does not work.... it shows only the last differential backup, not allowing me to restore.

Answer 3

[This post is an edit, as I failed to observe that you had all backups in the same file.]

Stay away from the UI!

First run

RESTORE HEADERONLY FROM DISK = 'C:\temp\mybackups.bak'

It will list all backups in the file. Pay attention to the BackupType column. 1 is full backup and 5 is Differential. Find the Position number for the last row with 1, that is your full backup. And find the Position column for the last row, this is the diff backup you need.

In this example, I will assume that the full backup has Position =10 and the diff backup Position = 18:

RESTORE DATABASE somedb FROM 'C:\temp\MyBackups.bak'
WITH MOVE 'somedb' TO '<path>',
         MOVE 'somedb_log' TO '<path>',
       NORECOVERY, FILE = 10

RESTORE DATABASE somedb FROM 'C:\temp\MyBackups.bak'
WITH RECOVERY, FILE = 18

The MOVE things are needed to tell where the database is to reside on your computer. Run sp_help on some other database and use that path as a model. The things that come directly after MOVE are the logical names of the files. They are often as in the pattern above, but not always. RESTORE FILELISTONLY FROM DISK = ... can give you the names. Look in the first column.

You need NORECOVERY to be able to apply the differential backup.

I like to point out that it is quite uncommon to write all backups to the same file, as the file can became quite large. Although, if you have a pattern with full backup on Sunday, and then a daily Diff backup, it can be handy to have all backups for a week in the same file.

Answer 4

Hi anonymous usertuardo-7580,

1.please verify your backup files, you can get more information from this reference.

RESTORE VERIFYONLY    
FROM <backup_device> [ ,...n ]    
[ WITH      
 {    
   LOADHISTORY     

--Restore Operation Option    
 | MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name'     
          [ ,...n ]     

--Backup Set Options    
 | FILE = { backup_set_file_number | @backup_set_file_number }     
 | PASSWORD = { password | @password_variable }     

--Media Set Options    
 | MEDIANAME = { media_name | @media_name_variable }     
 | MEDIAPASSWORD = { mediapassword | @mediapassword_variable }    

--Error Management Options    
 | { CHECKSUM | NO_CHECKSUM }     
 | { STOP_ON_ERROR | CONTINUE_AFTER_ERROR }    

--Monitoring Options    
 | STATS [ = percentage ]     

--Tape Options    
 | { REWIND | NOREWIND }     
 | { UNLOAD | NOUNLOAD }      
 } [ ,...n ]    
]    
[;]    

<backup_device> ::=    
{     
   { logical_backup_device_name |    
      @logical_backup_device_name_var }    
   | { DISK | TAPE | URL } = { 'physical_backup_device_name' |    
       @physical_backup_device_name_var }     
}  

2.does this database is an encrypted database? If yes, you must have access to the certificate or asymmetric key used to encrypt the database. If no, please ignore this.
3.I recommend that use T-SQL to restore databases.

 RESTORE DATABASE *new_database_name*    

    FROM *backup_device* [ ,...*n* ]    

    [ WITH    

     {    

        [ **RECOVERY** | NORECOVERY ]    

        [ , ] [ FILE ={ *backup_set_file_number* | @*backup_set_file_number* } ]    

        [ , ] MOVE '*logical_file_name_in_backup*' TO '*operating_system_file_name*' [ ,...*n* ]    

    }    

    ;  

Please get more information from Restore database to a new location; optionally rename the database using T-SQL.

Best regards,
Carrin

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

Please see:

https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-a-differential-database-backup-sql-server?view=sql-server-ver15#TsqlProcedure