Simple script to back up your SQL Server databases automatically

Sooner or later, the database application you have developed requires a backup plan. Nobody is going to cheer if you force your customers to take manual backups. Automation is the key here, so this is what you should strive for.

Automatically creating an SQL Server database backup can be done using the BACKUP DATABASE command, which can be executed either manually from SQL Server Management Studio (SSMS), or from the command-line with the help of the SQLCMD utility.

To take a backup, you need to know the SQL Server name (host or IP address), possible instance name and the database name. Furthermore, you need suitable credentials. Windows authentication can be a big help here, and also improves security as you don't need to include the username and password as plaintext strings in a Windows batch file (.cmd file).

The BACKUP DATABASE command also requires a destination filename. This filename often the biggest pain point, because you need to figure out a unique filename for each backup file so that the latest backup does not overwrite the previous one.

In Windows batch files, there are limited ways in which you can manipulate strings (and dates), but luckily, this can be done using environment variables. The SET command contains special support for taking substrings out of variable values, and this in turn can be used to retrieve the current year, month and day of the current date and time. This allows you to create unique filenames assuming that you take a backup only once per day.

Let's take a look at a script that parses the current date and time to its parts: the year, the month and the day:

@echo off
rem ** Create a string with format "yyyy-mm-dd"
set backup_day=%date:~3,2%
set backup_month=%date:~6,2%
set backup_year=%date:~-4%
set backup_datestring=%backup_year%-%backup_month%-%backup_day%

The main thing here is the special batch file variable called %date% which returns the current date and time in the format local to the current user's regional settings. For example here in Finland, it returns a string like: "la 31.12.2011" ("la" stands for the acronym of Saturday in Finnish, notice the format dd.mm.yyyy).

The parsing commands with the set command allow you to use the :~ operator to retrieve parts of this string based on zero-based character indexes. The specification "3,2" would take two characters starting from the index three (fourth character), i.e. in this case, "31". Similarly, "6,2" would take the numbers "12", i.e. December. The specification "-4" would take four last characters from the string, in this case the year "2011".

Of course, this requires adjustment for each Windows locale, but with some tweaking, you could make it rather universal. I have hard time finding the official documentation for this on the Technet libraries, but if you run "set /?" on the command line, the second page talks about substring expansion. This seems to be the official name for the feature. You can also find tips on this here, here and here.

Now that we have the date and time parsed (as to make sure our filenames are unique), we can run the actual backup. This could be done as follows:

...
cd /d "C:\Program Files\Microsoft SQL Server\90\Tools\Binn"
echo Creating a backup for the date %backup_datestring%...

echo Backing up the database...
sqlcmd -s MYSERVER\MYINSTANCE -e -Q "BACKUP DATABASE MyDatabaseName
TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\
MyDatabaseName %backup_datestring%.bak' WITH INIT, NOSKIP, NOFORMAT"

Here, we first go to the correct folder location to find the command-line utility SQLCMD.EXE. This command allows us to execute the BACKUP DATABASE command with the current Windows user account. The -s parameter gives the server hostname and possible instance name (if you want to use the default instance, just give the server name without the backslash). Then, the "TO DISK" parameter gives the destination folder and filename for the backup file. Note how we use the %backup_datestring% environment variable set earlier.

Here is a complete script you can use to tune to your own purposes:

@echo off
rem ** Create a string with format "yyyy-mm-dd" from the Finnish settings, adjust
for your own Windows locale!
set backup_day=%date:~3,2%
set backup_month=%date:~6,2%
set backup_year=%date:~-4%
set backup_datestring=%backup_year%-%backup_month%-%backup_day%
cd /d "C:\Program Files\Microsoft SQL Server\90\Tools\Binn"
echo Creating a backup for the date %backup_datestring%...

echo Backing up the database...
sqlcmd -s MYSERVER\MYINSTANCE -e -Q "BACKUP DATABASE MyDatabaseName
TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\
MyDatabaseName %backup_datestring%.bak' WITH INIT, NOSKIP, NOFORMAT"

echo Done!

Note that if you wish to back up multiple databases, simply repeat the SQLCMD command shown on the last three lines, and adjust the database names and/or server names. As a final step, you could clear our all the backup_* environment variables just created.

Once the complete script is in place, you can simply create a regular Windows scheduled task with the appropriate credentials, and then make the script run for example every night. This would create a nice, full backup of your database every day without you having to worry much (of course, you need to monitor the process is working).

One final question related to this kind of scripts is that the backup files accumulate in the destination folder, and one day or another, you run out of disk space. Most often, such events happen only when you are either very busy, or on vacation somewhere warm.

What would be an easy remedy for this? Try the new Windows script command FORFILES. This command supports the "/d" parameter, which allows you to list only files that are older than the given number of days. For example, "forfiles /d-7" would list files that are older than seven days. Then, you could simply delete all such files to make the backup file rotation clean and simple.

Sidenote: The latest Windows command-line reference is somewhat difficult to find, but if you browse the Technet library enough, you will find the correct location. The link provides and A-Z list of all available commands, including for instance the SET command. Note however, that the last update to this documentation (at this writing) is from 2007, so not all the latest tricks are there.

When talking about backups, there's always a need for the final reminder: do test your restores regularly. Nothing is worse than *thinking* that you have a valid backup. If your database for example becomes suitably corrupted, a full backup may run, but you cannot restore from it.

Good luck!