Sql-server – List all databases not attached to SQL server

sql serversql-server-2016

I am moving databases from one instance to another (by detaching databases from the first instance, move MDF and log files to another location, and attach them to the new instance), unfortunately could not attach some of them, and miss calculate number of databases that have issues, how to check all MDF files that is not attached to SQL server from specific directory.

Best Answer

I am moving databases from one instance to another

You should use powershell for automation and backup restore for a guranteed way of migrating your databases.

For powershell, use dbatools --> COPY-SQLDATABASE or for migrating entire server with logins, jobs etc use Start-SqlMigration

by detaching databases from the first instance, move MDF and log files to another location, and attach them to the new instance

Why not backup restore ? detaching and attaching database files for moving from one server to another is not a recommended approach !

List all databases not attached to SQL server

You can use Find-DbaOrphanedFile to find out orphaned database files .mdf, .ldf and .ndf files.