Restoring database as files from Azure Backup

Mohamed jihad bayali 1,101 Reputation points
2023-03-22T12:24:55.8933333+00:00

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

Azure Backup
Azure Backup
An Azure backup service that provides built-in management at scale.
1,102 questions
0 comments No comments
{count} votes

Accepted answer
  1. Sedat SALMAN 12,985 Reputation points
    2023-03-22T12:44:08.5466667+00:00

    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.


1 additional answer

Sort by: Most helpful
  1. Bjoern Peters 8,776 Reputation points
    2023-03-22T14:04:58.52+00:00

    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!

    0 comments No comments