Restore Database on SQL Server with PowerShell

Today is the day of testing database restoration. Such a test should be done every six months to ensure that the backup and restore work as expected. Sometimes I see that the backup is done outside the maintenance window, manually without the CopyOnly CHECKSUM parameter, and saved to another path, etc. Then it turns out that the whole backup chain is broken and the Point-In-Time database cannot be restored because the file has been deleted, are missing or are corrupted etc. This happens when too many “standard” users have permissions on the SQL server. But this is a story for another post (maybe)

As I said, sometimes I’m lazy when I do the same things every day. And here, of course, Power Shell and dbatools come with help.

First thing first. The best opportunity to test backup is, when someone will restore the production database on the test or development Server, because of testing something or develop new features for application or something else.

In my example, I take the backup from production DB and restore on test with another name to Point-In-Time. The cool thing is that the Powershell script searches all the necessary files from the full and transactional logs that will be needed for database recovery.

Lets start.

First I define the target server variable

$SqlInstance = 'TestServer\InstanceName' #target server

Second, the variable for production database name with the origin name and the backup path where all production database files are stored.

$DBNameOrigin = 'ProductionDB'
$Path = "\\BackupShare\$DBNameOrigin" 

Third, the target database name variable. In my example, I take the Prefix “_Restore” and the original database name. But I can also take the same name as production db.

$DBName = "_Restore$DBNameorgin" #varialbe with different db name
#$DBName = $DBNameorgin #varialbe with the same db name as production

The description for all parameters that I use for the restoring can be found in the help documentation of Restore-DbaDatabase command

For example -ReplaceDbNameInFile, set and occurrence of the original database’s name in a data or log file will be replace with the name specified in the DatabaseName parameter. That means that the files .mdf and .ldf will be replaced from “ProductionDB.mdf”; “ProductionDB.ldf” to “_RestoredProductionDB.mdf” and “_RestoredProductionDB.ldf”

-WithReplace means, if the database already exists on the server, will be replaced, and -RestoreTime is the Point-In-Time date. It could be specified as a date or as shown below, in the last 24 hours from the actual time I am running the script.

$params = @{
                SqlInstance = $sqlinstance
                Path = $Path
                DatabaseName = $DBName
                ReplaceDbNameInFile = $true
                WithReplace = $true
                Verbose = $true
                RestoreTime = (Get-Date "09:00:00 15/07/2020")
                #RestoreTime = (Get-Date).AddHours(-24)
           }

And finally the run command.

Restore-DbaDatabase @params 

I also use -Verbose parameter to see what the script are doing. I hope this help you develop your own restore script.

#target server
$SqlInstance = 'TestServer\InstanceName'

#source database name
$DBNameOrigin = 'ProductionDB'

#path to productin backup share
$Path = "\\BackupShare\$DBNameOrigin" 

#target database name
$DBName = "_Restore$DBNameorgin"

$params = @{
                SqlInstance = $sqlinstance
                Path = $Path
                DatabaseName = $DBName
                ReplaceDbNameInFile = $true
                WithReplace = $true
                Verbose = $true
                RestoreTime = (Get-Date "09:00:00 15/07/2020")
                #RestoreTime = (Get-Date).AddHours(-24)
           }

Restore-DbaDatabase @params

Restore Database on SQL Server with PowerShell

Tweet