Click here to Skip to main content
15,867,308 members
Articles / Programming Languages / PowerShell

SQL DB Restore using PowerShell

Rate me:
Please Sign up or sign in to vote.
5.00/5 (3 votes)
20 Sep 2010CPOL5 min read 94.3K   8   7
Restore an SQL database on a local or remote SQL server using PowerShell

If you are looking for a PowerShell script that lets you restore a database from a backup (.bak) file to MSSQL server, then this is the right place for you.

Database Restore - SQL

Let’s say you have a .bak file (Example: AdventureWorks), and you would like to restore it to an SQL server using PowerShell. If you use SQL Server 2008 Management Studio and click on the “Script” button, you will get the SQL statement that will do that restore for you.

Here is the SQL RESTORE statement that will allow you to restore the AdventureWorksLT2008R2.bak to the new database ‘NewDatabaseName’.

SQL
RESTORE DATABASE [NewDatabaseName]
FROM DISK = N'C:\.etc..\AdventureWorksLT2008R2.bak'
WITH FILE = 1,
MOVE N'AdventureWorksLT2008R2_Data'
TO N'C:\.etc..\MSSQL\DATA\NewDatabaseName.mdf',
MOVE N'AdventureWorksLT2008R2_Log'
TO N'C:\.etc..\MSSQL\DATA\NewDatabaseName_1.ldf',
NOUNLOAD, STATS = 10

If you use SQL Server 2008 R2 Management Studio, the generated SQL statement would look like this:

SQL
RESTORE DATABASE [NewDatabaseName]
FROM  DISK = N'C:\.etc..\AdventureWorksLT2008R2.bak'
WITH  FILE = 1,  NOUNLOAD,  STATS = 10

Running this statement is almost the same as the previous statement, except that the MDF and LDF files would be named AdventureWorksLT2008R2_Data.mdf and AdventureWorksLT2008R2_Log.ldf respectively (instead of having NewDatabaseName in the file names).

If we want to create a simple PowerShell function to run the SQL Restore command, we can do so using the OSQL Utility:

function global:RestoreDB ([string] $newDBName, [string] $backupFilePath)
{
    [string] $dbCommand = "RESTORE DATABASE [$newDBName] " +
    "FROM    DISK = N'$backupFilePath' " +
    "WITH    FILE = 1, NOUNLOAD, STATS = 10"

    OSQL.EXE -E -Q $dbCommand
}

We can also take advantage of the Invoke-Sqlcmd command to do the same thing. Here is how the function would look like:

function global:RestoreDB ([string] $newDBName, [string] $backupFilePath)
{
    [string] $dbCommand = "RESTORE DATABASE [$newDBName] " +
                          "FROM    DISK = N'$backupFilePath' " +
                          "WITH    FILE = 1, NOUNLOAD, STATS = 10"

    Invoke-Sqlcmd -Query $dbCommand
}

If using Invoke-Sqlcmd fails, make sure the SqlServerCmdletSnapin100 snapin is added. You can put the below snippet in the function above before the call to Invoke-Sqlcmd.

$sqlSnapin = Get-PSSnapin | where {$_.Name -eq "SqlServerCmdletSnapin100"}
if($sqlSnapin -eq $null)
{
    Add-PSSnapin SqlServerCmdletSnapin100
}

Calling this function is as simple as:

RestoreDB "NewDatabaseName" "C:\.etc..\AdventureWorksLT2008R2.bak"

Remote SQL Server

In case your SQL Server is on a remote machine that doesn’t have the SQL client installed, you can use the Invoke-Command to restore the database on the remote machine. However, the function needs to be loaded to be invoked on the remote machine, so either put it in a separate file and load it remotely or convert it to a script block that can be directly passed to Invoke-Command.

Assuming the remote SQL server name is SQLServer01, here are the two approaches to do this.

Using a Function

Put the RestoreDB function in a new PowerShell file; Let’s call it DatabaseRestore.ps1. To call remotely, first we need to create a PowerShell session on the remote SQL Server machine.

$session = New-PSSession -ComputerName "SQLServer01"

If you are using SQL Server 2008 R2, you might get the following error: “Connecting to remote server failed with the following error message: The WinRM client cannot complete the operation within the time specified. Check if the machine name is valid and is reachable over the network and firewall exception for Windows Remote Management service is enabled“. The resolution is located here. In short, go to your SQL Server 2008 R2 machine, launch the Command Prompt as administrator, then run this command:

winrm quickconfig

After creating the session, we need to load the DatabaseRestore.ps1 script on the remote machine to be able to call the RestoreDB function.

$scriptPath = "C:\..etc..\DatabaseRestore.ps1"
Invoke-Command -Session $session -FilePath $scriptPath

If you get an error that the file cannot be loaded because the execution of scripts is disabled on the system, then you should make sure that the execution policy is remotesigned or unrestricted (Set-ExecutionPolicy unrestricted).

The next step would be to simply call the function. Note that the path to the backup file must be accessible to the remote SQL machine.

