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.