April 29, 2003 at 4:47 am
Hello All,
I have written a database backup stored procedure which accepts a database name as input and works fine. This will be scheduled later in the process.
My requirement is to create a new folder for each day, so that for example when i check the backup tomorrow morning, it should be residing in the path 'D:\MSSQL\BACKUP\29APR2003\Test_Apr 29 2003.DAT'. The daywise folder should be created thru the stored procedure.
Can anybody help me out in this area.
Ravi
Lucky
April 29, 2003 at 5:32 am
Within the stored procedure, you could use xp_cmdshell (with an appropriate parameter) to issue the mkdir command to create the new directory.
Jeremy.
April 29, 2003 at 5:56 am
Hello Jeremy,
Thanks for the help. But i want the creation to be dynamic ie., the date should be taken automatically while creating the folder in "D:\MSSQL\BACKUP\DATE\FILENAME.DAT".
We are having the GETDATE() function, from which the date is to be taken and a folder is to be created dynamically.
With the xp_cmdshell, i have to specify the folder name to be created.
Ravi
Lucky
April 29, 2003 at 6:19 am
declare @datevar varchar(10), @command_string varchar(255)
/* Get current date in yyyymmdd format*/
set @datevar = convert(varchar,getdate(),112)
set @command_string = 'mkdir D:\MSSQL\BACKUP\' + @datevar
/* Create directory */
exec master..xp_cmdshell @command_string
If you want the date in a particular format which CONVERT does not give you then you can use various date functions (datepart, datename, year, month, day etc) to give you the exact format.
You could do something similar to create a specific backup command as well if you need to.
Jeremy
April 29, 2003 at 6:24 am
You can use sql similar to the following to create a directory dynamically.
DECLARE @m_date VARCHAR(255)
DECLARE @m_cmd VARCHAR(255)
SET @m_date = CAST(DAY(GETDATE()) AS VARCHAR) + DATENAME(m,GETDATE())+ CAST(YEAR(GETDATE()) AS VARCHAR)
SET @m_cmd = "D:\MSSQL\BACKUP\" + @m_date
execute xp_cmdshell @m_cmd
April 29, 2003 at 6:27 am
Sorry. Jeremy has already posted the aswer. Mine was a repeat (and an error as well missing mkdir). Please ignore,
April 29, 2003 at 6:30 am
Rajesh,
No problems - at least we came up with essentially the same answer. Great minds think alike or Fools seldom differ?
Jeremy
April 29, 2003 at 6:44 am
Hello Jeremy & Rajesh,
Thank you very much for your suggestions.
Ravi
Lucky
April 29, 2003 at 11:28 pm
Hello Ravi, Jeremy & Rajesh
I have gone through the discussion between you 3 people. The topic is very nice. But how does the path be changed dynamically after it has been created through the xp_cmdshell and the respective mkdir command.
Normally we change the directory as "cd <directoryname>. In the above case, how this will be taken care off. Another thing if a procedure is to be written for differential and log backups which will be job scheuled, how the above task will be fruitful as these will be taking more than 1 backup for a single day which will be over written as the same path strategy should be applied.
For example a procedure is written for Full, Diff & Log backups. As per Mr. Ravi, a date folder should be created ie., 20030430. In that the full backup will be saved. Later the differential & log backups also should be written to the same folder. As the folder is already created in the Full backup case, it has to be checked in the diff & log procedures and also the backup(s) file names should differ from each other.
Just for knowledge sake, i wanted to know.
April 30, 2003 at 6:15 am
I understand your problem of differential backup issues. I did implement this in one of the projects.
At the start of the day a full database back up is stored as explained earlier with DDMMYYYY format.
For the remaining part of day, differential backup is stored in the same directory. The time infomration is
used to build the file name for this for example ( REPLACE(CONVERT(VARCHAR,GETDATE(),108),':','')+ '.bak').
Regarding your query on changing directory, I cannot see the reason why you might need it, you can
always use the full path for filenames in all commands(hence there will not be a need for CD)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply