Listar todos os bancos de dados não anexados ao servidor SQL

Estou movendo bancos de dados de uma instância para outra

Você deve usar o powershell para automação e restauração de backup para uma maneira garantida de migrar seus bancos de dados.

Para powershell, use dbatools --> COPY-SQLDATABASEou para migrar todo o servidor com logins, trabalhos etc.Start-SqlMigration

desanexando os bancos de dados da primeira instância, mova os arquivos MDF e de log para outro local e anexe-os à nova instância

Por que não restaurar o backup? desanexar e anexar arquivos de banco de dados para mover de um servidor para outro não é uma abordagem recomendada !

Listar todos os bancos de dados não anexados ao servidor SQL

Você pode usar Find-DbaOrphanedFilepara descobrir arquivos de banco de dados órfãos .mdf, .ldf and .ndf files.

Encontrei a resposta para minha pergunta, o script a seguir listará todo o MDF e o LDF não está anexado à instância em todo o servidor (Nota: NÃO reivindico a propriedade deste script, tive que fazer pequenas modificações, pois é um script muito antigo)

-- THIS CODE COMPARES THE DATABASE FILES ON THE DISKS WITH SYSALTFILES
-- TO DETERMINE WHICH OF THOSE FILES AREN'T USED BY THE INSTANCE
-- IF YOU'RE AWAKE YOU'D NOTICE THAT THE ONE FLAW IS THAT IF THE BOX CONTAINS > 1 INSTANCE,
--   IT DOESN'T COMPARE THE DATABASE FILES ON THE DISKS TO ALL INSTANCES,
--   BUT IT ONLY COMPARES AGAINST THE CURRENT INSTANCE
-- THOUGH YOU CAN RUN THIS AGAINST A BOX WITH MULTIPLE INSTANCES, THE RESULTS WILL BE SKEWED
-- BY THE FACT THAT WE ARE ONLY SAYING "WHICH FILES HAS THE DISKS GOT THAT ISN'T IN A PARTICULAR SQL INSTANCE"
-- IDEALLY WE'D LIKE TO SAY "WHICH FILES HAS THE DISKS GOT THAT AREN'T USED BY ANY SQL INSTANCE"
-- STILL IT IS HANDY ON SOME SERVERS

-- PREREQUISITE: YOUR INSTANCE MUST HAVE XP_CMDSHELL MUST BE ENABLED
-- IF IT IS SQL2000, XP_CMDSHELL IS INHERRINTLY ENABLED BY DEFAULT
-- AND IS NOT EVEN LISTED IN THE SP_CONFIGURE OPTIONS
set nocount on
DECLARE @sqlversion sql_variant
SELECT @sqlversion = SERVERPROPERTY('productversion')
IF LEFT(CONVERT(VARCHAR(255),@SQLVERSION),2) <> '8.'
  BEGIN
    -- START CHECKING SP_CONFIGURE FOR XP_CMDSHELL OPTION  --

                CREATE TABLE #xp_cmdshell (OptionName varchar(255), minval int, maxval int, configval int, runval int)
                INSERT INTO #xp_cmdshell
                EXEC master..sp_configure
                declare @runval int
                select @runval = runval from #xp_cmdshell where OptionName = 'xp_cmdshell'
                drop table #xp_cmdshell
                if @runval is null
                  begin
                                RAISERROR ('enable "show advanced options" before you run this code', -- Message text.
                                                   10, -- Severity,
                                                   16 -- State,
                                                   )
                  end     

                if @runval = 1
                  begin
                                print '' -- The pre-requisites are enabled, so we can continue
                  end
                else -- IF xp_cmdshell is NOT enabled
                  begin
                                RAISERROR ('enable xp_cmdshell before you run this code', -- Message text.
                                                   10, -- Severity,
                                                   16 -- State,
                                                   )
                  end
    -- FINISH CHECKING SP_CONFIGURE FOR XP_CMDSHELL OPTION --
  END

-- OBTAIN A LIST OF ALL THE DRIVES ON THE SERVER
CREATE TABLE #Drives (DriveLetter char(1), MBFree int)
INSERT INTO #Drives
EXEC master..xp_fixeddrives

-- DECLARE VARIABLES
DECLARE @CurrentDriveLetter CHAR(1), @MaxDriveLetter CHAR(1), @EXECSTR varchar(1024)

