All the good DBAs backup their databases. A significant amount of SQL DBAs use [Ola Hallengrens maintenance solution](https://ola.hallengren.com/) to do so. This gives a folder structure like this ![01 - folder structure.PNG](https://blog.robsewell.com/assets/uploads/2017/03/01-folder-structure.png) In my lab I had installed SQL 2016 on a server running Server 2016 TP5 which expired so I needed to re-install Windows and therefore needed to restore all of my user databases again. This was so easy using the [dbatools module](https://dbatools.io) that I thought it was worth sharing to show how easy your disaster recovery process could be. Having re-installed Windows and SQL and copied the backup files back to the server (although I could have used a network location), I then had to restore all of the user databases. This is how I restored all of my user databases using the dbatools module command [Restore-SQLBackupFromDirectory](https://dbatools.io/functions/restore-sqlbackupfromdirectory/) ` Restore-SqlBackupFromDirectory -SqlServer SQL2016N2 -Path '\\\sql2016n2\\c$\\MSSQL\\Backup\\SQL2016N2'` Here it is in action ![02 - Restore in action.PNG](https://blog.robsewell.com/assets/uploads/2017/03/02-restore-in-action.png) This is the output ![03 - output.PNG](https://blog.robsewell.com/assets/uploads/2017/03/03-output.png) That’s it. As simple as that. An entire SQL Servers user databases restored in one line of code. The latest Full, Latest Diff and latest Log backups for each user database all restored to the default file and log location without issue If you look at the help for the command using `Get-Help Restore-SqlBackupFromDirectory -ShowWindow` You will see that there is a -ReuseSourceFolderStructure switch which will use the file structure from the backup file if you have a complex file structure for your SQL files. You can also use a -NoRecovery switch so that you can add further backups, maybe you could use this for setting up mirroring or Always On Availability groups. If you want to check the restore history of your SQL Server then you can use the [Get-DbaRestoreHistory](https://dbatools.io/functions/Get-DbaRestoreHistory/) command. I like to use [Out-GridView](https://msdn.microsoft.com/en-us/powershell/reference/5.1/microsoft.powershell.utility/out-gridview) as it enables you to filter and sort easily ` Get-DbaRestoreHistory -SqlServer sql2016N2 | ogv` [![04 get-database restore history](https://blog.robsewell.com/assets/uploads/2017/03/04-get-database-restore-history.png)](https://blog.robsewell.com/assets/uploads/2017/03/04-get-database-restore-history.png) So you can see each file that was restored and where it was restored to Happy Automating NOTE – The major 1.0 release of dbatools due in the summer 2017 may have breaking changes which will stop the above code from working. There are also new commands coming which may replace this command. This blog post was written using dbatools version `0.8.942` You can check your version using `Get-Module dbatools` and update it using an Administrator PowerShell session with `Update-Module dbatools` You may find that you get no output from Update-Module as you have the latest version. If you have not installed the module from the PowerShell Gallery using `Install-Module dbatools` Then you can use `Update-dbatools`
Rob was a SQL Server DBA.
Now he just helps people with PowerShell, Azure, Automation, and Data.
He is a Cloud and Data Center MVP and a Data Platform MVP, co-leader of Data South West UK user group and PSConfEU organiser. One of the Admins for Data Saturdays https://datasaturdays.com
He is a proud supporter of the Data and PowerShell communities.
He has a fabulous beard
You May Also Enjoy
4 minute read
What is SQLBits? SQLBits is the largest data platform conference in Europe. It has been running every year since 2007 in a different city in the UK providin...
3 minute read
Thank you to the ever wonderful Ben for the suggestion to blog. Check out #NewStarNovember for more blog posts. Who was your mentor? I have never had an of...
3 minute read
Thank you to the ever wonderful Ben for the suggestion to blog. Check out #NewStarNovember for more blog posts. In the beginning It was around May of 2013 ...
How to import dbatools from a zip file from the GitHub release into Azure Automation Modules without an error
2 minute read
There are a number of methods to import PowerShell modules into Azure automation as described in the documentation here