Forum Discussion

LB-Tech's avatar
LB-Tech
Helper I
1 year ago
Solved

Automation for backup restoration

I have automated backups been automatically stored in my local file everyday, I wanted this backup to be automatically restored in the sql server ssms so that i can connect this to powerbi for analys...
data engineering
data pipeline
  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    1 year ago

    Hi LB-Tech,

    As the backups are in SQL text file format, they cannot be directly restored as binary .bak files. The SQL text files likely contain SQL scripts for recreating database objects and data. Below is a detailed step-by-step solution for automating the process using Azure services while ensuring cost efficiency:

     

    1. Set Up Azure Blob Storage:

      • Create a storage account and container (ex:sqltextfiles).
      • Generate a Shared Access Signature (SAS) token for secure access.

    2. Upload SQL Text Files to Azure Blob Storage:

      • Use PowerShell and the AzCopy tool to upload the SQL text files from the client machine to Azure Blob Storage on a daily basis.
      • Below is a PowerShell script for automating the file upload process. Schedule this script using Windows Task Scheduler to execute daily.

      $sourcePath = "C:\SQLTextFiles\*.sql" # Local folder containing SQL text files
      $destinationURL = "https://<StorageAccountName>.blob.core.windows.net/sqltextfiles" # Replace with your Blob URL
      $sasToken = "<Your_SAS_Token>" # Use SAS token for secure access

      # Upload files using AzCopy
      Start-Process -NoNewWindow -FilePath "C:\PathToAzCopy\azcopy.exe" `
      -ArgumentList "copy `"$sourcePath`" `"$destinationURL`" --recursive --sas-token `"$sasToken`""

    3. Set Up Azure SQL Database:

      • Create an Azure SQL Database. Opt for cost-efficient options such as DTU-based pricing or serverless tiers.

    4. Automate SQL File Execution:

      • Use a PowerShell script to fetch the SQL text files from Azure Blob Storage and execute them on the Azure SQL Database.
      • Below is a PowerShell script for automating the script execution process. Schedule this script using Windows Task Scheduler.

      $storageAccount = "<StorageAccountName>"
      $containerName = "sqltextfiles"
      $sasToken = "<Your_SAS_Token>"
      $sqlServer = "<AzureSQLServerName>.database.windows.net" # Replace with Azure SQL Server
      $databaseName = "<DatabaseName>"
      $username = "<SQLUsername>"
      $password = "<SQLPassword>"
      $destinationPath = "C:\DownloadedSQLFiles"

      # List files in Blob Storage
      az storage blob list --account-name $storageAccount --container-name $containerName `
      --sas-token $sasToken --output table | ForEach-Object {
      $fileName = $_.name
      $localFilePath = "$destinationPath\$fileName"

      # Download SQL file
      az storage blob download --account-name $storageAccount --container-name $containerName `
      --name $fileName --file $localFilePath --sas-token $sasToken

      # Execute SQL file on Azure SQL Database
      $sqlCommand = Get-Content $localFilePath -Raw
      Invoke-Sqlcmd -ServerInstance $sqlServer -Database $databaseName `
      -Username $username -Password $password -Query $sqlCommand
      }

    5. Create and Publish Reports in Power BI:

      • Connect Power BI to the Azure SQL Database, create the required reports, and publish them to the Power BI Service.
      • Set up a scheduled refresh to ensure the reports reflect the latest data.

    Cost-Efficiency Recommendations:

    1. Opt for serverless or basic tiers for Azure SQL Database to minimize costs.
    2. Use hot or cool access tiers in Azure Blob Storage depending on the frequency of file retrieval.
    3. Utilize free tools such as PowerShell and AzCopy to reduce additional software costs.
    4. Leverage Windows Task Scheduler as a free automation solution.

    If you find the response helpful, kindly mark it as the accepted solution and provide kudos, as this will assist other members with similar queries.

     

    Best regards,
    Pavan

v-pnaroju-msft's avatar
v-pnaroju-msft
Community Support
1 year ago

Hi LB-Tech,

 

