Backup Job and Cleanup Task using Windows Batch job

  • Hi Experts

    I want to write a windows batch file, to take backup file and once the backup is taken successfully, want to delete the backup files older than 7 days.

    How to use a IF condition with the backup step that when it is successful, then the next step (delete) will run. Please suggest.

    sqlcmd -S . -Q "EXEC sp_BackupDatabases @backupLocation='D:\Folder\', @backupType='F'"

    REM Remove files backup older than 7 days

    forfiles /p 'D:\Folder\' /s /m *.* /c "cmd /c Del @path" /d -07

    Thanks.

  • SQL-DBA-01 (12/13/2016)


    Hi Experts

    I want to write a windows batch file, to take backup file and once the backup is taken successfully, want to delete the backup files older than 7 days.

    How to use a IF condition with the backup step that when it is successful, then the next step (delete) will run. Please suggest.

    sqlcmd -S . -Q "EXEC sp_BackupDatabases @backupLocation='D:\Folder\', @backupType='F'"

    REM Remove files backup older than 7 days

    forfiles /p 'D:\Folder\' /s /m *.* /c "cmd /c Del @path" /d -07

    Probably better as an SSIS package than a batch file. How will you plan on determining what "success" means?

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • I recommend that you use Ola Hallengren's backup solution. No sense in wasting time writing it yourself when he's already done so for you!

    John

  • The SQL Server is Express Edition...so I need a simple solution as I cant schedule the jobs to run through Agent.

    Thanks.

  • You could run the job on a server that does have SQL Server Agent, I suppose. (Your job, that is, not Ola's.)

    John

  • Use Powershell

    Its a good idea to return result to a variable.

    You can also use xp_cmdshell and make a single sp to do both

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • SQL-DBA-01 (12/14/2016)


    The SQL Server is Express Edition...so I need a simple solution as I cant schedule the jobs to run through Agent.

    Ola Hallengren's solution is the simple and tested solution.

    (Well simple for us, not Ola Hallengren.)

    Just download the MaintenanceSolution script and when setting the variables before running it stop the agent jobs from being created.

    You can then create a batch file like the following to put in the Task Scheduler.

    (You should add errorlevel checks and possibly direct the output of sqlcmd to log files which would then need to be cleared up.)

    rem CheckDb

    sqlcmd -E -S . -d master -Q "EXECUTE [dbo].[DatabaseIntegrityCheck] @databases = 'ALL_DATABASES', @LogToTable = 'Y'" -b

    rem Update Stats

    sqlcmd -E -S . -d master -Q "EXECUTE [dbo].[IndexOptimize] @databases = 'ALL_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = NULL, @FragmentationHigh = NULL, @UpdateStatistics = 'ALL', @OnlyModifiedStatistics = N'Y', @LogToTable = 'Y'" -b

    rem Full Backup

    sqlcmd -E -S . -d master -Q "EXECUTE [dbo].[DatabaseBackup] @databases = 'ALL_DATABASES', @Directory = N'C:\SQLBackups\', @BackupType = 'FULL', @verify = 'Y', @CleanupTime = 168, @checksum = 'Y', @LogToTable = 'Y'" -b

    rem Command Log Cleanup

    sqlcmd -E -S . -d master -Q "DELETE FROM [dbo].[CommandLog] WHERE StartTime < DATEADD(dd,-30,GETDATE())" -b

    rem Delete Backup History

    sqlcmd -E -S . -d msdb -Q "DECLARE @CleanupDate datetime SET @CleanupDate = DATEADD(dd,-30,GETDATE()) EXECUTE dbo.sp_delete_backuphistory @oldest_date = @CleanupDate" -b

  • In my case, I dont have the agents,,as the SQL Servers are in express edition. Those do not have agents.

    So I wrote my own custom backup code and want to take backup with a cleanup logic in it.

    Can you suggest how to use my existing script to meet the requirement?

    Thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

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