Restoring database as files from Azure Backup

Question

Hello team,

I hope you're doing fine,

I just restored a database hosted on a sql vm as files from Azure backup with point in time restore,

I restore the DB into a fileshare hosted on a storage account,
the restore finished, when i checked on the fileshare, i found a lot of files :

full .bak file
diff .bak file
Logs files

I wanted to know when restoring using point in time, azure backup doesn't merge the backup files (Full-Incremental....) into a single file to restore it?

In this case, i have to restore the full, because i can't restore the diff file, right?

There is no way to restore the diff in ssms? the logs restore should be done separatly?

thank you

Answer 1

When you perform a point-in-time restore using Azure Backup, it restores the necessary files to allow you to restore the database to that specific point in time. Azure Backup does not merge the full, differential, and log backup files into a single file.

You will need to restore the full backup file (.bak) first, followed by the differential backup file (if any), and then the log backup files in the correct sequence. Here's how you can do it in SQL Server Management Studio (SSMS):

1. Open SSMS and connect to your SQL Server instance.
2. Right-click on the 'Databases' node in the Object Explorer, and then click 'Restore Database'.
3. In the 'Restore Database' dialog, select 'Device' as the source, and click the '...' button to select the backup files.
4. Click 'Add' and browse to the location where your full, differential, and log backup files are stored. Add the full and differential backup files.
5. In the 'Restore Database' dialog, you should now see the backup sets listed. Ensure that the full and differential backups are selected (checkboxes checked), and the appropriate point-in-time is specified if you are restoring to a specific point in time.
6. In the 'Options' page of the 'Restore Database' dialog, you may choose to overwrite the existing database (if you're restoring to the same server) or provide a new name for the restored database (if you're restoring to a new server or as a copy).
7. Click 'OK' to start the restore process. SSMS will restore the full and differential backups.

Once the full and differential backups are restored, you can apply the transaction log backups in sequence:

1. Right-click on the restored database in the Object Explorer, and then click 'Restore > Transaction Log'.
2. In the 'Restore Transaction Log' dialog, select 'Device' as the source, and click the '...' button to select the log backup files.
3. Click 'Add' and browse to the location where your log backup files are stored. Add the log backup files in sequence.
4. In the 'Restore Transaction Log' dialog, you should now see the log backup sets listed. Select the log backups in the correct sequence to restore (checkboxes checked), and specify the appropriate point in time if necessary.
5. Click 'OK' to start the restore process. SSMS will restore the transaction log backups in sequence.

After the process is completed, your database should be restored to the point in time you specified.

Answer 2

Hi Mohamed,

maybe you use a PowerShell script/commandline to restore your several files ;-)

https://docs.dbatools.io/Restore-DbaDatabase

Restore-DbaDatabase -SqlInstance server1\instance1 -Path d:\RestoredFiles 

If you add

-OutputScriptOnly

it will create a TSQL file for you which you can check before and execute from SSMS.

I hope my answer is helpful to you,

Your

Bjoern Peters

If the reply was helpful, please upvote and/or accept it as an answer, as this helps others in the community with similar questions. Thanks!