4

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?

  • 3
    You used $res.NoRecovery = $TRUE in query which means you restored database with NORECOVER. You just need to log into SSMS and run restore database db_name with recovery and database will come online. – Shanky Jan 13 '16 at 9:16
3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.