Script needed - to create a subdirectory using a database name

Question

Script needed - to create a subdirectory using a database name

Vivien Xing

SSChampion

Points: 12297
More actions
September 24, 2008 at 8:55 pm

#70825

I want to schedule a job to create subdirectories named by the user database names under K:\myfile. If subdirectories already exist, no need to create, if new database name, then create the subdirectory.
Does anyone know how to do it?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Rajesh kasturi SSCarpal Tunnel Points: 4007 More actions · Answer 1

Hi,

I have written stored procedure 'create_DBFolders ' for you. This will do your work. Just create the sp and schedule this sp by creating new job.

CREATE PROCEDURE create_DBFolders

AS

BEGIN

--Declare Variables

DECLARE @DBname VARCHAR(300)

DECLARE @Path VARCHAR(300)

--Get Directory Names

CREATE TABLE #DIR_NAMES ( DIRNAME VARCHAR(300))

INSERT INTO #DIR_NAMES exec xp_cmdshell 'DIR /B C:\MYFILE\'

--Get only User Database Names

CREATE TABLE #DB_NAMES (DBNAME VARCHAR(300))

INSERT INTO #DB_NAMES SELECT name FROM sys.databases where database_id >4

--Get DBnames which does not have folder till now i.e

DECLARE cur_DBname CURSOR FOR

SELECT DBNAME FROM #DB_NAMES WHERE DBNAME NOT IN (SELECT DIRNAME FROM #DIR_NAMES WHERE DIRNAME <>'NULL')

OPEN cur_DBname

FETCH NEXT FROM cur_DBname INTO @DBname

--Create one directory for each database.

WHILE @@FETCH_STATUS = 0

BEGIN

SET @Path = 'MD C:\myfile\'+@DBname

exec XP_CMDSHELL @Path

FETCH NEXT FROM cur_DBname INTO @DBname

END

DROP TABLE #DIR_NAMES

DROP TABLE #DB_NAMES

CLOSE cur_DBname

DEALLOCATE cur_DBname

END

Please let me know if you have any questions on this.

Have a Great Day

Rajesh Kasturi

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 2

Addition to what rajesh provided you:

Alter PROCEDURE create_DBFolders

AS

BEGIN

--Declare Variables

DECLARE @DBname VARCHAR(300)

DECLARE @Path VARCHAR(300)

DECLARE @backup VARCHAR(300)

--Get Directory Names

CREATE TABLE #DIR_NAMES ( DIRNAME VARCHAR(300))

INSERT INTO #DIR_NAMES exec xp_cmdshell 'DIR /B C:\MYFILE\'

--Get only User Database Names

CREATE TABLE #DB_NAMES (DBNAME VARCHAR(300))

INSERT INTO #DB_NAMES SELECT name FROM sys.databases where database_id >4

--Get DBnames which does not have folder till now i.e

DECLARE cur_DBname CURSOR FOR

SELECT DBNAME FROM #DB_NAMES WHERE DBNAME NOT IN (SELECT DIRNAME FROM #DIR_NAMES WHERE DIRNAME <>'NULL')

OPEN cur_DBname

FETCH NEXT FROM cur_DBname INTO @DBname

--Create one directory for each database.

WHILE @@FETCH_STATUS = 0

BEGIN

SET @Path = 'MD C:\myfile\'+@DBname

exec XP_CMDSHELL @Path

FETCH NEXT FROM cur_DBname INTO @DBname

END

DROP TABLE #DIR_NAMES

CLOSE cur_DBname

DEALLOCATE cur_DBname

DECLARE cur_DBnamebackup CURSOR FOR

SELECT DBNAME FROM #DB_NAMES

OPEN cur_DBnamebackup

FETCH NEXT FROM cur_DBnamebackup INTO @DBname

--Create one directory for each database.

WHILE @@FETCH_STATUS = 0

BEGIN

Set @backup='Backup Database '+@DBname+' To disk='+''''+'C:\myfile\'+@DBname+'\'+@DBname+'.bak'+''''+' With INIT'

Print (@backup)

Exec (@backup)

FETCH NEXT FROM cur_DBnamebackup INTO @DBname

END

DROP TABLE #DB_NAMES

CLOSE cur_DBnamebackup

DEALLOCATE cur_DBnamebackup

END

The above code will take backup too.

Thanks Rajesh.

HTH

MJ

Vivien Xing SSChampion Points: 12297 More actions · Answer 3

Thank you. This is exactly what I want.

I like this part very much, plus your have comments in your code.

--Get Directory Names

CREATE TABLE #DIR_NAMES ( DIRNAME VARCHAR(300))

INSERT INTO #DIR_NAMES exec xp_cmdshell 'DIR /B C:\MYFILE\'

one minor change: For SQL2000,

INSERT INTO #DB_NAMES SELECT name FROM sys.databases where database_id >4

should be:

INSERT INTO #DB_NAMES SELECT name FROM sysdatabases where dbid >4

Vivien Xing SSChampion Points: 12297 More actions · Answer 4

I felt embarrassing to ask code from the others for this question. I thought or the others would think I am a lazy DBA 😉 not to write the code by myself. Anyway, I was too busy to work on this. The good thing is there is always someone willing to share and help. Plus, I learned different coding style from the others.

Rajesh kasturi SSCarpal Tunnel Points: 4007 More actions · Answer 5

Hi Vivien Xing,

I am also feeling so happy because the code is working for you 🙂

Rajesh Kasturi

Rajesh kasturi SSCarpal Tunnel Points: 4007 More actions · Answer 6

Thanks MANU for modifying the code to take a backup 🙂

Rajesh Kasturi

J-983369 Mr or Mrs. 500 Points: 535 More actions · Answer 7

Hi,

Is this similar to ddl triggers? I'm new to sql server dba and coding is not my strongest part. I am looking for a script that will create backup devices and jobs for me when a new db is created. I have no control over this as an application creates the db. Any assistance would be greatly appreciated.

Thanks

J

Vivien Xing SSChampion Points: 12297 More actions · Answer 8

create the stored procedure; create a SQL scheduled job to exec this stored procedure in the job step.

Robert Davis One Orange Chip Points: 28027 More actions · Answer 9

Just a side note, but starting with SQL 2005, there is a system procedure that will do this for you: xp_create_subdir

Yet another reason to consider upgrading. 😀

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]