Process backup,update and rollback with sqlServer scriptsAnswered

Database Backup
Before any changes are made, the .bat file that backs up the database is executed. This ensures that, in the event of an error, you can restore the previous state.

Database Update
The .bat file containing the SQL commands to update the database is executed. It is recommended that these commands be executed within a transaction so that if an error occurs, the operation can be automatically reversed.

Rollback
The .bat file for rollback is not executed as part of the normal flow. You should only run it if and when you detect that the update is failing or has not been applied correctly. This will allow you to revert the changes made and restore the database to its pre-update state (using, for example, the previously performed backup).

In summary:

Run the backup and then the update routinely.

Rollback is used only as a corrective measure when something goes wrong.

This approach is a good practice to minimize risks during upgrades. If the upgrade completes successfully, a rollback is not necessary.

1. Backup File (backup.bat)

This script creates a backup of the database before any updates:

@echo off
REM Configuration: Change these values according to your environment
set SERVER=MY_SERVER\INSTANCE
set DATABASE=MyDatabase
set BACKUP_DIR=C:\Backups

REM Format the date for the filename (adjust the format as needed)
set TIMESTAMP=%DATE:~-4%%DATE:~3,2%%DATE:~0,2%
set BACKUP_FILE=%BACKUP_DIR%\%DATABASE%_Backup_%TIMESTAMP%.bak

echo Starting backup for database %DATABASE%...
sqlcmd -S %SERVER% -E -Q "BACKUP DATABASE [%DATABASE%] TO DISK = N'%BACKUP_FILE%' WITH NOFORMAT, NOINIT, NAME = N'%DATABASE%-Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10"
echo Backup completed.
pause
	

2. Update File (update.bat)

This script executes a SQL script (for example, update.sql) that contains the necessary update commands for the database:

@echo off
REM Configuration: Change these values according to your environment
set SERVER=MY_SERVER\INSTANCE
set DATABASE=MyDatabase
set SCRIPT=update.sql

echo Running update script...
sqlcmd -S %SERVER% -E -d %DATABASE% -i %SCRIPT%
echo Update completed.
pause

3. Rollback File (rollback.bat)

This script restores the database from the backup created earlier. Note: You only execute this file if the update fails and you need to revert the changes.
Make sure the value of TIMESTAMP matches the backup file you wish to restore.

@echo off
REM Configuration: Change these values according to your environment
set SERVER=MY_SERVER\INSTANCE
set DATABASE=MyDatabase
set BACKUP_DIR=C:\Backups

REM Specify the date of the backup to restore (example: 20250325)
set TIMESTAMP=20250325
set BACKUP_FILE=%BACKUP_DIR%\%DATABASE%_Backup_%TIMESTAMP%.bak

echo Restoring backup for database %DATABASE%...
sqlcmd -S %SERVER% -E -Q "RESTORE DATABASE [%DATABASE%] FROM DISK = N'%BACKUP_FILE%' WITH REPLACE"
echo Restore completed.
pause