Automate Database Restore for SQL Server

27 Comments

1. Younes, that’s just a basic connectivity error. It’s likely the server name is wrong, listening on a port other than 1433, or not running.

2. I run this script qt powershell but i found this erreur anyone here can help me
   Invoke-Sqlcmd : A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the
   instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)
   Au caracte Ligne:27 : 1
   + Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Query $KillCo …
   + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   + CategoryInfo : InvalidOperation : (:) [Invoke-Sqlcmd], SqlException
   + FullyQualifiedErrorId : SqlExceptionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

   Invoke-Sqlcmd : DÉtection d’une syntaxe incorrecte pendant l’analyse de ”.
   Au caracte Ligne:27 : 1
   + Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Query $KillCo …
   + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   + CategoryInfo : ParserError: (:) [Invoke-Sqlcmd], BatchParserException
   + FullyQualifiedErrorId : ExecutionFailureException,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

   Restore-SqlDatabase : Échec de la connexion au serveur 192.168.108.100.
   Au caracte Ligne:32 : 1
   + Restore-SqlDatabase -ServerInstance $TargetSqlServerInstance -Databas …
   + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   + CategoryInfo : NotSpecified: (:) [Restore-SqlDatabase], ConnectionFailureException
   + FullyQualifiedErrorId : Microsoft.SqlServer.Management.Common.ConnectionFailureException,Microsoft.SqlServer.Management.PowerShell.RestoreSqlDatabaseCommand

   Invoke-Sqlcmd : A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the
   instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)
   Au caracte Ligne:35 : 1
   + Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Database $Tar …
   + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   + CategoryInfo : InvalidOperation : (:) [Invoke-Sqlcmd], SqlException
   + FullyQualifiedErrorId : SqlExceptionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

   Invoke-Sqlcmd : DÉtection d’une syntaxe incorrecte pendant l’analyse de ”.
   Au caracte Ligne:35 : 1
   + Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Database $Tar …
   + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   + CategoryInfo : ParserError: (:) [Invoke-Sqlcmd], BatchParserException
   + FullyQualifiedErrorId : ExecutionFailureException,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

   Invoke-Sqlcmd : A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the
   instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)
   Au caracte Ligne:38 : 1
   + Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Database $Tar …
   + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   + CategoryInfo : InvalidOperation : (:) [Invoke-Sqlcmd], SqlException
   + FullyQualifiedErrorId : SqlExceptionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

   Invoke-Sqlcmd : DÉtection d’une syntaxe incorrecte pendant l’analyse de ”.
   Au caracte Ligne:38 : 1
   + Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Database $Tar …
   + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   + CategoryInfo : ParserError: (:) [Invoke-Sqlcmd], BatchParserException
   + FullyQualifiedErrorId : ExecutionFailureException,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

   Invoke-Sqlcmd : A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the
   instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)
   Au caracte Ligne:41 : 1
   + Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Database $Tar …
   + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   + CategoryInfo : InvalidOperation : (:) [Invoke-Sqlcmd], SqlException
   + FullyQualifiedErrorId : SqlExceptionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

   Invoke-Sqlcmd : DÉtection d’une syntaxe incorrecte pendant l’analyse de ”.
   Au caracte Ligne:41 : 1
   + Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Database $Tar …
   + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   + CategoryInfo : ParserError: (:) [Invoke-Sqlcmd], BatchParserException
   + FullyQualifiedErrorId : ExecutionFailureException,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

   Invoke-Sqlcmd : A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the
   instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)
   Au caracte Ligne:44 : 1
   + Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Database $Tar …
   + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   + CategoryInfo : InvalidOperation : (:) [Invoke-Sqlcmd], SqlException
   + FullyQualifiedErrorId : SqlExceptionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

   Invoke-Sqlcmd : DÉtection d’une syntaxe incorrecte pendant l’analyse de ”.
   Au caracte Ligne:44 : 1
   + Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Database $Tar …
   + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   + CategoryInfo : ParserError: (:) [Invoke-Sqlcmd], BatchParserException
   + FullyQualifiedErrorId : ExecutionFailureException,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

   Invoke-Sqlcmd : A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the
   instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)
   Au caracte Ligne:45 : 1
   + Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Database $Tar …
   + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   + CategoryInfo : InvalidOperation : (:) [Invoke-Sqlcmd], SqlException
   + FullyQualifiedErrorId : SqlExceptionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

   Invoke-Sqlcmd : DÉtection d’une syntaxe incorrecte pendant l’analyse de ”.
   Au caracte Ligne:45 : 1
   + Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Database $Tar …
   + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   + CategoryInfo : ParserError: (:) [Invoke-Sqlcmd], BatchParserException
   + FullyQualifiedErrorId : ExecutionFailureException,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

   Invoke-Sqlcmd : A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the
   instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)
   Au caracte Ligne:48 : 1
   + Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Database $Tar …
   + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   + CategoryInfo : InvalidOperation : (:) [Invoke-Sqlcmd], SqlException
   + FullyQualifiedErrorId : SqlExceptionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

   Invoke-Sqlcmd : DÉtection d’une syntaxe incorrecte pendant l’analyse de ”.
   Au caracte Ligne:48 : 1
   + Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Database $Tar …
   + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   + CategoryInfo : ParserError: (:) [Invoke-Sqlcmd], BatchParserException
   + FullyQualifiedErrorId : ExecutionFailureException,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

   Invoke-Sqlcmd : A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the
   instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)
   Au caracte Ligne:51 : 1
   + Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Database $Tar …
   + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   + CategoryInfo : InvalidOperation : (:) [Invoke-Sqlcmd], SqlException
   + FullyQualifiedErrorId : SqlExceptionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

   Invoke-Sqlcmd : DÉtection d’une syntaxe incorrecte pendant l’analyse de ”.
   Au caracte Ligne:51 : 1
   + Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Database $Tar …
   + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   + CategoryInfo : ParserError: (:) [Invoke-Sqlcmd], BatchParserException
   + FullyQualifiedErrorId : ExecutionFailureException,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

