List down folders underlying file info using SQL query

Sometimes we need to list down file/folder’s information from a folder like filename, extension, created date and modified date etc.

The below query can give you the list of all content’s info from given folder path.

SQL Query:

DECLARE @FolderPath VARCHAR(MAX) = ''

IF OBJECT_ID('Tempdb..#FileDetails') IS NOT NULL
DROP TABLE #FileDetails

CREATE TABLE #FileDetails (FileDetails VARCHAR(MAX))

INSERT INTO #FileDetails (FileDetails)
EXEC ('EXEC XP_CMDSHELL ''powershell.exe -c "Get-ChildItem '''''+@FolderPath+''''' | SELECT Name,CreationTime,LastWriteTime,Extension|foreach{$_.Name+''''@''''+$_.CreationTime+''''|''''+$_.LastWriteTime+''''#''''+$_.Extension}"''')

SELECT SUBSTRING(FileDetails,0,CHARINDEX('@',FileDetails)) AS [File Name]
 , CONVERT(DATETIME,SUBSTRING(FileDetails,CHARINDEX('@',FileDetails)+1,19)) AS [Creation Date Time]
 , CONVERT(DATETIME,SUBSTRING(FileDetails,CHARINDEX('|',FileDetails)+1,19)) AS [Modified Date Time]
 , ISNULL(NULLIF(SUBSTRING(FileDetails,CHARINDEX('#',FileDetails)+1,LEN(FileDetails)),''),'Directory') AS [Extension]
FROM #FileDetails
WHERE FileDetails IS NOT NULL

Output Content:

  Column Name Description Remarks
File Name Name of content  File name, Folder name etc.
Creation Date Time File/Folder creation date-time
Modified Date Time Last Modified date-time
Extension File extension  like.. .sql, .xlxs etc.

 

#file-created-date, #file-modified-date, #filedetails, #folders-contents, #sql-query, #sql-server

How to recover Tempdb system database step by step

Hello Friends,

We all know Tempdb is very important component of SQL SERVER Instance and there are some incidents where things goes crazy due to Tempdb.

I have some crazy experiences with Tempdb like giving wrong path OR forget to specify extension while shifting Tempdb database or Somehow Tempdb got crashed so the SQL Instance couldn’t able to work and will give  error like “Path is not found for Tempdb database” or “Specified path is not found”.

It’s bit difficult to recover Tempdb back on track in this situation. But using command prompt we can do that.

You can follow the below steps to recover Tempdb database if SQL Instance is not able to bring up because not able to create Tempdb.

Step 01: STOP ALL Services of sql instance.
Step 02: Open cmd and execute below command:
For Default SQL Instance:
       NET START MSSQLSERVER /f /T3608 -- to bypass system db recovery.
For Named Instance:
       NET START MSSQL$<INSTANCE NAME>/f/T3608 

Step 03: Open SQLCMD using below command in cmd prompt:
SQLCMD -S<INSTANCE NAME>
GO

Step 04: Execute below code to change tempdb path in SQLCMD.

ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev' , FILENAME = '<New  Path>.mdf' )
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog' , FILENAME = '<New  Path>.ldf' )
GO

Step 05: Exit from SQLCMD USING EXIT.

Step 06: Stop SQL Service:

NET STOP MSSQLSERVER /f /T3608
OR
NET STOP MSSQL$<INSTANCE NAME>

Step 07: Start services from config manager.

Then Enjoy!!!

Hope this post help someone….

#recover-tempdb, #sql-server, #tempdb, #tempdb-file-path-not-found