Product:
Microsoft SQL server 2016
Issue:
SSIS job fail with error:
Could not find database ID 42, name ’42’. The database may be offline. Wait a few minutes and try again.
Solution:
Check if the database have change ID.
select * from sys.databases
Above will list the database name and id.
When you do a detach and attach on a database file, you can if you are unlucky, get a new ID on the database. Then old SSIS job that worked yesterday, will fail.
Repeat the detach and attach process to get the database back to correct number.
Move file locations with ALTER command instead; this script will list the commands for each database
SELECT DB_NAME(database_id) AS [Database] , name AS [LogicalName] , type_desc AS [FileType] , physical_name AS [FilePath] , 'ALTER DATABASE ' + DB_NAME(database_id) + ' MODIFY FILE ( NAME = ' + name + ', FILENAME = ''' + physical_name + '''); 'AS Command FROM SYS.master_files WHERE database_id > 4 ORDER BY database_id ASC, type_desc DESC;
You have to change the path to new location before you run the command.
Also copy the database files to the new location before you run the script.
https://blog.coeo.com/moving-sql-databases
More Information:
https://www.mssqltips.com/sqlservertip/1420/sql-server-system-databases/