Although we are using a “normal” SQL Server, we have also a lot of SQL Server Express running. Mostly these are database were a daily backup is more than enough, for example for Windows Update Services.
So far, we are using customs scripts for each server to backup them, but I wanted a different and more easier way to do it. Once it is set up, all you need to do to backup a new database on the server is to copy a batch file and rename it.
First, create a folder on the server you want to backup, e.g. “C:\SQLServBkp”. Give “NETWORK-SERVICE” write access to this folder. Since SQL Server will do the backup for us, we need to make sure that the account has access to create the backup file.
Secondly, make sure that you have the SQL Server Tools installed since we will use SQLCMD.exe. In case you have only the database installed, you can easily download and install the SQL Server Management Tools. And it’s no problem to use the 2008 version even if your SQL Server Express is only 2005.
Next, create a batch file named C:\SQLServBkp\_SQLBackupWorker.bat with the following contents:
SET SQLCMD_Path=C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE
SET SQLServerInstance=.\sqlexpress
"%SQLCMD_Path%" -S %SQLServerInstance% -E -e -v db=%1 -v bakfile="%~dp0%1_Backup.bak" -i"%~dp0_SQLBackupWorker.sql"
Please note: The last line is ONE single line.
The variable SQLCMD_Path points to SQLCMD.exe, the path that is used here is for the default SQL Server 2008 installation.
The variable SQLServerInstance is the instance of the SQL Server you want to backup; the default is “.\sqlexpress”. In case you want to backup the Windows Internal Database (for example WSUS) you need to write “\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query”. Strange syntax, but it works.
Now we need a file that issues the backup command to the SQL Server, name it C:\SQLServBkp\_SQLBackupWorker.sql
-- SQLBackupWorker.sql START
PRINT 'Running on server ' + @@ServerName
PRINT 'Backing up database [$(db)] to file [$(bakfile)]'
BACKUP DATABASE $(db) TO DISK = '$(bakfile)' WITH INIT
-- SQLBackupWorker.sql END
Finally, simply create one batch file that is named exactly as the database you wish to backup. For example, if you want to backup the database “WhatsUp”, name the file C:\SQLServBkp\WhatsUp.bat and put the following contents in (one line):
call "%~dp0_SQLBackupWorker.bat" %~n0 >"%~dpn0.log"
If you want to backup a second databse, e.g. “NetFlow”, you simply create a file name C:\SQLServBkp\NetFlow.bat and put the exact same contents in as above.
The trick is quite simple: The batch will extract its own name and pass it over to _SQLBackupWorker.bat. This will in turn use it as a name for the backup file and passes it over to _SQLBackupWorker.sql which caries out the backup.
Once this is all set up, give it a try and execute one of the batch files, e.g. WhatsUp.bat. A log file (WhatsUp.log) is automatically created and you can review it. If it seems that nothing is working, check the instance name and make sure that NETWORK-SERVICE has write access to the folder you have created.
You can simply schedule any of the batches with Task Scheduler so they run on a regular basis.