Windows batch file to backup SQL Server databases

Question

I am trying to run a script similar to this one (example), but it is not running correctly. The txt file is successfully generated, with the database names row by row, but when you back up the databases, a single large file is generated instead of one file for each database. In my txt contains 30 names, as there are 30 databases.

@echo off

:: Update variable date environment
set today=%date:~0,2%-%date:~3,2%-%date:~6,4%

:: Set folder root of backup files to send to network and to GDRIVE
set BACKUPPATHROOT=%systemdrive%\TMPSQL

:: Create local folder from date
mkdir %BACKUPPATHROOT%\%today% >nul 2>&1

:: Set local folder to save backup files
set BACKUPPATH=%BACKUPPATHROOT%\%today%

:: Set SQL Server location
set SERVERNAME=localhost

:: Get password of backup file compress
set PSWDZIP=123456

:: Build a list of databases to backup
set DBList=%BACKUPPATH%\SQLDBList.txt
SqlCmd -E -S %SERVERNAME% -h-1 -W -Q "SET NoCount ON; SELECT Name FROM sys.databases WHERE [Name] NOT IN ('master','model','msdb','tempdb')" > "%DBList%"

:: Backing UP
For /f "tokens=*" %%i in (%DBList%) do (
    set BACKUPFILENAME=%BACKUPPATH%\%%i.bak
    echo Fazendo backup do banco de dados %%i
    sqlcmd -E -S %SERVERNAME% -Q "BACKUP DATABASE [%%i] TO DISK=N'%BACKUPFILENAME%' WITH NOFORMAT, NOINIT, NOREWIND, NOUNLOAD"
    7za a -r -tzip -bso0 -bsp0 -sdel -p%PSWDZIP% %BACKUPFILENAME%.zip %BACKUPFILENAME%
    echo.
)

Can anyone help me identify the error?

First lines of txt file:

MYDBTEST
MYDBTEST_ADM
DBTEST
DBTEST_ADM
MYDBZBX
MYDBZBX_OLD

PrintScreen

I'm interested to know why you're setting both a temporary and persistent, (using two methods), variable to a value containing today's date. Obviously tomorrow that value tells you nothing other than it is no longer valid as representative of today! — Compo
– Compo, Commented Oct 22, 2019 at 14:49
I agree with you, so it performs updating this variable before following through with the rest of the code. It was the way I found it that works. I made several other attempts that didn't work. — Yuri Prawucki
– Yuri Prawucki, Commented Oct 22, 2019 at 15:02
In addition to that, your Remove old backup files from GDRIVE code uses the incorrect syntax for a batch file and your gdrive commands would certainly benefit from using the --no-header option. I would suggest that you use the recommended syntax for setting variables, Set "VariableName=StringValue", and to doublequote variables which may contain spaces or poison characters. — Compo
– Compo, Commented Oct 22, 2019 at 17:04
You also need to make your mind up about your day numbers; your broken label comment states, :: Get date 10 days ago on GDRIVE, but then you go on to capture a date 11 days ago with set day=-11. I'm aware that is because you're using less than, <, in your GDrive command, and I'm unsure whether it accepts the commonly used less than or equal to, <=, but if it does, that would certainly tidy up your code. Inexplicably however, you then want to :: Delete folder older than 10 days but proceed to use /D -5 in your ForFiles command. — Compo
– Compo, Commented Oct 22, 2019 at 17:22
You also appear to be specifying mounting a drive to Z: via a previously defined variable, but then opt to unmount drive S: at the end of your code. — Compo
– Compo, Commented Oct 22, 2019 at 17:27

Nico JL · Accepted Answer · 2022-06-27 19:27:21Z

0

I have a script to backup all databases in the server, using 7-Zip:

@echo off
set Month=%Date:~3,2%
set Day=%Date:~0,2%
set Year=%Date:~6,4%
for %%f in (C:\respaldos\*.bak) do (
"C:\Program Files\7-Zip\7z.exe" a "c:\respaldos\%Day%-%Month%-%Year%.zip" %%f
 del %%f
)

echo Done!

What I'm doing is make a .zip file and then delete the original file.

answered Jun 27, 2022 at 19:27

Nico JL

4293 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Windows batch file to backup SQL Server databases

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related