How long does it take for powershell to restore a SQL Backup?

Question

I executed this Powershell script to backup a Database, Drop that database and then restore it with a different name:

    $srv = new-object Microsoft.SqlServer.Management.Smo.Server("(local)")
    $res = new-object Microsoft.SqlServer.Management.Smo.Restore
    $backup = new-object Microsoft.SqlServer.Management.Smo.Backup

    $backup.Devices.AddDevice("D:\AWS_CM.bak", [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
    $backup.Database = "Arizona_CM"
    $backup.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Database
    $backup.Initialize = $TRUE
    $backup.SqlBackup($srv)
    $srv.Databases["Arizona_CM"].Drop()

    $res.Devices.AddDevice("D:\AWS_CM.bak", [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
    $res.Database = "AWS_CM"
    $res.NoRecovery = $TRUE
    $res.SqlRestore($srv)

The script worked. However 10 minutes have passed and when I see the Database in SQL Server it is still in "Restoring" mode even though the size is only 80 mb.

Does anyone know how long restoring Database in SQL Server through powershell usually takes?

Answer 1

You are setting the restore to not recover your database.

$res.NoRecovery = $TRUE

Remove this line or set this value to $FALSE and the database will restore as you expect.

This setting is useful when restoring log backups. This includes when you restore the tail of the log. Books Online has more info here

As for the duration of a RESTORE it depends on your storage and the size of your database. With that said, 80MB shouldn't take very long at all.