How to Restore SQL Server Database Using PowerShell

Tagged: PowerShell

Problem#

As engineer, it is common to have a task where we want to restore Sql Server database from backup file. For example, there is a bug in your application and you want to reproduce the bug in your local machine, thus you need to restore the database that has been backed up from test environment or production before.

To achieve this task, we can use PowerShell besides manually restore the database using SQL Server Management Studio (SSMS).

In this blog post, we will walk you through how to restore Sql Server database using PowerShell.

Using SqlServer Module#

To use this module, first we need to install the module.

Install-Module -Name SqlServer

Then, we can use Restore-SqlDatabase cmdlet to restore the database by specifying the instance, database name, backup file location, etc.

Restore-SqlDatabase -ServerInstance "U4-3X34FG3" -Database "AgrM7Loc572" -BackupFile "C:\temp\AgrM7Loc572.bak" -AutoRelocateFile -PassThru

If you only use the first three parameters (ServerInstance, Database and BackupFile), you might get below error:

Restore-SqlDatabase: Microsoft.Data.SqlClient.SqlError: Directory lookup for the file "F:\MsData22\AgrM7Loc572.mdf" failed with the operating system error 3(The system cannot find the path specified.).

This happens because a backup file (.bak) contains data (.mdf) and log (.ldf) file and PowerShell will try to restore those data as in original location. Since the original location is unlikely to be available in your machine, you will get above error.

That is why you need to use AutoRelocateFile parameter so that data (.mdf) and log (.ldf) file will be mapped automatically to the default location to store data and log file of your sql server instance.

Nevertheless, after you run the script, you will see the progress bar until the operation is completed.

You will also see the result of database restore in console since we use PassThru parameter.

Using DbaTools Module#

To use this module, first we need to install it from PowerShell Gallery.

Install-Module dbatools

Then, we can use Restore-DbaDatabase cmdlet to restore database. Beforehand, we need to create connection to the instance and store it into a variable. The variable later will be used by Restore-DbaDatabase cmdlet to run actual script to restore database.

TrustServerCertificate is optional in case you get The certificate chain was issued by an authority that is not trusted error message.

By default, this cmdlet will use Windows Authentication credential.

$server = Connect-DbaInstance -SqlInstance 'U4-3X34FG3' -TrustServerCertificate
Restore-DbaDatabase -SqlInstance $server -Path 'C:\temp\AgrM7Loc572.bak'

After executing the script, you will see the progress bar until the operation is completed.

After the operation is completed, you will see some information regarding the database being restored.

You can also connect with other alternative credential like SQL Server Authentication by using SqlCredential parameter. In this case, we use sa account.

$server = Connect-DbaInstance -SqlInstance 'U4-3X34FG3' -SqlCredential (Get-Credential sa) -TrustServerCertificate
Restore-DbaDatabase -SqlInstance $server -Path 'C:\temp\AgrM7Loc572.bak'

It will prompt you user and password when you run the script.

Using .NET Framework#

We can also use SqlConnection and SqlCommand classes from .NET Framework. But, remember we need to apply dispose pattern when dealing with streaming so we will enclose the script with try-finally block to ensure the connection is released regardless error happens or not to avoid memory leak.

The method to restore databsae is pretty different with previous methods in the sense that we cannot automatically relocate data (.mdf) and log (.ldf) file. So, we have to obtain the logical name of those files.

To get the logical name, we need to execute database restore script that uses RESTORE FILELISTONLY phrases. After that, we can execute actual script to restore the database by specifying the location to store mdf and ldf files.

In this context, the location is C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA, so you have to adjust it according to your machine.

The database restore script also uses WITH REPLACE option meaning that it will overwrite existing database if exists.

This example uses Windows Authentication credential.

using namespace System.Data.SqlClient

try {    
    ## The script to get the logical name of data (.mdf) and log (.ldf) file
    $connectionString = 'Data Source=.;Integrated Security=True; Initial Catalog=master;'
    $sqlConnection = New-Object SqlConnection $ConnectionString
    
    $dbName = 'AgrM7Loc572'
    $filePath = 'C:\temp\{0}.bak' -f $dbName
    $cmdText = "RESTORE FILELISTONLY FROM DISK = '{0}'" -f $filePath
    $sqlCmd = New-Object SqlCommand $cmdText, $sqlConnection

    $sqlConnection.Open()
    $databaseFile = [PSCustomObject]@{}
    try {
        $reader = $sqlCmd.ExecuteReader()        
        while ($reader.Read()) {
            $type = $reader["Type"].ToString()
            switch ($type) {
                "D" {
                    $databaseFile | Add-Member -Type NoteProperty -Name DataName -Value $reader["LogicalName"].ToString()
                }
                "L" { 
                    $databaseFile | Add-Member -Type NoteProperty -Name LogName -Value $reader["LogicalName"].ToString()
                }
            }
        }
    }
    catch {
        Write-Host $_.Exception.Message
    }
    finally {
        $reader.Close() 
    }    

    ## The actual script to restore database
    $dataPath = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\{0}.mdf' -f $dbName
    $logPath = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\{0}.ldf' -f $dbName
    $cmdText = "RESTORE DATABASE {0} FROM DISK = '{1}' WITH REPLACE, MOVE '{2}' TO '{3}',
    MOVE '{4}' TO '{5}'" -f $dbName, $filePath, $databaseFile.DataName, $dataPath, $databaseFile.LogName, $logPath
    
    $sqlCmd = New-Object SqlCommand $cmdText, $sqlConnection
    $sqlCmd.ExecuteNonQuery()

    ## This message will be displayed if operations above does not throw any exception
    Write-Host 'Open database connection'
}
catch {
    Write-Host $_.Exception.Message
}
finally {
    ## Close connection and reader to release the resource / free memory
    $sqlConnection.Close()    

    Write-Host 'Close database connection'
}

After executing the script, there will be messages displayed as follows:

Conclusion#

To restore sql server database, we can use either Restore-SqlDatabase cmdlet from SqlServer module or Restore-DbaDatabase cmdlet from dbatools module.

We can also use .Net Framework to restore the database. However, we cannot automatically relocate the data (.mdf) and log (.ldf) file, thus we must get logical name of those files before executing the actual script to restore the database.