Restore-SqlDatabase leaves a database "Restoring"

PowerShell Help
1

I put together this script to copy a production database (DB_VIEWS) to a development server and restore it. The job will run at night, and keep the development data parallel to production. On a VM where I did the development the SQL Server db is left in the “Restoring” state. The restore runs without error, but the database is unusable. I am familiar with the SQL Server VLF (virtual log file) problem, but I am using SQL Server 2012 SP2 (build 11.0.5058). On a real (not VM) server which supports SQL Server for other development sites the same job runs without error, and the DB is useable,

Invoke-Sqlcmd “ALTER DATABASE DB_VIEWS SET OFFLINE WITH ROLLBACK IMMEDIATE;”
Invoke-Sqlcmd “ALTER DATABASE DB_VIEWS SET ONLINE;”
$a = Get-ChildItem Filesystem::\RC-DBPROD\Backup\DB_VIEWS\DB_VIEWS*.bak | sort LastWriteTime | select -last 1
$RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile(“DB_VIEWS”, “C:\MSSQL\DB_VIEWS.mdf”)
$RelocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile(“DB_VIEWS_log”, “C:\MSSQL\DB_VIEWS_log.ldf”)
Restore-SqlDatabase -ServerInstance RC-DBUPD58 -Database DB_VIEWS -BackupFile $a -RelocateFile @($RelocateData,$RelocateLog) -replacedatabase -norecovery

Why is the database left “Restoring” ?

Thank you for any help.

2

We’ll hope someone jumps in with an answer, but I’ll be honest - we don’t get a lot of SQL folks dropping by here, at this point. This isn’t really a PowerShell question, per se… you’re using PowerShell, sure, but you’re actually running SMO stuff from the .NET Framework, same as a C# application might do. If you find yourself not getting an answer here, consider asking at db.stackexchange.com.

However, I’ll hazard a guess.

Doesn’t “-NoRecovery” tell SQL Server to sit in recovery mode and wait for additional restore files? E.g., if you remove that switch… does it work?

As an alternative, you could not use Restore-SqlDatabase and instead use Invoke-SqlCmd to issue a T-SQL “RESTORE DATABASE” command, if you’re more comfortable with that syntax.

As a note, the virtual-vs-physical has nothing to do with this; don’t let that be a red herring for you.

3

Thank you, Don. You nailed it.

Back in the old days we told folks to RTFM - the Fine Manual (get-help) for the NoRecovery option of Restore-SqlDatabase says (in part): “The database is restored into the restoring state.”. I confused it with the SQL Server Management Studio option to take a tail backup of the database before overwriting it. Removing the option lets the command leave the database usable.