question

JaimeStuardo-7580 avatar image
0 Votes"
JaimeStuardo-7580 asked ErlandSommarskog commented

How to restore a differential backup in SQL Server

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:

83364-image.png

(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


sql-server-general
image.png (21.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered GuoxiongYuan-7218 edited

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JaimeStuardo-7580 avatar image
0 Votes"
JaimeStuardo-7580 answered JaimeStuardo-7580 commented

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

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Did you restore the database using the full backup file only or the full backup file with some differential backup files?

0 Votes 0 ·

I don't understand you. The file backup is only one. The first backup type in that file was FULL. All other backups following it has the type DIFFERENTIAL.

0 Votes 0 ·

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.

I think the backup file you downloaded is the full backup file. So you just need to restore the database using this file as I described in the previous answer. The differential backup files in the backup sets are the backups from your local database on your PC. You cannot use them together with the full backup file downloaded from the other server.

0 Votes 0 ·
Show more comments
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

[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.

· 12
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

So, I would need to run with RECOVERY first and then with NORECOVERY, all working in the same backup file?

0 Votes 0 ·

The other way round. NORECOVERY for the full backup and the RECOVERY for the last backup.

Observe that I have edited by post to reflect that you have all in a single file. I did not observer that yesterday.

0 Votes 0 ·

Writing backup files to the same file is actually quite dangerous - if you ever use INIT on the full backup. As soon as you INIT the file you wipe out all previous backups and lose any ability of recovering from an earlier known good backup.

For example - let's say you start your full backup and prior to that full backup completing successfully you have a storage outage. That full backup job fails - but now you don't have a previous backup available, nor do you have your differential backups available.

If you don't use INIT - then your backup file will just continually grow.

Much better to date stamp each file and copy each backup file off the local drives as soon as possible.

0 Votes 0 ·

Absolutely. Although, one could imagine a strategy where the full and diff backups are written to the same date-stamped file.

I can tell a war story. Many years ago, the production one of our customers went belly-up. I looked into restore the database to the state at around 4:00, about which time the error message in the logs were starting to really weird. The backup jobs were set such that log backups were taken five to every hour. Then there was a job that ran at 23:00 which had three steps: 1) Full backup 2) Backup log WITH INIT 3) Copy the full backup elsewhere.

Because the hardware was such in bad shape, the full backup ran for ninety minutes. This was SQL 2000, so the BACKUP LOG that ran at 23:55 was blocked by the full backup. (This does not happen on modern versions of SQL Server.) Once the full backup completed this pending BACKUP LOG ran. And next the job step that had WITH INIT. And so the log chain was broken.

0 Votes 0 ·

Just to end the story: in the end, things went fine. The few transactions that had occurred after the backup completed 00:30, could be recovered from other systems. And this was a Friday night, so we could work all weekend with getting the system back to the life. And, no, not on the same hardware.

0 Votes 0 ·

Do you mean it is better to do full backups always and generating new files for each one naming them with a timestamp?

0 Votes 0 ·
Show more comments

Hello.... thanks for your clarification. For instance this is the results of HEADERONLY command:

87379-image.png

As you see, first backup is full backup and the others are differential.

In that case, after I restore the full backup, then I should restore all differential backups one after the other using this command 14 times:


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

Is that right?


You are right about the file size. At this time, file size is 151 MB and it will be growing for ever. That is normal since after some database inserts, database will be larger, however, since only the differential is backed up, the size will increase accordingly to the quantity of operations made to the database.

Or, what do you recomend to do as a best practice?

Thanks
Jaime

0 Votes 0 ·
image.png (31.2 KiB)

In that case, after I restore the full backup, then I should restore all differential backups one after the other using this command 14 times:

No, that is not right.

I have said this more than once: you need to restore the full backup and the last differential backup. (Or for that matter an earlier, if you want the state of the database at that time.)

You may have transaction-log backups in mind. If you have a full backup and a number of T-Log backups, you need to apply all of them. (And all but the last should be restored WITH NORECOVERY.)

A T-log backup has a flight recorder of the events in the database, so all the steps needs to be a applied.

A differential backups, on the other hand, has all changed pages from the most recent full backups. This is why you only need to apply one of them.

0 Votes 0 ·

You are right about the file size. At this time, file size is 151 MB and it will be growing for ever. That is normal since after some database inserts, database will be larger, however, since only the differential is backed up, the size will increase accordingly to the quantity of operations made to the database.

Or, what do you recomend to do as a best practice?

