Script needed - to create a subdirectory using a database name
-
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?
-
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
-
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
-
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
-
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. -
Hi Vivien Xing,
I am also feeling so happy because the code is working for you
Rajesh Kasturi
-
Thanks MANU for modifying the code to take a backup
Rajesh Kasturi
-
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
-
create the stored procedure; create a SQL scheduled job to exec this stored procedure in the job step.
-
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]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply