# Solved: .bat file help.



## BenjiBob (Jan 12, 2012)

hi,

I want to add the date in the titles of my future database backups. I already have a .bat file in place which overwrites itself on start up. Could anyone suggest the necessary amendment needed for my .bat file, shown below, please?

SET SQLSERVER=WENDY-PC\TAGCARE
SET DATABASE=CAREDATA
SET BACKUPLOCATION=C:\tagtronics\backup\
SET BACKUPFILENAME=CareData.bak
DEL %BACKUPLOCATION%%BACKUPFILENAME% /S /Q
REM Reindex all the keys within the database
SQLCMD -S%SQLSERVER% -E -d%DATABASE% -Q "EXEC spRebuildAllIndexes"
REM -- Truncate the log file attached to the database, this can sometimes get a little large and slow down the system overall
SQLCMD -S%SQLSERVER% -E -d%DATABASE% -Q "DBCC SHRINKFILE (N'%DATABASE%_log',100)"
SQLCMD -S%SQLSERVER% -E -d%DATABASE% -Q "ALTER DATABASE [%DATABASE%] SET RECOVERY SIMPLE"
SQLCMD -S%SQLSERVER% -E -d%DATABASE% -Q "CHECKPOINT"
SQLCMD -S%SQLSERVER% -E -d%DATABASE% -Q "ALTER DATABASE [%DATABASE%] SET RECOVERY FULL"
REM -- Shrink the actual database itself
SQLCMD -S%SQLSERVER% -E -d%DATABASE% -Q "DBCC SHRINKDATABASE (%DATABASE%)"
SQLCMD -S%SQLSERVER% -E -d%DATABASE% -Q "DBCC SHRINKFILE (N'%DATABASE%_log',100)"
REM -- Backup the databse to our backup location
SQLCMD -S%SQLSERVER% -E -d%DATABASE% -Q "BACKUP DATABASE [%DATABASE%] TO DISK = N'%BACKUPLOCATION%%BACKUPFILENAME%' WITH NOFORMAT, INIT, NAME = N'Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10"


----------



## Squashman (Apr 4, 2003)

Date is a variable.
At a cmd prompt type *echo %date%*.

The date variable can be maninpulated to make a more user friendly date output but I would need to see the output of the date variable first. The date output is based on your regional and language settings.


----------



## BenjiBob (Jan 12, 2012)

thanks for responding,

the command prompt came back with: 13/01/2012


----------



## Squashman (Apr 4, 2003)

So you can do a SET command to set todays date to a variable without the slashes. You have two options.
If you just want the slashes removed and use your existing date format of DDMMYYYY you can do this.

set TODAY=%DATE:/=%
so now the variable TODAY is set to 13012012

But I prefer to put my dates in YYYYMMDD format. To do that you can do the following.
set TODAY=%DATE:~6,4%%DATE:~3,2%%DATE:~0,2%
So now the variable TODAY is set to 20120113


----------



## BenjiBob (Jan 12, 2012)

cheers that worked a treat,

just one more thing that you might be able to help me with. We've currently got this .bat related backup running on startup, but we'd prefer it to have it running as the machine shuts down. Any ideas how I could achieve this?


----------



## Squashman (Apr 4, 2003)

If you are running a NON HOME version of Windows, like XP Pro or Windows 7 (Pro,Ultimate), Windows Vista (Business,Ultimate) then you can use Group Policy to schedule shutdown and start up scripts.


----------



## BenjiBob (Jan 12, 2012)

spot on mate,

that's two out of two. You've tweaked our backup to perfection, I'm genuinely appreciative, thanks a lot.


----------

