0

I am using SQL Server Express and I have a stored procedure for taking backups which I took from Microsoft: How to schedule and automate backups of SQL Server

Then I use Windows Task Scheduler for taking the backups.

I have a task running on 2 AM which takes a full backup using command:

sqlcmd -S SERVER\MSSQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='C:\Backups\SQL\TEMP\', @backupType='F'"

Then from 2.30 AM I take a differential backup every hour using command:

sqlcmd -S SERVER\MSSQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='C:\Backups\SQL\TEMP\', @backupType='D'"

Now here I have a problem, the full backup works but after that I see no new added backup files in my folder. When I open the server I see a command prompt from the differential backup running asking to overwrite the FULL backup files. I click A from ALL and then it works. The hour after again, a command prompt to overwrite previous DIFF and FULL backup files.

How can I set this to always be ALL or how can I disable this?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mivaweb
  • 5,580
  • 3
  • 27
  • 53
  • Looking at that script it generates a unique filename per-backup and uses WITH INIT which allows overwrite so something else is wrong. No T-SQL will prompt you for anything, its not interactive, what is actually prompting you to overwrite? What happens if you run the command manually outside of the Task Scheduler? – Alex K. Dec 09 '17 at 18:13
  • Ok I have found the problem. After every DIFF backup I have a xcopy command which copies all the files to another location. This was causing the command prompt to ask if files needs to be overwritten. My mistake! – Mivaweb Dec 09 '17 at 18:17

0 Answers0