Recovery SQL database via PowerShell

Recovery of a SQL database can be done very easily via PowerShell. For this, the Restore-SqlDatabase command is available.

With the command below, I restore all backups located in the backup folder.

$bakFiles = Get-ChildItem -Path $sqlRestorePath -filter *.bak
foreach($bakFile in $bakfiles) {
    Restore-SqlDatabase -ServerInstance "localhost" -Database $bakFile.BaseName -BackupFile $bakFile.Name -ReplaceDatabase -AutoRelocateFile
}

For clarity, the file PS Variables.ps1 contains the following line:

$sqlRestorePath = "C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup"

We call the file with variables using:

# Begin Dot-source the PS Variables.ps1 file
. "$PSScriptRoot\PS Variables.ps1"
# End Dot-source the PS Variables.ps1 file

If you only want to do a recovery of a single SQL database via PowerShell, you can do so using:

Restore-SqlDatabase -ServerInstance "localhost" -Database "Demo Database BC (26-0)" -BackupFile `
"$appPathBC260\$dvdNameBC260\SQLDemoDatabase\CommonAppData\Microsoft\Microsoft Dynamics NAV\260\Database\Demo Database BC (26-0).bak" `
-ReplaceDatabase -AutoRelocateFile

The Restore-SqlDatabase cmdlet in PowerShell is used to restore a SQL Server database from a backup file or transaction log.

This cmdlet offers several options, such as restoring full databases, transaction logs, and database files.

Important parameters you can use related to recovery of a SQL database via PowerShell

• AutoRelocateFile: Ensures that the data and log files are automatically relocated, for example to the default location of the SQL Server instance.
• ServerInstance: Specifies the name of the SQL Server instance.
• Database: The name of the database you want to restore.
• BackupFile: The path to the backup file.

To use the Restore-SqlDatabase cmdlet, there are a few requirements and conditions that must be met:

• SQL Server Module: The cmdlet is part of the SqlServer module, which must be installed on your system. You can install this module using the following PowerShell command, for example: powershellCopyEditInstall-Module -Name SqlServer
• SQL Server Instance: You must have access to a SQL Server instance where you want to restore the database. This includes the proper permissions to perform restore operations.
• Backup File: A valid backup file (.bak) of the database you want to restore.
• PowerShell Version: Ensure that you are using a compatible version of PowerShell that supports the SqlServer module.

Finally, a similar command is Backup-SqlDatabase. I will discuss the PowerShell command Backup-SqlDatabase in a separate post on the Digitale Mels website.

More information about Microsoft Business Central can be found here. To go back to the Home page, you can use this link.