Search code examples
sql-serverbackupsqlcmd

Question of SQL backup database to destination folder by bat file

I use a bat file to backup databases to destination folder as below.

@ECHO OFF
SETLOCAL
REM Get date in format YYYY-MM-DD (assumes the locale is the United States)
FOR /F "tokens=1,2,3,4 delims=/ " %%A IN ('Date /T') DO SET NowDate=%%D-%%B-%%C

REM Build a list of databases to backup
SET DBList=D:\SQLDBList.txt
SqlCmd -E -S EvergrandERP2 -h-1 -W -Q "SET NoCount ON; SELECT Name FROM master.dbo.sysDatabases WHERE [Name] NOT IN ('tempdb')" > "%DBList%"

REM Backup each database, prepending the date to the filename
FOR /F "tokens=*" %%I IN (%DBList%) DO (
ECHO Backing up database: %%I
SqlCmd -E -S ERP2 -Q "BACKUP DATABASE [%%I] TO Disk='D:\SQL ERP2_Backup\%%I.bak'"
ECHO.)

REM Clean up the temp file
IF EXIST "%DBList%" DEL /F /Q "%DBList%"

ENDLOCAL

I can successfully backup the database to the folder, but when I run the bat file again, I cannot overwrite backup files.

What can I do to overwrite the old file when I run the .bat file again?

Solution

  • you can modify your backup command to include the WITH INIT option

    REM Backup each database, prepending the date to the filename
FOR /F "tokens=*" %%I IN (%DBList%) DO (
  ECHO Backing up database: %%I
  SqlCmd -E -S ERP2 -Q "BACKUP DATABASE [%%I] TO Disk='D:\SQL ERP2_Backup\%%I.bak' WITH INIT"
  ECHO.
)

    any existing backup file with the same name will be overwritten