3. Ivan, I’m guessing the account you’re running this doesn’t have right to the backup directory. If you step through the script manually, after this line ‘$LatestFullBackupFile = Get-ChildItem -Path $BackupDir -Filter *.bak | Sort-Object LastAccessTime -Descending | Select-Object -First 1’, $LatestFullBackupFile is likely empty.

4. Hi, Just want to ask about my error while doing this script. Actually, this script worked previously on the sql servers, however, when i changed the target SQL servers, i received an error message indicating that Error: 18204, Severity 16, State:1
   BackupDiskFile:OpenMedia: BackupDevice “path of backups” failed to open. Operating System Error 123(the filename, , the directory name, or volume label syntax is incorrect)

   Kindly help me with this one. Thank you

5. Thanks Abel. Stay tuned, there may be another tip coming that will include restoring full plus differential backups.

6. Hello,

   Thanks for the information, greately appreciated, just have question, what would you need to add if I would want to restore a full and a diferential.

   Thanks again.

7. Excellent Mando. Glad the fix was easy.

8. That was it, Joe! Thanks so much. Now trying to get this to work on databases with multiple data files…

9. Mando, I’m suspecting you may have omitted the -ReplaceDatabase.

10. Glenn’s issue is his db name has a ‘-‘ in it.

    I’m working through using this script myself and currently getting this error:
    “
    Restore-SqlDatabase : System.Data.SqlClient.SqlError: The file ‘F:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\[DBNAME].mdf’ cannot be overwritten. It is being used
    by database ‘[DBNAME]’.
    “

    Any ideas?

11. C. Uerel, the database will be overwritten. No need to drop drop and recreate it.

12. if a database was restored from an earlier backup and thus already exists on the SQL server, does this procedure overwrite that db with the newest bak file or do I have to delete that db first by an additional script?

13. Glenn, I can’t really tell where the issue is. I did copy and paste the PowerShell from the tip again, configured it and tested it successfully. Guessing you may have inadvertently have an an additional character or 2 that’s breaking things. I’d suggest copying and pasting it again.

14. Hello,

    I’m trying to run the script but we are getting the below error, anyone has a idea?

    Invoke-Sqlcmd : Incorrect syntax near ‘GO’.
    Incorrect syntax near ‘-‘.
    Incorrect syntax near the keyword ‘with’. If this statement is a common table expression, an
    xmlnamespaces clause or a change tracking context clause, the previous statement must be
    terminated with a semicolon.
    Incorrect syntax near ‘IMMEDIATE’.
    Incorrect syntax near ‘-‘.
    ‘MULTI_USER’ is not a recognized SET option.
    Msg 102, Level 15, State 1, Procedure , Line 1.
    + Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Query $KillCo …

15. Good question Jack. That’s a little out of scope for these requirements. I put this together based on a common request I get to restore test/dev from the previous nights’ full backup. Don’t know off the top of my head how I’d go about with this script. The dbatools Restore-DbaDatabase cmdlet is probably the best place to start.

16. how would you include all the subsequent log files so we always restore the latest copy from production?

    tyia!

17. Thanks Joe. you are very helpful.

