June 26, 2008 at 12:08 am
Hi All
Can someone help me to modify the below sp to backup specific directory
CREATE PROCEDURE usp_ZipBackups
@BACKUPPATH VARCHAR(300),
@EXT VARCHAR(5) = '*.BAK'
AS
SET NOCOUNT ON
DECLARE @GZIP VARCHAR(8),
@CMD VARCHAR(300),
@DIR VARCHAR(100)
CREATE TABLE #dir (dir varchar(100))
SELECT @GZIP = 'gzip.exe'
SELECT @CMD = 'dir /b '+@BACKUPPATH
INSERT INTO #dir
EXEC master.dbo.xp_cmdshell @CMD
SELECT @CMD = @GZIP + ' ' + @BACKUPPATH + @EXT
EXEC master.dbo.xp_cmdshell @CMD, NO_OUTPUT
DELETE FROM #dir WHERE dir IS NULL
SELECT @DIR = MIN(dir) FROM #dir
WHILE (@DIR IS NOT NULL)
BEGIN
SELECT @CMD = @GZIP + ' ' + @BACKUPPATH + @DIR + '\' + @EXT
EXEC master.dbo.xp_cmdshell @CMD, NO_OUTPUT
SELECT @DIR = MIN(dir) FROM #dir WHERE dir > @DIR
END
DROP TABLE #dir
Thanks
New DBA
It's better to fail while trying, rather than fail without trying!!!
June 26, 2008 at 9:09 am
I'm a little confused. Backup the directory to where? Zip?
It looks like you want to zip the contents of a dir. You would need to create a stored procedure that builds a command simmilar to this:
exec xp_cmdshell 'gzip c:\backuppath\file.BAK
Step away from the keyboard 🙂 the code you posted runs the gzip command and then uses the directory contents from before the command to step through yet another gzip command? Not sure if that's what you are looking to do.
Is this what you need to do?
Psudo code first:
SP must accept variables of backup path and extension and set the default extension to .bak
1. get directory listing of files into temp table.
2. loop through the temp table to build and run each zip command using the path specified above.
3. drop the temp table.
Sorry if I'm over simplifying, but it helps when searching for snipits of code to accomplish your goals.
If I'm way off let me know.
Thanks
_______________________________________________________________________
Work smarter not harder.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply