I had a circumstance where I needed to restore 100’s of databases from a source server to a destination server, and we had to do it manually one by one, or manually build restore scripts for all databases using T-SQL scripts, or automate the process using PowerShell. I’m planned to use PowerShell to move the databases, however while searching, I came across DBATOOLS, which appears to be a quick and straightforward operation. To accomplish this, we must first install the DBATOOLS Module, and then update it if it is already installed. We used to get Database Refresh requests from Production to DEV/TEST on a regular basis as Database Administrators.
I installed and upgraded DBATOOLS on my machine, then I developed 3 databases to test migration from one instance to another. I already have database backup files in a folder to conduct our work, and now I’m testing to start restoring all 3 databases using DBATOOLS in PowerShell. To restore a database using DBATOOLS, we need to know the Restore command in PowerShell. We can check this in PowerShell using the Get-help *restore* command, which will display all of the available restore commands as well as a synopsis to help us figure out which restore command we can use to complete our task.
I use the command Restore-DbaDatabase from the DBATOOLS commands listed above. I’m only using this command since my backup files are already in a directory with Databases Full Backups, and I need to restore databases from these backup files.
In the above screenshot you can see 3 backup full files for test purpose, I use above backup path in our restore command to restore databases in my destination instance. Also I need to specify some parameters in Restore command like Destination Instance Name, backup Path, destination DATA Directory and Destination LOG Directory
Below is the Complete Restore command used in PowerShell :
You can see in the above Snippet that I ran the Restore command with the parameters passed in, and at the top you can see the restore database operation is in progress, as well as the percent completion of the restore is showing in a Progress bar. Once the Database Restore is complete, it generates an Output that contains the information details. In the Output, we can see the Script Field, which contains the T-sql script used by DBATOOLS to restore databases. We may copy that T-sql script and use it for future restorations.
All 3 databases were successfully restored to my destination instance and the databases are ONLINE.
We may generate Restore Script for all Databases in a Backup path without restoring them to the Destination Instance by using the same command. We just need to add one parameter to the Restore command: -OutputScriptonly. Below is the Full Restore command :
Restore-DbaDatabase -SqlInstance DESKTOP-02JIB76\INS17 -Path 'E:\Backup\INS17' -DestinationDataDirectory 'D:\INS17\DATA' -DestinationLogDirectory 'D:\INS17\LOG' -WithReplace -OutputScriptOnly
When you execute above command it generates the Restore commands for all 3 databases and provides like below :
You can copy the Restore script to your SSMS and will looks like below :
Testing looks Good , I will use this in my current organization Environment .