SqlCmd -E -S MyServer -Q “BACKUP DATABASE TO Disk=’D:Backup%NowDate%_%%I. REM Backup each database, prepending the date to the filename SqlCmd -E -S MyServer -h-1 -W -Q “SET NoCount ON SELECT Name FROM WHERE NOT IN (‘master’,’model’,’msdb’,’tempdb’)” > “%DBList%” REM Get date in format YYYY-MM-DD (assumes the locale is the United States)įOR /F “tokens=1,2,3,4 delims=/ ” %%A IN (‘Date /T’) DO SET NowDate=%%D-%%B-%%C To get right to the point, this is the backup OFF

Instead, in true “set it and forget it” fashion, we will create a batch script which will adapt to your SQL Server as new databases are added and removed. Be sure to review the logs outputted to see if there are any errors due to syntax or locks on the files. Additionally, the database backups will all be appended to one file which will grow by the size of the new backup each time it is run. For the actual cutover, change the source server’s shares to Read Only to prohibit users from changing file and run the task one last time (right-click the task and click Run). We have already covered how to back up a SQL Server database from the command line, so what if you want to backup all your databases at once? You could create a batch script which runs the backup command for each database, but this script would have to be updated each time a database is added or removed.

Schedule server 2003 backup batch file