-- FIND THE FIRST AND LAST DRIVES FOR THE LOOP
SELECT @CurrentDriveLetter = Min(DriveLetter), @MaxDriveLetter = Max(DriveLetter) from #Drives

-- CREATE THE TABLE TO HOST THE LIST OF FILES
CREATE TABLE #Files (autono_id int NOT NULL IDENTITY (1, 1), RawData varchar(255), FilePath varchar(255), DriveLetter CHAR(1), [FileName] varchar(255), FileSize varchar(17), FileSizeInMB decimal(18,2), FileSizeInGB decimal(18,2))

WHILE @CurrentDriveLetter <= @MaxDriveLetter
BEGIN
      -- STORE THE FILES WE ARE LOOKING FOR IN THE #FILES TABLE
    -- PRINT STR('dir ' + STR(@CurrentDriveLetter) + ':\*.mdf;*.ndf;*.ldf /s')
      SELECT @EXECSTR = 'dir ' + CONVERT(VARCHAR(1),@CurrentDriveLetter) + ':\*.mdf;' + CONVERT(VARCHAR(1),@CurrentDriveLetter) + ':\*.ndf;' + CONVERT(VARCHAR(1),@CurrentDriveLetter) + ':\*.ldf;' + CONVERT(VARCHAR(1),@CurrentDriveLetter) + ':\*.ubak;' + CONVERT(VARCHAR(1),@CurrentDriveLetter) + ':\*.BAK /s' -- string in the drive letter later
      INSERT INTO #Files (RawData)
      EXEC master..xp_cmdshell @EXECSTR
    -- PRINT @EXECSTR
      select @CurrentDriveLetter = MIN(DriveLetter) from #Drives where DriveLetter > @CurrentDriveLetter
END

-- CLEAN UP #FILES
update #Files
   set FilePath = REPLACE(RawData,'Directory of ','')
 where RawData like '%Directory of %:%'

update #Files
   set FilePath = SubString(FilePath, 2, 255)
 where FilePath is not null

delete from #Files
 where RawData is NULL
      or RawData = 'File Not Found'
      or RawData like '%Volume%'
      or RawData like '%File(s)%'
      or RawData like '%Dir(s)%'
      or RawData like '%Total Files Listed:%'

update #Files set [FileName] = substring (RawData, 40, 255) where FilePath is NULL
update #Files set FileSize = substring (RawData, 22, 17) where FilePath is NULL
update #Files set FileSize = replace(substring (RawData, 22, 17),',','') where FilePath is NULL
update #Files set DriveLetter = substring(FilePath, 1, 1) where FilePath is not NULL
update #Files
   set FileSizeInMB = CONVERT(decimal(18,2), FileSize) / 1024 / 1024,
         FileSizeInGB = CONVERT(decimal(18,2), FileSize) / 1024 / 1024 / 1024

DECLARE @autono_id int, @fp varchar(255), @drive char(1)