18. I have Joey. The reason is with SQL Server (and Sybase for that matter), logins to the SQL Server and users in a database are 2 separate but related entities tied together with a number called a sid. If you’re restoring the backup to the same SQL Server or another one where the logins were added in the same order, the sids will match, and you’re all set. Here are a couple of tips that will give you some more detail:

    https://www.mssqltips.com/sqlservertip/1063/sql-server-database-restores-mapping-users-to-logins/

    https://www.mssqltips.com/sqlservertip/1590/understanding-and-dealing-with-orphaned-users-in-a-sql-server-database/

19. Thanks Joe. Do you come across the user permission issue or login problem after the full backup restore? even the dev database already has the same user logins created as the production database.

20. You are 100% correct Joey. I frequently get requests to restore a dev database from the previous night’s full backup of production so that was my basic requirement for this tip.

21. seems like your script only restore the bak file, it does not restore the subsequent (transaction logs files *.trn). Please correct me if I am wrong.

22. Joe and Greg, no worries and thank you, This script helps me a a lot and saved me loads of time, exactly I was after , keep up the good work!

23. The code has been updated.

    -Greg

24. Ima, sorry you had problems with this. Thanks for pointing it out.

    Greg,

    I confirmed the following lines had soem spaces removed:

    ALTER DATABASE$TargetDb SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    ALTER DATABASE$TargetDb SET MULTI_USER

    Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Query “ALTER DATABASE$($TargetDb) SET COMPATIBILITY_LEVEL =$($CompatLevel)”

    Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Query “ALTER DATABASE$($TargetDb) SET RECOVERY SIMPLE WITH NO_WAIT”

    Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Query “ALTER DATABASE$TargetDb MODIFY FILE (NAME=’$SourceLogicalDataFileName’, NEWNAME=’$TargetLogicalDataFileName’)”

    Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Query “ALTER DATABASE$TargetDb MODIFY FILE (NAME=’$SourceLogicalLogFileName’, NEWNAME=’$TargetLogicalLogFileName’)”

    Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Query “EXEC sp_helpdb$($TargetDb)”

    Here are the edited lines:

    ALTER DATABASE $TargetDb SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    ALTER DATABASE $TargetDb SET MULTI_USER

    Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Query “ALTER DATABASE $($TargetDb) SET COMPATIBILITY_LEVEL = $($CompatLevel)”

    Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Query “ALTER DATABASE $($TargetDb) SET RECOVERY SIMPLE WITH NO_WAIT”

    Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Query “ALTER DATABASE $TargetDb MODIFY FILE (NAME=’$SourceLogicalDataFileName’, NEWNAME=’$TargetLogicalDataFileName’)”

    Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Query “ALTER DATABASE $TargetDb MODIFY FILE (NAME=’$SourceLogicalLogFileName’, NEWNAME=’$TargetLogicalLogFileName’)”

    Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Query “EXEC sp_helpdb $($TargetDb)”

    I can send you the whole scripts with edits if you like.

25. Joe, let me know if the script needs to be updated. It is possible the formatting got messed up when it was converted.

    Thanks
    Greg

26. Great article, thanks very much!

    In case if it helps anyone I wasted couple hours due to errors like below

    Logical file xxx is not part of database yyy..
    Unkown object type ‘xxx’ used in a create, drop..

    Turned out I had to add spaces in multiple places in the script

    DATABASE$($TargetDb) –>DATABASE $($TargetDb)
    DATABASE$TargetDb –> DATABASE $TargetDb
    sp_helpdb$($TargetDb) –> sp_helpdb $($TargetDb)

27. Thanks Mike. Glad you like it.

    I’m presuming we’re talking about multiple full backup files in one directory and you want to test each file. In that case I’d do something like this to build a list of backup files in order then restore each of them within a ForEach.

    # build list of backup files in order from oldest to newest
    $BackupFiles = Get-ChildItem -Path $BackupDir -Filter *.bak | Sort-Object LastAccessTime

    ForEach($FileToRestore in $BackupFiles)
    {
    # kill any connections in target database
    $KillConnectionsSql=
    “
    USE master
    GO
    ALTER DATABASE $TargetDb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    GO
    USE master
    GO
    ALTER DATABASE $TargetDb SET MULTI_USER
    GO
    USE master
    GO
    “
    Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Query $KillConnectionsSql

    # restore
    $RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile(“$SourceLogicalDataFileName”, “$TargetPhysicalDataFileName”)
    $RelocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile(“$SourceLogicalLogFileName”, “$TargetPhysicalLogFileName”)
    Restore-SqlDatabase -ServerInstance $TargetSqlServerInstance -Database $TargetDb -BackupFile $BackupDir\$FileToRestore -RelocateFile @($RelocateData,$RelocateLog) -ReplaceDatabase
    # end restore
    }