Based on the error message "Login Failed for user 'domain\machinename$' Reason: Failed to open the explicitly specified database 'ReportServer'. [CLIENT: ]".
Seems the related database had been dropped (database missed) or there is any service is trying to access the database which does not exist.
That means you did not restore the databases correctly or you did not really full back up the databases correctly. And when you use SSMS and manually restore each database, the related database online again, so it works.
Yes we recommend use the Scheduled Backups feature wizard to backup or restore the DBs. However you need to note below things:
You can use the backups you made of the original deployment to restore
the data from that deployment to the new server. When restoring the
data, be sure to restore all databases and the SharePoint Farm to the
same point in time. If you followed the guidance in Create a backup
schedule and plan, you used the Scheduled Backups feature to
create your backups. You will use these backups to restore your data.
If you manually configured your backups, you cannot use the Restore
wizard in Scheduled Backups to restore those databases. You must
manually restore them using the software you used to back them up.
Source : Restore the databases
Generally, we can use the SQL Server Profiler to trace the error, then debug accordingly. Within SQL Server Management Studio , choose Tools menu and then SQL Profiler with Audit Login/Logout, User Error Message events selected and check the relative entries of login fails. See below threads for details:
UPDATE:
For the account issue, please refer to the Required permissions part in this article :Restore the databases. Just add the TFS service account as the sysadmin role, then try it again.
Required permissions
To perform these procedures, you must be a member of the following
groups or have the following permissions:
- A member of the Administrators security group on the server.
- Either a member of the SQL Server System Administrator security group, or your SQL Server Perform Back Up and Create Maintenance
Plan permission must be set to Allow.
- A member of the sysadmin security group for the databases for Team Foundation.
- A member of the Farm Administrators group.
