Thursday, September 8, 2016

Check and Create a Folder Using T-SQL Code

> Check if Folder Exist or Not in our Desired Location Using System Stored Procedure
"master.dbo.xp_fileexist" And if not exists create Directoty Using Below T-SQL Code:

declare @chkdirectory as nvarchar(4000)
    declare @folder_exists as int     
    set @chkdirectory = 'C:\SQLDBPool\SQL\Articles' 
    declare @file_results table
    (file_exists int,
    file_is_a_directory int,
    parent_directory_exists int
    ) 
    insert into @file_results
    (file_exists, file_is_a_directory, parent_directory_exists)
    exec master.dbo.xp_fileexist @chkdirectory
     
    select @folder_exists = file_is_a_directory
    from @file_results     
    --script to create directory       
    if @folder_exists = 0
     begin
        print 'Directory is not exists, creating new one'
        EXECUTE master.dbo.xp_create_subdir @chkdirectory
        print @chkdirectory +  'created on' + @@servername
     end       
    else
    print 'Directory already exists'
GO

Dynamically Create a Folder for Each Database In Server

I Need to Create Folder For Each Database in a particular server.I like to have a specific folder structure to hold the backup files for each database.  See below:

                       D:\Backup\Server\Instance\BabjiDB
                     D:\Backup\Server\Instance\NaveenDB
                     D:\Backup\Server\Instance\JaipalDB

So we need to maintain each DB backup in separate folder,Check Below T-SQL code 


DECLARE
       @BaseFolder nvarchar(128)
      ,@DatabaseFolder nvarchar(256)
      ,@DbName sysname
     
-- Only one input parameter needed from the user.
SET @BaseFolder = 'D:\Backup';

DECLARE Db CURSOR FOR
SELECT name FROM sys.databases WHERE name <> 'tempdb';

OPEN Db;
FETCH NEXT FROM Db INTO @DbName;
WHILE @@FETCH_STATUS = 0
BEGIN
      -- Dynamically create a folder for each database.
      SET @DatabaseFolder = @BaseFolder + '\'
      + CONVERT(nvarchar,SERVERPROPERTY('MachineName')) + '\'
      + ISNULL(CONVERT(nvarchar,SERVERPROPERTY('InstanceName')),'DEFAULT')
      + '\' + @DbName;
      EXEC master.sys.xp_create_subdir @DatabaseFolder;
      FETCH NEXT FROM Db INTO @DbName;
END

-- Clean up
CLOSE Db;
DEALLOCATE Db;
GO


HOW TO CREATE WINDOWS USING T-SQL Script

1. By using system stored procedure "master.sys.xp_create_subdir" to create  windows directories(Folders).see below tsql code to create folder 


  DECLARE @foldername VARCHAR(100)
  SET @foldername='c:\createfromsql'
  EXEC master.sys.xp_create_subdir @foldername