Restore-SqlDb Automate a SQL Server Database Restore improved with Powershell


By:   |   Updated: 2008-10-12   |   Comments (11)   |   Related: More > PowerShell


UPDATE: This script requires the Invoke-TryCatch.ps1 and the newly posted Invoke-Nz.ps1 scripts as well - thanks to all who pinged me about the Inovke-Nz.ps1 being required. 

A while back I posted on Automating a Database Restore and it was quite a popular post, I also received multiple emails with requests for other features in the script such as allowing a restore to a different instance, extending support for SQL 2008 (if you try that script on a 2008 instance you'll notice an error occurs due to the result-set format of the "restore headeronly" statement to support new features like compression and encryption), support for restoring specific files/filegroups, removing the use of xp_cmdshell, etc. I've been wanting to update the procedure for a while anyhow to support some of these things as well and also to migrate the functionality over to PoSh (which is a more appropriate fit to be honest).

This new PoSh version of the script supports everything from the original script plus other features like allowing a restore to be automated/initiated from any machine (as opposed to having to be on the server itself), restoring from one instance to another instance, simply outputting a script of the restore statement(s) and/or execute the restore, removing the dependency on using things like xp_cmdshell, supports SQL 2005 and 2008, exclude differential and/or log backups if you like, and a few others as well. On the PoSh side, this script supports all the major considerations any good PoSh script should such as:

  • Can be dot-sourced into a script
  • Can be invoked from a script (i.e. &restore-sqldb)
  • Fully supports pipeline processing for SMO Database objects and/or any object that can be string-expanded to a database name
  • Friendly usage output (run the script with a single '-?' parameter)
  • Debug and Verbose optional output
  • I don't support a -whatIf directly, but you get this by basically excluding the -execute switch (you'll get a restore script output)

If you haven't read the original script post, I'd encourage you to take a look at it quickly (the text of the blog post, not necessarily the script) to give you an idea of what can be done with the script and hence this script - some of the functionality included allows for things like:

  • Restore a database with nothing more for information than what database and what instance it resides on
  • By default will pull restore information from the msdb database for the instance being restored from. This will basically query the appropriate backup meta-data tables for backup information on the database in question and build the restore statement(s) from that data appropriately including proper ordering, grouping of media sets/families, etc.
  • Can specify '-paths' that support wildcards and can include 1 or more locations to backup files for the database in question - the backup files will be:
    • Investigated for proper ordering of restore sequence
    • Expanded (if they are backup set files containing multiple backups) appropriately
    • Grouped correctly if part of a media family/set (i.e. if you use a backup statement with multiple output files)
  • Can now restore from one instance to a totally different instance (obviously you need to be able to connect to each and have appropriate privileges to do so)
    • Use the '-fromInstance' parameter to specify where to restore from
    • Use the '-toInstance' parameter to specify where to restore to
  • Can specify a new location to move log file(s) to during the restore - this will build the appropriate 'with move...' statement(s) into the restore script to move log files to the specified location(s)
    • You do not need to know anything about where the log file(s) already existed within the backup
  • Can specify new location(s) to move data file(s) to during the restore - this will build the appropriate 'with move...' statement(s) into the restore script to move data files to the specified location(s)
    • You do not necessarily need to include the same number of new locations as existing locations - if there are more data files than new locations, the script will simply round-robin the data files among the new locations
    • You do not need to know anything about where the data file(s) already existing within the backup
  • You can specify a '-stopAt' value that will mimic the 'STOPAT' statement within the restore
  • You can choose to ignore differential backups and/or log backups - by default the script uses all possible backups, this provides some flexibility
  • You can perform a page restore that will pull pages to be restored automatically from the msdb.dbo.suspect_pages table
  • You can perform a restore of only specific files or filegroups - simply include the appropriate logical filenames and/or filegroup names in the '-files' and '-filegroups' parameters
  • If you don't want to incur the overhead of a restore headeronly/filelist only operation and you write backups with a timestamp, you can specify the '-timeStampInFileNames' option and the script will shred each filename for a timestamp value that will act as the ordering/grouping values instead of performing a restore headeronly/filelistonly operation on each
  • Can restore the database with a new name via the '-newDbName' parameter
  • Support for liteSpeed syntax via the '-liteSpeed' switch
  • Checksum support via the '-checksum' switch
  • And much more...(just like on TV)

For those of you familiar with PoSh arguments, you realize you don't have to necessarily include the entire name of a script parameter, just enough of it so the PoSh engine can distinguish it from the other parameter names - this will allow you to short-hand things like the '-toInstance' parameter to just '-to', or the '-fromInstance' to just '-from', or the '-dbName' parameter to just '-db', or just '-lite' for liteSpeed vs. the full '-liteSpeed', or...well, you get the picture.

