By: Chad Boyd | Comments (11) | Related: > 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 -?.
Enjoy!
About the author
Chad Boyd is an Architect, Administrator and Developer with technologies such as SQL Server, .NET, and Windows Server.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
