Backup sp_procedure

  • 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!!!

  • 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