For detailed usage scenarios and some examples, just PoSh Restore-SqlDb.ps1 -?.

Restore-SqlDb.ps1

Enjoy!



Last Updated: 2008-10-12


get scripts

next tip button



About the author
MSSQLTips author Chad Boyd Chad Boyd is an Architect, Administrator and Developer with technologies such as SQL Server, .NET, and Windows Server.

View all my tips
Related Resources





Comments For This Article




Tuesday, June 16, 2020 - 4:01:22 PM - Hunter Back To Top (86012)
Tip Comments Pending Approval

Thursday, August 23, 2018 - 8:50:35 AM - Vyacheslav Back To Top (77286)

It's not working till i added the second dot in this string

    . .\Invoke-Nz.ps1
    . .\Invoke-TryCatch.ps1

Tuesday, February 14, 2017 - 8:48:24 PM - David Back To Top (46343)

Sorry, never mind, I confirmed for myself it does exactly that. Thanks! 

However, if I could make one suggestion (that I will try to implement myself), I'd include an option to be able to supply different credentials for the SQLcmd. I have some SQL servers which are not domain bound, and so need explicit credentials provided.

Great tool. Thanks!

 


Monday, February 13, 2017 - 11:55:12 AM - David Back To Top (46283)

Hi - thanks very much for this, it looks like it's exactly what I want. However, I'm trying to see if there is a safe/simulation mode, where I can get the output commands but not necessarily have them executed. I can't quite tell if that is possible. If I omit the -execute will I get what I am hoping for? Will it actually perform the operations on the database without the -execute?

 

Thanks!

 

 


Thursday, April 14, 2016 - 12:18:17 PM - rd Back To Top (41225)

 

Hi Greg,

 

Thanks. That helped. Was able to get going. 


Thursday, April 14, 2016 - 6:47:25 AM - Greg Robidoux Back To Top (41219)

Hi RD,

it looks like the Log Path and Data Path are not pointing to the correct location.  It should show a drive location like C:\xxx\

-Greg

 


Thursday, April 14, 2016 - 6:22:28 AM - rd Back To Top (41218)

i am running the following for testing

.\Restore-SqlDb.ps1 -db 'pubs' -from 'From Instance name' -new 'pubs1' -to 'To Instance name' -movelog 'Log Path' -movedata 'Data Path'

Script runs but at the end comes back with the following and database isnt restored . Appreciate any help i can get. Thanks in advance.

use master;

GO

restore database pubs1

from

        disk = '\\rrdsqln1\C$\ClusterStorage\Volume2\Refresh\pubs_full.bak'

with

        file = 1, norecovery, replace,

        move 'pubs_log' to 'Log Path\pubs11.ldf',

        move 'pubs' to 'Data Path\pubs11.mdf';

GO

PS SQLSERVER:\> 

 

 

 


Thursday, November 10, 2011 - 6:24:35 AM - nj Back To Top (15071)

Hi. I just found your script and I am intending to use it on several servers. I have a small problem though. The script doesn't seem to like UNC paths in the -paths attribute and we are moving most of our SQL backups to UNC shares.

I can still run the restore from one instance to another if I use the -from and -to attributes and it picks up the files without any problems and completes the restore, but I am intending to use the script as part of our DR environment so the -from instance won't always be available, and I need to be able to use the -paths instead. The error returned is:

Invoke-Sqlcmd : Cannot open backup device 'E:\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Microsoft.PowerShell.Core\FileSystem:
:\\server\path\dbname\dbname_backup_2011_11_04_020001_6155971.back'. Operating system error 1
23(failed to retrieve text for this error. Reason: 15105).

I have checked permissions and I have currently allowed everyone to access the folder and I still get the error. Also, if it were permissions, restoring using the -from and -to attributes should also have failed.

 


Friday, January 28, 2011 - 3:51:47 PM - Admin Back To Top (12740)

The links should be updated now.  Let me know if you are still having issues.

 


Tuesday, January 11, 2011 - 4:13:44 PM - Boris Back To Top (12581)

I cannot get your scripts. Can you mail them to me? Thank you.


Tuesday, January 11, 2011 - 10:32:35 AM - Lan Back To Top (12566)

Just come across this script, the logic looks great, but the scirpt download link does not work. Do you mind mailing me a copy ?



download





Recommended Reading

Setting the PowerShell Execution Policy

How to find a specific text string in a SQL Server Stored Procedure, Function, View or Trigger

Using PowerShell to Work with Directories and Files

Bulk Copy Data from Oracle to SQL Server

Execute SQL Server Stored Procedures from PowerShell








get free sql tips
agree to terms


Learn more about SQL Server tools