0

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

Yuri Prawucki
  • 1
  • 2
  • 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 Oct 22 '19 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 Oct 22 '19 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 Oct 22 '19 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 Oct 22 '19 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 Oct 22 '19 at 17:27
  • Right, I made some mistakes. In Get date 10 days ago on GDRIVE I put as -11 justification for the sign < In Delete folder older than 10 days I forgot to change the comment, but it's 5 anyway. But my biggest problem is backing up the databases. I think it's something in the FOR loop, because it does not generate a backup file for each bank but a single large file for all. – Yuri Prawucki Oct 22 '19 at 18:21
  • The point I was making with regards the 11 days was that you should leave it as 10 to match the comment, then use `<=` in your `GDrive` command. The biggest issue however is that you're supposed to ask one question and post a [MCVE] of the code to allow us to replicate it. Instead you've posted code which exhibits many issues outside of the scope of the specific issue, and hence made your question too broad and therefore off topic. I have therefore as a courtesy, removed all unnecessary code from your question in order that a minimal example remains and your issue is more readily dealt with. – Compo Oct 22 '19 at 19:35
  • In order to assist us further however, I'd appreciate it if you could additionally provide us with the first few lines, edited to protect sensitive information, from `%systemdrive%\TMPSQL\%date:~0,2%-%date:~3,2%-%date:~6,4%\SQLDBList.txt`. – Compo Oct 22 '19 at 19:47
  • Understand. First time I'm using StackOverflow. I really could have been more objective. I am very grateful for your help! I understand your placement about the date assignment in the GDRIVE command, I will adjust. I didn't quite understand how to provide the data you request. I send the file to you or just write here line by line? – Yuri Prawucki Oct 22 '19 at 20:46
  • Very good your script adjustments. Actually my variable declaration for today's date was very bad. – Yuri Prawucki Oct 22 '19 at 20:58
  • Just add it to your question, by way of the [edit](https://stackoverflow.com/posts/58506602/edit) facility. When you do that, please highlight it and click on the **`{}`** button to format it as code. Don't forget though to only post a few lines, and ensure that there's nothing sensitive there. – Compo Oct 22 '19 at 21:07
  • Remove this `set BACKUPFILENAME=%BACKUPPATH%\%%i.bak` and change this `sqlcmd -E -S %SERVERNAME% -Q "BACKUP DATABASE [%%i] TO DISK=N'%BACKUPFILENAME%' WITH NOFORMAT, NOINIT, NOREWIND, NOUNLOAD"` to this `sqlcmd -E -S %SERVERNAME% -Q "BACKUP DATABASE [%%i] TO DISK=N'%BACKUPPATH%\%%i.bak' WITH NOFORMAT, NOINIT, NOREWIND, NOUNLOAD"`. Also replace this, `7za a -r -tzip -bso0 -bsp0 -sdel -p%PSWDZIP% %BACKUPFILENAME%.zip %BACKUPFILENAME%`, with this `7za a -r -tzip -bso0 -bsp0 -sdel -p%PSWDZIP% %BACKUPPATH%\%%i.bak.zip %BACKUPPATH%\%%i.bak`. – Compo Oct 22 '19 at 22:27
  • It's working! I do not believe. Running smoothly, uploading to google drive, uploading to shared folder on the network and recycling. I don't know how to thank you. Thank you very much!!! – Yuri Prawucki Oct 22 '19 at 23:53
  • To explain, the code essentially required delayed expansion because you were both defining a variable and using it within the same parenthesised block. I could have closed your question as a duplicate of [this](https://stackoverflow.com/questions/30282784). However, on this occasion I decided it would be simpler to prevent it rather that work around it. – Compo Oct 23 '19 at 00:02

1 Answers1

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.

Nico JL
  • 409
  • 3
  • 6