December 13, 2016 at 9:15 am
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.
December 14, 2016 at 8:43 am
SQL-DBA-01 (12/13/2016)
Hi ExpertsI 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)
December 14, 2016 at 8:57 am
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
December 14, 2016 at 9:06 am
The SQL Server is Express Edition...so I need a simple solution as I cant schedule the jobs to run through Agent.
Thanks.
December 14, 2016 at 9:13 am
You could run the job on a server that does have SQL Server Agent, I suppose. (Your job, that is, not Ola's.)
John
December 14, 2016 at 9:31 am
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
December 14, 2016 at 11:07 am
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
December 14, 2016 at 12:39 pm
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