Please find below the detailed steps to ensure that the backup files are efficiently transferred, restored, and made available for Power BI analysis using Azure services:

  1. Set up Azure Blob Storage:

    • Create a storage container to store the daily .bak backup files.

  2. Automate Backup File Upload:

    • Use PowerShell and the AzCopy tool to upload .bak files from the client machine to Azure Blob Storage.
    • Install AzCopy on the client machine.Please find the PowerShell script to upload backups to Azure Blob Storage as below.Schedule this script to run daily using Windows Task Scheduler.

    # PowerShell Script to Upload Backups to Azure Blob Storage
    $sourcePath = "C:\SQLBackups\*.bak" # Path to local backup folder
    $destinationURL = "https://<YourStorageAccountName>.blob.core.windows.net/backups" # Replace with your Blob URL
    $sasToken = "<Your_SAS_Token>" # Use SAS token for secure access

    # Use AzCopy to upload files to Azure Blob Storage
    Start-Process -NoNewWindow -FilePath "C:\PathToAzCopy\azcopy.exe" `
    -ArgumentList "copy `"$sourcePath`" `"$destinationURL`" --recursive --sas-token `"$sasToken`""

  3. Restore the .bak File Automatically to SQL Server on an Azure Virtual Machine (VM):
    a) Provision an Azure Virtual Machine with SQL Server pre-installed (e.g., SQL Server Developer Edition or Standard Edition).
    b) Use a PowerShell script to fetch the latest .bak file from Azure Blob Storage and restore it to the SQL Server instance running on the Azure VM.Please find the PowerShell script to download the latest backup and restore it to SQL Server as below. Schedule this script to run daily on the Azure VM using Windows Task Scheduler. 

    # PowerShell Script to Download Latest Backup and Restore to SQL Server
    $storageAccount = "<YourStorageAccountName>"
    $containerName = "backups"
    $destinationPath = "C:\SQLRestores\"
    $sasToken = "<Your_SAS_Token>"
    $sqlInstance = "localhost" # Replace with SQL Server instance name
    $databaseName = "YourDatabaseName"

    # Find the latest backup file
    $latestBackup = (az storage blob list --account-name $storageAccount `
    --container-name $containerName --sas-token $sasToken --output table `
    | Sort-Object -Property LastModified -Descending | Select-Object -First 1).name

    # Download the latest backup
    az storage blob download --account-name $storageAccount `
    --container-name $containerName --name $latestBackup `
    --file "$destinationPath\$latestBackup" --sas-token $sasToken

    # Restore the database
    $restoreCommand = "
    RESTORE DATABASE [$databaseName]
    FROM DISK = N'$destinationPath\$latestBackup'
    WITH REPLACE, RECOVERY
    "
    Invoke-Sqlcmd -ServerInstance $sqlInstance -Query $restoreCommand

  4. Connect Restored Database to Power BI:

    • Once the database is restored on the SQL Server in Azure VM, connect to it using Power BI Desktop.
    • Create the required reports and publish them to Power BI Service.
    • Set up a scheduled refresh to ensure the reports reflect the latest data.

If you find this response helpful, please mark it as the accepted solution and provide kudos, as it will assist other community members with similar queries.

 

Best Regards,
Pavan

LB-Tech's avatar
LB-Tech
Helper I
1 year ago

My backup files are in the format of ###SQL Text Files and please suggest me some cost efficient methods

  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    Community Support
    1 year ago

    Hi LB-Tech,

    As the backups are in SQL text file format, they cannot be directly restored as binary .bak files. The SQL text files likely contain SQL scripts for recreating database objects and data. Below is a detailed step-by-step solution for automating the process using Azure services while ensuring cost efficiency:

     

    1. Set Up Azure Blob Storage:

      • Create a storage account and container (ex:sqltextfiles).
      • Generate a Shared Access Signature (SAS) token for secure access.

    2. Upload SQL Text Files to Azure Blob Storage:

      • Use PowerShell and the AzCopy tool to upload the SQL text files from the client machine to Azure Blob Storage on a daily basis.
      • Below is a PowerShell script for automating the file upload process. Schedule this script using Windows Task Scheduler to execute daily.

      $sourcePath = "C:\SQLTextFiles\*.sql" # Local folder containing SQL text files
      $destinationURL = "https://<StorageAccountName>.blob.core.windows.net/sqltextfiles" # Replace with your Blob URL
      $sasToken = "<Your_SAS_Token>" # Use SAS token for secure access

      # Upload files using AzCopy
      Start-Process -NoNewWindow -FilePath "C:\PathToAzCopy\azcopy.exe" `
      -ArgumentList "copy `"$sourcePath`" `"$destinationURL`" --recursive --sas-token `"$sasToken`""

    3. Set Up Azure SQL Database:

      • Create an Azure SQL Database. Opt for cost-efficient options such as DTU-based pricing or serverless tiers.

    4. Automate SQL File Execution:

      • Use a PowerShell script to fetch the SQL text files from Azure Blob Storage and execute them on the Azure SQL Database.
      • Below is a PowerShell script for automating the script execution process. Schedule this script using Windows Task Scheduler.

      $storageAccount = "<StorageAccountName>"
      $containerName = "sqltextfiles"
      $sasToken = "<Your_SAS_Token>"
      $sqlServer = "<AzureSQLServerName>.database.windows.net" # Replace with Azure SQL Server
      $databaseName = "<DatabaseName>"
      $username = "<SQLUsername>"
      $password = "<SQLPassword>"
      $destinationPath = "C:\DownloadedSQLFiles"

      # List files in Blob Storage
      az storage blob list --account-name $storageAccount --container-name $containerName `
      --sas-token $sasToken --output table | ForEach-Object {
      $fileName = $_.name
      $localFilePath = "$destinationPath\$fileName"

      # Download SQL file
      az storage blob download --account-name $storageAccount --container-name $containerName `
      --name $fileName --file $localFilePath --sas-token $sasToken

      # Execute SQL file on Azure SQL Database
      $sqlCommand = Get-Content $localFilePath -Raw
      Invoke-Sqlcmd -ServerInstance $sqlServer -Database $databaseName `
      -Username $username -Password $password -Query $sqlCommand
      }

    5. Create and Publish Reports in Power BI:

      • Connect Power BI to the Azure SQL Database, create the required reports, and publish them to the Power BI Service.
      • Set up a scheduled refresh to ensure the reports reflect the latest data.

    Cost-Efficiency Recommendations:

    1. Opt for serverless or basic tiers for Azure SQL Database to minimize costs.
    2. Use hot or cool access tiers in Azure Blob Storage depending on the frequency of file retrieval.
    3. Utilize free tools such as PowerShell and AzCopy to reduce additional software costs.
    4. Leverage Windows Task Scheduler as a free automation solution.

    If you find the response helpful, kindly mark it as the accepted solution and provide kudos, as this will assist other members with similar queries.

     

    Best regards,
    Pavan