select top 1 @autono_id = autono_id, @fp = [FilePath], @drive = DriveLetter
  from #files F1
 where FilePath is not null
   and autono_id < (select max(autono_id) from #Files where FilePath is NULL)
 order by autono_id desc

WHILE @autono_id IS NOT NULL
BEGIN

      update #Files
         set [FilePath] = @fp, DriveLetter = @Drive
       where autono_id > @autono_id and [FilePath] is NULL

      DELETE from #Files where [FileName] is null AND DriveLetter = @Drive AND autono_id > @autono_id

      SELECT @autono_id = NULL, @fp = NULL, @drive = NULL -- RESET FLAGS

      select top 1 @autono_id = autono_id, @fp = [FilePath], @drive = DriveLetter
        from #files F1
       where FilePath is not null
         and autono_id < (select max(autono_id) from #Files where FilePath is NULL)
       order by autono_id desc

END

Agora vamos obter o resultado usando o comando select

select 'Drive' = Ltrim(rtrim(LEFT(DriveLetter,1))),
       'FileName' = REPLACE(FilePath + '\' + [FileName], ':\\', ':\'), FileSizeInMB, FileSizeInGB
  from #Files
 where REPLACE(FilePath + '\' + [FileName], ':\\', ':\') not in (select Ltrim(rtrim(filename)) from master.dbo.sysaltfiles)
   and right(FileName,3) <> 'bak' -- EXCLUDE .BAK/.UBAK FILES
   and FileSizeInMB > 0 -- YOU CAN STIPULATE YOU ARE LOOKING FOR FILES LARGER THAN X MB
 order by 3 desc

Finalmente, depois que meu trabalho estiver concluído, preciso remover as tabelas temporárias

drop table #Files
drop table #Drives

Aqui está uma solução que não requer xp_cmdshell.

Não temos permissão para habilitar o xp_cmdshell por motivos de segurança. Este script verifica cada diretório onde o SQL possui arquivos de banco de dados e verifica se há órfãos nesses diretórios.

--Create temp tables to hold the results.
IF OBJECT_ID('tempdb..#DirectoryList') IS NOT NULL
      DROP TABLE #DirectoryList;

CREATE TABLE #DirectoryList (
       id int IDENTITY(1,1)
      ,fullpath varchar(2000));

IF OBJECT_ID('tempdb..#FileList') IS NOT NULL
      DROP TABLE #FileList;

CREATE TABLE #FileList (
       id int IDENTITY(1,1)
      ,fullpath varchar(2000)
      ,subdirectory nvarchar(512)
      ,depth int
      ,isfile bit);


-- Populate a list of directories to check based on the ones used by databases
INSERT INTO #DirectoryList
SELECT DISTINCT LEFT(physical_name,LEN(physical_name) - CHARINDEX('\', REVERSE(physical_name)))
FROM sys.master_files

-- Declare Variables 
DECLARE @currentfolder Varchar(2000)

DECLARE folder_cursor CURSOR FAST_FORWARD READ_ONLY FOR 
SELECT fullpath FROM #DirectoryList

OPEN folder_cursor

FETCH NEXT FROM folder_cursor INTO @currentfolder

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Populate File List for each directory
    INSERT INTO #FileList (subdirectory, depth, isfile)
    EXEC master.sys.xp_dirtree @currentfolder,1,1
    UPDATE #FileList
            SET fullpath = @currentfolder
            WHERE fullpath IS NULL;

    FETCH NEXT FROM folder_cursor INTO @currentfolder
END

CLOSE folder_cursor
DEALLOCATE folder_cursor

-- Now we have a list of directories in #DirectoryList
-- and a list of files in #FileList
--SELECT * FROM #DirectoryList
--SELECT * FROM #FileList

-- Delete non database file rows from #FileList
DELETE FROM #FileList
WHERE UPPER(subdirectory) NOT LIKE '%.MDF'
AND UPPER(subdirectory) NOT LIKE '%.NDF'
AND UPPER(subdirectory) NOT LIKE '%.LDF'

-- List Database Files that are NOT Orphans
SELECT 'All Files'
SELECT sdb.name AS [Database], fl.fullpath AS [Path], fl.subdirectory AS [File] 
FROM #FileList fl
JOIN sys.master_files smf
ON fl.fullpath + '\' + fl.subdirectory = smf.physical_name
JOIN sys.databases sdb
ON smf.database_id = sdb.database_id
ORDER BY sdb.name


-- List Orphaned Files
SELECT 'Orphaned Files'
SELECT fl.fullpath AS [OrphanPath], fl.subdirectory AS [OrphanFile]  FROM #FileList fl
LEFT OUTER JOIN sys.master_files smf
ON fl.fullpath + '\' + fl.subdirectory = smf.physical_name
WHERE smf.database_id IS NULL
ORDER BY fl.subdirectory

-- Cleanup Temp Tables
IF OBJECT_ID('tempdb..#DirectoryList') IS NOT NULL
      DROP TABLE #DirectoryList;

IF OBJECT_ID('tempdb..#FileList') IS NOT NULL
      DROP TABLE #FileList;

how to check all MDF files that is not attached to SQL server from specific directory.

Se você quiser ver todos os detalhes (.mdf) em um diretório específico, então você deve habilitar o 'xp_cmdshell'.

---- enable these jobs 
---- show advanced options
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

---- enable xp_cmdshell
sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE;
GO

---- To Disable advanced options
sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO 

  --- To Disable 'Xp_cmdshell'
 Exec sys.sp_configure 'xp_cmdshell',0;
 GO
 RECONFIGURE
 GO

Suponha que você queira ver todos os arquivos (.mdf) , que estão em 'D:\' e então execute esta consulta.

exec xp_cmdshell 'dir D:\*.mdf /s/b';
Go

Ele fornecerá todos os arquivos (.mdf) anexados em sua instância específica do SQL Server.