0

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.

7
  • what do you mean by directory? Is it specific drive folder or else.? Jan 22 '17 at 13:00
  • If you want to see the all (.MDF & .LDF) files in particular SQL instance then through this query you shall know all (.MDF & .LDF) files (select * from master.sys.master_files;) Jan 22 '17 at 13:30
  • Hi @MdHaidarAliKhan directory is specific folder for example d:\sql\data. Regarding select * from master.sys.master_files; this will show all files attached to the instance, what I need is the opposite, since I have more than 200 MDF file, inside the directory Jan 22 '17 at 13:58
  • 2
    Why are you doing it this way? It is so brittle: blogs.sentryone.com/aaronbertrand/bad-habits-file-backups Jan 22 '17 at 14:38
  • 2
    How on earth is it 10 times faster? If you back up a database, it is not down. And you can log ship those databases so that all that happens at cutover time is repointing, instead of detaching, copying, and re-attaching (how do you do that for a 3TB database?). Also faster than what? If you're already taking backups, nothing is faster than using a backup you've already taken. If you're not already taking backups, then never mind, risk is obviously not on your radar. Detach / attach is just so dangerous, it's like I can get to the store faster if I don't bother with that pesky seatbelt. Jan 22 '17 at 15:08
6

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.

1

I found answer to my question, the following script will list all MDF and LDF is not attached to the instance on all the server (Note: I do NOT claim ownership of this script, had to do small modification since its a very old script)

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

Now lets get the result by using select command

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

Finally after my work is completed I need to remove the temp tables

drop table #Files
drop table #Drives
1

Here is a solution that does not require xp_cmdshell.

We are not permitted to enable xp_cmdshell for security reasons. This script checks each directory where SQL currently has database files and checks for orphans in those directories.

--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;
0
how to check all MDF files that is not attached to SQL server from specific directory.

If You want to see all (.mdf) details in specific directory then you have to enabled the '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

Suppose that you want to see all (.mdf) files , which are in 'D:\' then execute this query.

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

It will give all (.mdf) files which are attached in your specific SQL Server instance.

2
  • Thanks, this is a normal list, I want to show files not being used by SQL Server Jan 22 '17 at 14:04
  • 1
    Or you could do it outside of SQL Server. Jan 22 '17 at 15:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.