Error during backup of an SQL database: Could not locate entry in sysdatabases for database [name]. No entry found with that name.
When making a backup of an SQL server, the following error occurs:
Database [path] backup was skipped Exception: Could not locate entry in sysdatabases for database [name]. No entry found with that name. Make sure that the name is entered correctly. BACKUP DATABASE is terminating abnormally. Stacktrace: at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at BackupAgent.Plugins.SQLServer.SQLServerBackend.Backup(SQLServerDatabase database) at BackupAgent.Plugins.SQLServer.SQLServerBackend.Backup(Object item, SQLBackupMethod method)
Presumably, this is caused by an issue with Reporting Services 2005 in SQL. The upgrade scripts, generated for upgrading the database, truncate the name of the database, removing the ReportServer string. Therefore the scripts will reference a nonexistent database.
For now, this is only fixed in SQL Server 2008 Reporting Services, but the following workarounds are available for SQL 2005 Reporting Services:
1. Use a different naming scheme.
2. Generate the upgrade scripts separately, manually modify them to reflect the real name of the database and execute them.
Detailed steps on how to implement the second workaround are below:
a. Make backups of your Reporting Services databases (XReportServer and XReportServerTempDb).
b. From Reporting Services Configuration Manager, make a backup of the Encryption Key.
c. From Reporting Services Configuration Manager, create a new database with a name that does not contain the words ReportServer. We will use this new database to install SP2.
d. Install SP2 for SQL Server 2005 Reporting Services.
e. After installing SP2 and restarting the server, open Reporting Services Configuration Manager and choose the Database Setup option. Here, start the Create Scripts wizard by pressing the Script ... button. Select Upgrade Database Script and put XReportServer for the "Database Name". In "Server Version" select the version that corresponds to the version of the database we're trying to upgrade (the version of the old database ) and finally choose a path to save the script that will be generated.
f. Open SQL Server Management Studio, connected to the SQL instance where the databases Reporting Services are located, and open the script that was saved at the previous step. Modify the script so that it correctly specifies the name of the database that we are trying to upgrade, as well as its temp database (XReportServer and XReportServerTempDb).
g. Execute the script.
h. Again, in Reporting Services Configuration Manager, choose "Database Setup" and connect to the SQL instance by choosing Connect. Select your database (XReportServer) and then click Apply.
i. If the instance is not initialized, the last step will be to restore the encryption key saved at step b. Normally this step should not be needed if the service accounts haven't changed.