Restore database backup files in a folder to SQL server using PowerShell

Ever had a directory full of .bak files and need them all restored to a SQL server? Ever tried to find the solution to that problem through PowerShell instead of some narly looking VBscript on spiceworks?

If you said yes to either of these then the code below is for you. I will try to document more when/if I can but the code does what it says and handles logicial file names that are not the same as the database names and other common gotchas. I have tried to write this in such a way that it requires little or no stateful information and determines everything it needs from the backup data contained within the files and from the configuration parameters of the SQL instance itself.

function invoke-DatabaseRestore {
    param ([String]$SQLServer="(local)", $BackupPath, [String]$BackupFileFilter = "")
    #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
    
    gci $BackupPath -Filter $BackupFileFilter | select fullname | % {
        $backupFile = $_.FullName

        #we will query the database name from the backup header later
        $server = New-Object ( "Microsoft.SqlServer.Management.Smo.Server" ) $SQLServer
        $backupDevice = New-Object( "Microsoft.SqlServer.Management.Smo.BackupDeviceItem" ) ($backupFile, "File")
        $smoRestore = new-object( "Microsoft.SqlServer.Management.Smo.Restore" )
        $backupDevice| FL *

        #Get default log and data file locations http://sqlblog.com/blogs/allen_white/archive/2009/02/19/finding-your-default-file-locations-in-smo.aspx
        $DataPath = if ($server.Settings.DefaultFile.Length -gt 0 ) { $server.Settings.DefaultFile } else { $server.Information.MasterDBLogPath }
        $LogPath = if ($server.Settings.DefaultLog.Length -gt 0 ) { $server.Settings.DefaultLog } else { $server.Information.MasterDBLogPath }

        #restore settings
        $smoRestore.NoRecovery = $false;
        $smoRestore.ReplaceDatabase = $true;
        $smoRestore.Action = "Database"
        $smoRestore.PercentCompleteNotification = 10;
        $smoRestore.Devices.Add($backupDevice)
 
        #get database name from backup file
        $smoRestoreDetails = $smoRestore.ReadBackupHeader($server)
 
        #display database name
        "Database Name from Backup Header : " +$smoRestoreDetails.Rows[0]["DatabaseName"]
 
        #give a new database name
        $smoRestore.Database = $smoRestoreDetails.Rows[0]["DatabaseName"]

        #Relocate each file in the restore to the default directory
        $smoRestoreFiles = $smoRestore.ReadFileList($server)

        foreach ($File in $smoRestoreFiles) {
            #Create relocate file object so that we can restore the database to a different path
            $smoRestoreFile = New-Object( "Microsoft.SqlServer.Management.Smo.RelocateFile" )
 
            #the logical file names should be the logical filename stored in the backup media
            $smoRestoreFile.LogicalFileName = $File.LogicalName

            $smoRestoreFile.PhysicalFileName = $( if($File.Type -eq "L") {$LogPath} else {$DataPath} ) + "\" + [System.IO.Path]::GetFileName($File.PhysicalName)
            $smoRestore.RelocateFiles.Add($smoRestoreFile)
        }
        #restore database
        $smoRestore.SqlRestore($server)
 
    }
}
About these ads

