列出所有未附加到 SQL Server 的数据库

我正在将数据库从一个实例移动到另一个实例

您应该使用 powershell 进行自动化和备份还原，以确保迁移数据库。

对于 powershell，使用 dbatools -->COPY-SQLDATABASE或使用登录、作业等迁移整个服务器Start-SqlMigration

通过从第一个实例中分离数据库，将 MDF 和日志文件移动到另一个位置，并将它们附加到新实例

为什么不备份还原？分离和附加数据库文件以从一台服务器移动到另一台服务器不是推荐的方法！

列出所有未附加到 SQL Server 的数据库

您可以使用Find-DbaOrphanedFile找出孤立的数据库文件.mdf, .ldf and .ndf files。

我找到了我的问题的答案，以下脚本将列出所有 MDF 和 LDF 未附加到所有服务器上的实例（注意：我不声明此脚本的所有权，因为它是一个非常旧的脚本，所以必须进行小修改）

-- 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

现在让我们使用 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

最后，在我的工作完成后，我需要删除临时表

drop table #Files
drop table #Drives

这是一个不需要 xp_cmdshell 的解决方案。

出于安全原因，我们不允许启用 xp_cmdshell。此脚本检查 SQL 当前具有数据库文件的每个目录，并检查这些目录中的孤儿。

--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.

如果您想查看特定目录中的所有 (.mdf) 详细信息，则必须启用“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

假设您想查看位于“D:\”中的所有 (.mdf) 文件，然后执行此查询。

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

它将提供附加在您的特定 SQL Server 实例中的所有 (.mdf) 文件。