Invoke-Command -Session $session -ScriptBlock 
	{ RestoreDB "NewDatabaseName" "C:\..etc..\AdventureWorksLT2008R2.bak" }
Using a Script Block

In our case, using a script block is simpler. All we need to do is define the script block, then run one single Invoke-Command to do the database restore. The script block definition is almost the same as the function definition, as shown below:

[ScriptBlock] $global:RestoreDB = {

    param ([string] $newDBName, [string] $backupFilePath)

    [string] $dbCommand = "RESTORE DATABASE [$newDBName] " +
                          "FROM    DISK = N'$backupFilePath' " +
                          "WITH    FILE = 1, NOUNLOAD, STATS = 10"

    $sqlSnapin = Get-PSSnapin | where {$_.Name -eq "SqlServerCmdletSnapin100"}
    if($sqlSnapin -eq $null)
    {
         Add-PSSnapin SqlServerCmdletSnapin100
    }

    Invoke-Sqlcmd -Query $dbCommand
}

Make sure you don’t forget the equal sign (=) in the first line of the above script block definition. Now, to restore the DB, we call a single Invoke-Command cmdlet. Make sure that the -ArgumentList parameters are separated by comma (,) or the script will fail.

Invoke-Command -ComputerName "SQLServer01" -ScriptBlock $RestoreDB 
	-ArgumentList "NewDatabaseName", "C:\AdventureWorksLT2008R2.bak" 
Function vs. Script Block

Here are some simple guidelines on when to use a function and when to use a script block.

  • To call a function on a remote machine, it must first be defined on the remote machine. That’s why we need an Invoke-Command to load the PowerShell file containing the function, then another Invoke-Command to call the function.
  • To call a script block remotely, it doesn’t have to be defined on the remote machine. When the script block is passed as a parameter to Invoke-Command, the whole script block actually gets copied to the remote machine.
  • If your logic has any dependency on any global variable or function (Example: it calls another script block or it calls a helper function), then it makes most sense to use a function. Define the function, global variables and helper functions in one file, then load them on the remote machine.
  • If your logic is self-contained, then use a script block. The whole logic will get copied to the remote machine.

Database Restore – SMO

To have more control on how we do the database restore, we should use the SMO object model. For example, our script above restores the database with name “NewDatabaseName” but the MDF and LDF file names are still AdventureWorksLT2008R2_Data.mdf and AdventureWorksLT2008R2_Log.ldf. Now, you would think, why not use the SQL statement below in our script?

SQL
RESTORE DATABASE [NewDatabaseName]
FROM DISK = N'C:\.etc..\AdventureWorksLT2008R2.bak'
WITH FILE = 1,
MOVE N'AdventureWorksLT2008R2_Data'
TO N'C:\.etc..\MSSQL\DATA\NewDatabaseName.mdf',
MOVE N'AdventureWorksLT2008R2_Log'
TO N'C:\.etc..\MSSQL\DATA\NewDatabaseName_1.ldf',
NOUNLOAD, STATS = 10

To put this in a script block, it will look as follows:

[ScriptBlock] $global:RestoreDB = {

    param ([string] $newDBName, [string] $backupFilePath, 
	[string] $sqlDataPath, [string] $dataLogicalName, [string] $logLogicalName)
   
    [string] $dbCommand = "RESTORE DATABASE [$newDBName] " +   
                          "FROM    DISK = N'$backupFilePath' " +
                          "WITH    FILE = 1, " +
                          "MOVE N'$dataLogicalName' " +
                          "TO N'$sqlDataPath\$newDBName.mdf', " +
                          "MOVE N'$logLogicalName' " +
                          "TO N'$sqlDataPath\$newDBName.ldf', " +
                          "NOUNLOAD, STATS = 10"   
   
    $sqlSnapin = Get-PSSnapin | where {$_.Name -eq "SqlServerCmdletSnapin100"}
    if($sqlSnapin -eq $null)
    {
         Add-PSSnapin SqlServerCmdletSnapin100
    }
   
    Invoke-Sqlcmd -Query $dbCommand
}

Ok, this might be good enough, but it’s not dynamic\generic. You can get the SQL data path from the registry, but you cannot get the logical names of the data and log files unless you find a way to open the .bak file and read the logical names of those files. Instead of hardcoding the logical names of the Data and Log files (i.e. pass “AdventureWorksLT2008R2_Data” and “AdventureWorksLT2008R2_Log”), we can read the file list from the .bak file using SMO (ReadFileList() method).

PowerShell comments below provide enough details. Note that if the backup file is on a network share, the ReadFileList() method would fail. So in that case, we need to copy the file locally. That’s why the script block takes “$isNetworkPath” boolean variable to take that into consideration.