6 thoughts on “Restore database backup files in a folder to SQL server using PowerShell

  1. Alan says:

    Just the script I was looking for. However, I am getting an error:

    Exception calling “SqlRestore” with “1″ argument(s): “Restore failed for Server ‘TESTSERVER’. ”
    At C:\Users\TestAdmin\Desktop\New Scripts\RestoreAllSQLUserDatabases.ps1:63 char:1
    + $smoRestore.SqlRestore($server)
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : FailedOperationException

    • obligatorymoniker says:

      Alan,

      Glad this looks like something you can use.

      Can you reply back with the version of Windows, powershell, SQL server, and the SQL Management tools (if they are from a different version) for the system running the powershell script from and the system your running it against?

      PS, it says line 63 char 1 but the original script only seems to have 55 lines, is there something additional that has been added to this to make it fit your use case? If so can you post the full script your currently using?

      Thanks,

      Chris

  2. Alan says:

    Chris, I made some cosmetic changes mostly. I removed the function component as I won’t be calling this from within a bigger script. I added some comment lines at the top telling people what this script does and I hard coded the backup path. I am running on Windows Server 2012, SQL 2012 and was running in PowerShell ISE.

  3. Alan says:

    Here is the whole script:

    # This script restores all SQL user databases on this machine.
    # Script was taken from a blog entry posted by Chris Magnuson and found
    # at the followng URL: http://obligatorymoniker.wordpress.com/2013/02/26/restore-database-backup-files-in-a-folder-to-sql-server-using-powershell/
    # Edited slightly by me
    # Author: Alan Whitehouse
    # Last Edited: June, 2013

    param ([String]$SQLServer=”(local)”, $BackupPath, [String]$BackupFileFilter = “”)

    $BackupPath = “F:\Backups\SQL\”

    #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

    gci $BackupPath -Filter $BackupFileFilter | select fullname | % {
    $backupFile = $_.FullName

    #we will query the database name from the backup header later
    $server = New-Object ( “Microsoft.SqlServer.Management.Smo.Server” ) $SQLServer
    $backupDevice = New-Object( “Microsoft.SqlServer.Management.Smo.BackupDeviceItem” ) ($backupFile, “File”)
    $smoRestore = new-object( “Microsoft.SqlServer.Management.Smo.Restore” )
    $backupDevice| FL *

    #Get default log and data file locations http://sqlblog.com/blogs/allen_white/archive/2009/02/19/finding-your-default-file-locations-in-smo.aspx
    $DataPath = if ($server.Settings.DefaultFile.Length -gt 0 ) { $server.Settings.DefaultFile } else { $server.Information.MasterDBLogPath }
    $LogPath = if ($server.Settings.DefaultLog.Length -gt 0 ) { $server.Settings.DefaultLog } else { $server.Information.MasterDBLogPath }

    #restore settings
    $smoRestore.NoRecovery = $false;
    $smoRestore.ReplaceDatabase = $true;
    $smoRestore.Action = “Database”
    $smoRestore.PercentCompleteNotification = 10;
    $smoRestore.Devices.Add($backupDevice)

    #get database name from backup file
    $smoRestoreDetails = $smoRestore.ReadBackupHeader($server)

    #display database name
    “Database Name from Backup Header : ” +$smoRestoreDetails.Rows[0]["DatabaseName"]

    #give a new database name
    $smoRestore.Database = $smoRestoreDetails.Rows[0]["DatabaseName"]

    #Relocate each file in the restore to the default directory
    $smoRestoreFiles = $smoRestore.ReadFileList($server)

    foreach ($File in $smoRestoreFiles) {
    #Create relocate file object so that we can restore the database to a different path
    $smoRestoreFile = New-Object( “Microsoft.SqlServer.Management.Smo.RelocateFile” )

    #the logical file names should be the logical filename stored in the backup media
    $smoRestoreFile.LogicalFileName = $File.LogicalName
    $smoRestoreFile.PhysicalFileName = $( if($File.Type -eq “L”) {$LogPath} else {$DataPath} ) + “\” + [System.IO.Path]::GetFileName($File.PhysicalName)
    $smoRestore.RelocateFiles.Add($smoRestoreFile)
    }
    #restore database
    $smoRestore.SqlRestore($server)
    }

  4. Alan says:

    I did more data capture on the error.

    PS C:\Users\TSDemoAdmin01> $error[0] | format-list -force

    Exception : System.Management.Automation.MethodInvocationException: Exception calling “SqlRestore” with “1″ argument(s): “Restore failed for Server ‘TESTSERVER’. ” —>
    Microsoft.SqlServer.Management.Smo.FailedOperationException: Restore failed for Server ‘TSDEMO01′. —>
    Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. —>
    System.Data.SqlClient.SqlException: The operating system returned the error ’32(The process cannot access the file because it is being used by another
    process.)’ while attempting ‘RestoreContainer::ValidateTargetForCreation’ on ‘F:\Program Files\Microsoft SQL
    Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\\TESTDB.mdf’.
    File ‘TESTDB’ cannot be restored to ‘F:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\\TESTDB.mdf’. Use WITH MOVE to
    identify a valid location for the file.
    The operating system returned the error ’32(The process cannot access the file because it is being used by another process.)’ while attempting
    ‘RestoreContainer::ValidateTargetForCreation’ on ‘F:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\\TESTDB_log.ldf’.
    File ‘TESTDB_log’ cannot be restored to ‘F:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\\TESTDB_log.ldf’. Use WITH
    MOVE to identify a valid location for the file.
    Problems were identified while planning for the RESTORE statement. Previous messages provide details.
    RESTORE DATABASE is terminating abnormally.
    at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean
    catchException)
    at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
    — End of inner exception stack trace —
    at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
    at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
    at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)
    at Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql(Server server, StringCollection queries)
    at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
    — End of inner exception stack trace —
    at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
    at CallSite.Target(Closure , CallSite , Object , Object )
    — End of inner exception stack trace —
    at System.Management.Automation.ExceptionHandlingOps.ConvertToMethodInvocationException(Exception exception, Type typeToThrow, String methodName, Int32
    numArgs, MemberInfo memberInfo)
    at CallSite.Target(Closure , CallSite , Object , Object )
    at System.Dynamic.UpdateDelegates.UpdateAndExecute2[T0,T1,TRet](CallSite site, T0 arg0, T1 arg1)
    at System.Management.Automation.Interpreter.DynamicInstruction`3.Run(InterpretedFrame frame)
    at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
    TargetObject :
    CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    FullyQualifiedErrorId : FailedOperationException
    ErrorDetails :
    InvocationInfo : System.Management.Automation.InvocationInfo
    ScriptStackTrace : at , C:\Users\TSDemoAdmin01\Desktop\New Scripts\RestoreAllDatabases.ps1: line 71
    at , C:\Users\TSDemoAdmin01\Desktop\New Scripts\RestoreAllDatabases.ps1: line 27
    PipelineIterationInfo : {}
    PSMessageDetails :

  5. Chris Smith says:

    I apologize for my newbie questions but I’m trying to implement your script. How would you run this as a scheduled task and where do you define the folder where the backups are stored?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: