All the good DBAs backup their databases.
A significant amount of SQL DBAs use Ola Hallengrens maintenance solution to do so.
This gives a folder structure like this
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 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
Restore-SqlBackupFromDirectory -SqlServer SQL2016N2 -Path '\\sql2016n2\c$\MSSQL\Backup\SQL2016N2'
Here it is in action
This is the output
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 command. I like to use Out-GridView as it enables you to filter and sort easily
Get-DbaRestoreHistory -SqlServer sql2016N2 | ogv
So you can see each file that was restored and where it was restored to
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
and update it using an Administrator PowerShell session with
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
Then you can use