I'm with Jeffery, one backup per file, typically with a timestamp in the name. Writing multiple backups to one file same requires that your understand exactly what you are doing.

Mixing full and diff backups is alright, but you should take full backups with some frequency. Exactly how often depends what requirements you have when it comes to data loss etc.

And if this is a production database, you should also take log backups.

Tip: to set up a good backup with a good naming convention go to http://ola.hallengren.com, and download what is more or less is the standard solution for database maintenance.

0 Votes 0 ·

Thanks.. will the same apply for restore if I have all backups in different files?

For example, if I create a 2 full backups per month (at 1st and 15th days) and all other days to have differential backup, each one in different files like the following:

 MyDB_FULL_20210401.bak
 MyDB_DIFF_20210402.bak
 MyDB_DIFF_20210403.bak
 MyDB_DIFF_20210404.bak
 ....
 MyDB_FULL_20210415.bak
 MyDB_DIFF_20210416.bak
 MyDB_DIFF_20210417.bak
 MyDB_DIFF_20210418.bak
 ... and so on

Can I proceed the same way in restoration? Since differential backups only contain the data changes between one backup with respect to the previous, I cannot imagine SQL Server to restore all data since the last full backup until the last differential backup (of course, with all data in between). That is why I ask you the same thing several times in order to get sure about that.

Regards,
Jaime

0 Votes 0 ·
Show more comments
CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered ErlandSommarskog commented

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.

· 8
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I think verification succeeds, since I can restore all backups one by one using timeline. The problem is that there are several backups after the first full backup. By using that T-SQL command, can I restore all backups at once?

0 Votes 0 ·

Hi anonymous usertuardo-7580, you don't need to restore all differential backup files. The differential database backup is based on full database backup. So you just need restore the latest full database backup and the latest differential database backup. Please find the T-SQL from this link.

0 Votes 0 ·

I did it... but the database was not full after restoring it. Only initial full backup and the last differential backup were restored.

I have used this command:

 RESTORE DATABASE MyDB_testing  
    FROM [Device MyDev]  
    WITH NORECOVERY, MOVE 'MyDB' TO
 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\MyDB_RECOVER.mdf',
       MOVE 'MyDB_log'
 TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\MyDB_RECOVER.ldf' 

After that, MyDB_testing database appeared in Management Studio, but in "Restoring" state forever.

What was wrong?



0 Votes 0 ·
Show more comments
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered TomPhillips-1744 commented
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

How to specify the target database by using the command. I did not find the parameter.

0 Votes 0 ·

I don't understand the question. The syntax is:

 RESTORE DATABASE {database_name}...

https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql?view=sql-server-ver15#syntax

0 Votes 0 ·

When trying to restore the database, it tries to use the same MDF file name from the source database. That database already exists in the target machine, so I need to restore it using different name,

If you use the UI, you can specify Destination in the first screen of the dialog box. That is what I am asking. If UI allows that, I think command should allow it too. Right?

0 Votes 0 ·
Show more comments

It seems the option to do that is MOVE parameter. I will try it.

0 Votes 0 ·
GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered ErlandSommarskog commented

Now I understand what you try to do. You back up your database to the backup devices and then you copy the backup set file to your local PC for the restore test. From Media Contents in the Backup Devices properties, you can see that the backup set may contain multiple FULL and DIFFERENTIAL backups. What backups you choose for the restore depends on the time point when you want to restore the database. For example, if you choose "Restore to Last backup taken" (which is default) the system will automatically pick the backups for you. It is possible that a FULL backup is the last one. You also can check the contents in the backup set using the following query:


 RESTORE HEADERONLY FROM DISK = 'Backup_Set_Name_with_Location';

The output lists the Position and BackupTypeDescription. You can see if the full backup is the last one. If it is, there is no any differential backup.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

You mean if I use RESTORE DATABASE command once, it will restore the full backup first and then all subseqent differential backups taken across the time? There are are a lot of them (about 30).

0 Votes 0 ·

No. You need to run two RESTORE DATABASE commands. First one to restore the most recent full backup and then one two restore the most recent differential backups. A differential backup has all changed pages in the database since the most recent full backup, so there is no need to restore the intermediate differential backups. (In fact, I don't think it will even work out.)

I can appreciate that this thread is confusing for you, because there are a lot of us saying different things, and some confusion was added because we did not all get that you were having all backups in the same file. However, I believe that my answer above, after the edit I did last night, gives you the instructions you need.

0 Votes 0 ·