If you’re like most database administrators, you probably rely on a script to back up your SQL Server databases. But what if something goes wrong? What if the script fails? That’s why it’s important to have a backup strategy that includes a batch script. A batch script is a simple text file that contains commands that can be executed automatically. You can use a batch script to back up your SQL Server databases or any other type of data. There are several ways to create a batch script. You can use an existing program or you can write your own. The most important thing is that the batch script is easy to use and understand. Here are some tips for creating a successful batch script:

  1. Make sure the commands in the batch script are easy to understand. Use clear, concise language and avoid long lines of code.
  2. Use standard Windows commands and utilities to create the scripts. This makes them easier to manage and troubleshoot.
  3. Test the scripts before you deploy them on production servers. Make sure they work as expected before you put them into action ..

To get right to the point, this is the backup script:

Assuming the date is 1/13/2009 and you have 3 databases named ‘MyDB’, ‘AnotherDB’ and ‘DB Name with Spaces’, the script will produce 3 files in the backup location specified:

REM Get date in format YYYY-MM-DD (assumes the locale is the United States) FOR /F “tokens=1,2,3,4 delims=/ ” %%A IN (‘Date /T’) DO SET NowDate=%%D-%%B-%%C

REM Build a list of databases to backup SET DBList=%SystemDrive%SQLDBList.txt SqlCmd -E -S MyServer -h-1 -W -Q “SET NoCount ON; SELECT Name FROM master.dbo.sysDatabases WHERE [Name] NOT IN (‘master’,’model’,’msdb’,’tempdb’)” > “%DBList%”

REM Backup each database, prepending the date to the filename FOR /F “tokens=*” %%I IN (%DBList%) DO ( ECHO Backing up database: %%I SqlCmd -E -S MyServer -Q “BACKUP DATABASE [%%I] TO Disk=’D:Backup%NowDate%_%%I.bak’” ECHO. )

REM Clean up the temp file IF EXIST “%DBList%” DEL /F /Q “%DBList%”

ENDLOCAL

2009-01-13_AnotherDB. bak 2009-01-13_DB Name with Spaces. bak 2009-01-13_MyDB. bak

Customizing and Running the Batch Script

Of course, you will want to customize the script to your environment so here is what you need to do:

If your machine’s locale is not set to the US, the command ‘Date /T’ may not return the date in the format “Tue 01/13/2009”. If this is case, the NowDate variable will not produce the desired format and should be adjusted. (1 place) Change ‘MyServer’ to be the name of your SQL Server (add the instance name if applicable). (2 places) The databases named ‘master’, ‘model’, ‘msdb’ and ‘tempdb’ are databases which ship with SQL Server. You can add additional database names to this list if you do not want them to be backed up. (1 place) Change the backup location from ‘D:Backup’ to the location where you want the database backup files stored.

Once you have customized the batch script, schedule it to run via Windows Task Scheduler as a user with Administrator rights and you are all set.