CODEWITHSUNDEEP
sql-serverpowershellssms
user1888955
user1888955

Reputation: 626

Database reports option is disabled in SQL Server

I restored the database from a .bak file by running a PowerShell script. Today, when I tried to check the database with "Reports", I noticed it had been disabled. See the screenshot:

enter image description here

Not quite sure whether there is anything wrong with the script I used to restore the database, but here it is: 

$restoredDbName = "dbx"

[string] $dbCommand = "RESTORE DATABASE [$($restoredDbName)] " +
                    "FROM    DISK = N'$($bakFilePath)' " +
                    "WITH    FILE = 1," + 
                    "MOVE N'$($fileLogicalName)' TO     N'$($restorePathD)\$($restoredDbName).mdf'," +
                    "MOVE N'$($fileLogicalName)_Log' TO N'$($restorePathD)\$($restoredDbName)_Log.ldf',"

if($bakFilename.Contains("xyz")) {
    $dbCommand += "MOVE N'abc' TO N'$($restorePathD)\$($restoredDbName).ndf',"
}

$dbCommand += "NOUNLOAD,  REPLACE,  STATS = 1"

Upvotes: 2

Views: 3257

Answers (2)

Codeek
Codeek

Reputation: 1624

It is because your SSMS is of older version than the one required to be compatible with your running version of sql server instance.

Try upgrading your ssms. This should solve the problem.

Upvotes: 1

Zach Olinske
Zach Olinske

Reputation: 557

Few things to try:

It could be a IIS Role that is not installed correctly. Common HTTP Features (Select All) Application Development (Select ASP .NET) Security (Windows Authentication)

It could be that SSMS is not running as an Admin ‘Run as Administrator’ i.e. right-click on the SSMS icon or menu option and opt to ‘Run as Administrator’. Give this a try.

I am sure its not the PowerShell script. Also, I see a few issues in your PowerShell script to SQL. This is my goto script for restoring databases.

$DBName="RestoreDB"
$fileLogicalName_DATA="XYZ"
$fileLogicalName_LOG="XYZ_LOG"
$backupFilePath="D:\RestoreDB.bak"
$Datafilepath="D:\DataStore" #Do not add the trailing \
$Logfilepath="D:\LogStore" #Do not add the trailing \
$dbCommand = "use [master];RESTORE DATABASE [$DBName] " + "FROM DISK = N'$backupFilePath'" + "WITH FILE = 1, NOUNLOAD, STATS = 10, " + "move '$fileLogicalName_DATA' to '$Datafilepath" + "\" + "$DBName" + "_data.mdf', " + "move '$fileLogicalName_LOG' to '$Logfilepath" + "\" + "$DBName" + "_log.ldf';" + "alter database [$DBName] modify file (name=N'$fileLogicalName_DATA', newname=N'$DBName" + "_data');" + "alter database [$DBName] modify file (name=N'$fileLogicalName_LOG', newname=N'$DBName" + "_log');" + "alter database [$DBName] set read_committed_snapshot on with rollback immediate;" + "alter database [$DBName] set COMPATIBILITY_LEVEL = 100;"
sqlcmd -S $DBServerName -E -Q $dbCommand -r0

Upvotes: 0

Related Questions