[ScriptBlock] $global:RestoreDBSMO = {
    param([string] $newDBName, [string] $backupFilePath, [bool] $isNetworkPath = $true)

    try
    {
        # Load assemblies
        [System.Reflection.Assembly]::LoadWithPartialName
		("Microsoft.SqlServer.SMO") | Out-Null
        [System.Reflection.Assembly]::LoadWithPartialName
		("Microsoft.SqlServer.SmoExtended") | Out-Null
        [Reflection.Assembly]::LoadWithPartialName
		("Microsoft.SqlServer.ConnectionInfo") | Out-Null
        [Reflection.Assembly]::LoadWithPartialName
		("Microsoft.SqlServer.SmoEnum") | Out-Null

        # Create sql server object
        $server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "(local)"

        # Copy database locally if backup file is on a network share
        if($isNetworkPath)
        {
            $fileName = [IO.Path]::GetFileName($backupFilePath)
            $localPath = Join-Path -Path $server.DefaultFile -ChildPath $fileName
            Copy-Item $backupFilePath $localPath
            $backupFilePath = $localPath
        }

        # Create restore object and specify its settings
        $smoRestore = new-object("Microsoft.SqlServer.Management.Smo.Restore")
        $smoRestore.Database = $newDBName
        $smoRestore.NoRecovery = $false;
        $smoRestore.ReplaceDatabase = $true;
        $smoRestore.Action = "Database"

        # Create location to restore from
        $backupDevice = New-Object
	("Microsoft.SqlServer.Management.Smo.BackupDeviceItem") ($backupFilePath, "File")
        $smoRestore.Devices.Add($backupDevice)

        # Give empty string a nice name
        $empty = ""

        # Specify new data file (mdf)
        $smoRestoreDataFile = New-Object
	("Microsoft.SqlServer.Management.Smo.RelocateFile")
        $defaultData = $server.DefaultFile
        if (($defaultData -eq $null) -or ($defaultData -eq $empty))
        {
            $defaultData = $server.MasterDBPath
        }
        $smoRestoreDataFile.PhysicalFileName = 
	Join-Path -Path $defaultData -ChildPath ($newDBName + "_Data.mdf")

        # Specify new log file (ldf)
        $smoRestoreLogFile = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")
        $defaultLog = $server.DefaultLog
        if (($defaultLog -eq $null) -or ($defaultLog -eq $empty))
        {
            $defaultLog = $server.MasterDBLogPath
        }
        $smoRestoreLogFile.PhysicalFileName = Join-Path -Path $defaultLog 
		-ChildPath ($newDBName + "_Log.ldf")

        # Get the file list from backup file
        $dbFileList = $smoRestore.ReadFileList($server)

        # The logical file names should be the logical filename stored in the backup media
        $smoRestoreDataFile.LogicalFileName = 
		$dbFileList.Select("Type = 'D'")[0].LogicalName
        $smoRestoreLogFile.LogicalFileName = 
		$dbFileList.Select("Type = 'L'")[0].LogicalName

        # Add the new data and log files to relocate to
        $smoRestore.RelocateFiles.Add($smoRestoreDataFile)
        $smoRestore.RelocateFiles.Add($smoRestoreLogFile)

        # Restore the database
        $smoRestore.SqlRestore($server)

        "Database restore completed successfully"
    }
    catch [Exception]
    {
        "Database restore failed:`n`n " + $_.Exception
    }
    finally
    {
        # Clean up copied backup file after restore completes successfully
        if($isNetworkPath)
        {
            Remove-Item $backupFilePath
        }
    }
}

Here is how to call this script block (Don’t forget the dot (.) in front of the script block variable):

.$RestoreDBSMO "NewDatabaseName" "C:\.etc..\AdventureWorksLT2008R2.bak" $false

The same story goes for calling this function remotely.

This article was originally posted at http://mycodelog.com/2010/09/18/psdb

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer
United States United States
https://open-gl.com

Comments and Discussions

 
QuestionGreat script .....but getting stuck Pin
Member 1074884814-Apr-14 10:13
Member 1074884814-Apr-14 10:13 
QuestionMicrosoft.SqlServer.Management.Smo.Server Pin
NicolasWahl25-Jul-12 14:54
NicolasWahl25-Jul-12 14:54 
QuestionMultiple DB Restore Pin
Fabkhush15-May-12 1:03
Fabkhush15-May-12 1:03 
AnswerRe: Multiple DB Restore Pin
Ali BaderEddin15-May-12 4:06
Ali BaderEddin15-May-12 4:06 
GeneralRe: Multiple DB Restore Pin
Fabkhush15-May-12 21:09
Fabkhush15-May-12 21:09 
Thanks for the quick turnaround..

Sadly,I'm very new to powershell and learning powershell from the forums. I tried the looping but no luck...
Was thinking if you have any script... or for me to write it would take a good time Smile | :)

Thanks
GeneralAwesome!!! Pin
Mikhail-T3-Apr-12 6:10
Mikhail-T3-Apr-12 6:10 
GeneralRe: Awesome!!! Pin
Ali BaderEddin3-Apr-12 18:04
Ali BaderEddin3-Apr-12 18:04 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.