1

I'm using the following script to restore the database which has size more than 15 GB and it requires more than 100GB of space for mdf and ldf. However I have the enough space on the drive and I'm able to restore it manually through SSMS. But when I'm using PowerShell script I'm getting this error. I'm using sql server 2012 in my machine. This script is able to restore database with less size and I'm using the SMO for restoring.

Please help me in this. Any other code base is also appreciated.

Exception calling "SqlRestore" with "1" argument(s): "Restore failed for Server 'localhost'. " At D:\CI\RestoreDb.ps1:65 char:9 + $smoRestore.SqlRestore($server) + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : FailedOperationException

        function Invoke-DatabaseRestore {

    param ([String]$SQLServer="(local)",
                    $BackupPath,[String]$DataBaseName, 
                    [String]$BackupFileFilter = "")                 

                             $server = New-Object ( "Microsoft.SqlServer.Management.Smo.Server" ) $SQLServer 
                             if ($server.Databases[$DataBaseName] -ne $null)  
                            {  
                                $server.KillDatabase($DataBaseName)
                                #$server.databases[$DataBaseName].drop()                
                            }
                         # Get-ChildItem $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" ) ($BackupPath, "File")
                    $smoRestore = new-object( "Microsoft.SqlServer.Management.Smo.Restore" )
                    $backupDevice| FL *

                    #Get default log and data file locations
                    $DataPath ="D:\MSSQL\Data" 
                    $LogPath = "D:\MSSQL\Log"

            if(!(Test-Path($DataPath+"\"+$DataBaseName)))
                    {
                            New-Item -Name $DataBaseName -Path $DataPath -ItemType Directory -Force
                    }
                    if(!(Test-Path($LogPath+"\"+$DataBaseName)))
                    {
                            New-Item -Name $DataBaseName -Path $LogPath -ItemType Directory -Force
                    }
                    $DataPath = $DataPath+"\"+$DataBaseName
                    $LogPath = $LogPath+"\"+$DataBaseName

                    #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)

                    #give a new database name
                    $smoRestore.Database = $DataBaseName # $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)

            }


        #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

            $SqlInstance = 'HYDHTC233832D'
            $DatabaseName = "xyz"
            $BackupPath = "D:\xyz.bak"



        Invoke-DatabaseRestore  -SQLServer  $SqlInstance  -BackupPath $BackupPath -DataBaseName $DatabaseName
DhruvJoshi
  • 17,041
  • 6
  • 41
  • 60
Mirza Ahmed
  • 51
  • 3
  • I don't see how the stated error has anything to do with the size of the database. `localhost` is not a valid SQL Server instance name for the local instance. –  May 02 '16 at 08:21
  • I have intentionally renamed it to localhost for security concerns. As same script can be used to restore the Dbs that are lesser in size hence the db size matters. – Mirza Ahmed May 02 '16 at 10:30
  • The answer here might help: http://stackoverflow.com/questions/5979086/sql-server-timeout-while-restoring-databases-with-smo – Mike Shepard May 02 '16 at 13:39
  • It was the same issue Mike I had posted the solution. Thanks – Mirza Ahmed May 03 '16 at 06:56

1 Answers1

0

Here the problem was connection timeout due to large database. Adding $conn.StatementTimeout = 10000 solved me the issue :) Thanks

Mirza Ahmed
  • 51
  • 3