Database reports option is disabled in SQL Server

Question

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:

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"

Are you shure that's not permission problems? Have you tried connecting to the server using user with sysadmin permissions ('sa') to check if the issue still exists? — Shahar Gvirtz, Feb 21 '17 at 19:54

score 1 · Answer 1 · answered Nov 18 '19 at 06:34

1

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.

answered Nov 18 '19 at 06:34

Codeek

1,624
1
11
20

Zach Olinske · Answer 2 · 2017-02-22T18:43:09.213

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

I have added IIS. See if that is installed correctly. SSMS sometimes has issues with it. — Zach Olinske, Feb 22 '17 at 18:43
Yeah, IIS was installed and it's been working well. The thing i feel quite curious is that I restored the bak on another sql server then it showed up. Not quite sure what's wrong with the older one... — user1888955, Feb 24 '17 at 15:32

Database reports option is disabled in SQL Server

2 Answers2