Squishnet

Automating SQL Express Backup

Posted on 13 December 2011

If you are using MS SQL Express for your vcenter or web development project, you may want to automate the backup of your databases.  Unlike the full SQL Server, SQL Express doesn’t have the ability to schedule database backups or maintenance directly.  However, this can be accomplished easily if you have SQL Server Management Studio installed.  I have a 2 step process running in my lab.  The first step uses a scheduled task that runs a saved sql job to back up the database.  The second step is to copy the back-up to another location and add the date stamp to the file name.  That way I can have multiple day’s of backup stored.

Step 1: Create SQL Backup Job

1. Open Microsoft SQL Server Management Studio and expand the databases section.
2. Right click on the database you want to backup and select tasks > backup.
3. Set the General backup settings like (Full, Differential), backup set name and backup destination and file name. I set my disk path as C:\Sql_Backup to keep it simple for my script in step 2.
4. Set the Options for the backup like apend or overwrite backups and whether to verify after backup.
5. When you have the settings the way you want them, from the scripts pull down menu choose Script Action to File (see screenshot).
     
6. Save the .sql file to your hard drive.
7. Open Task Scheduler and create a new task.
8. In the RUN box on the Task enter the following: “C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE” -S .\SQLINSTANCENAME -i “C:\Program Files\Microsoft SQL Server\vcenter-bu-script.sql” .
9. Schedule the task to repeat daily or however often you want.
10. When you are finished with the task, Run it and verify it works correctly.  The  file will be saved to the location set in step 3.

 Step 2: Create a batch file to copy daily backups to a different location

1.  Create a Windows batch file with the following code listed below.
2. Set up a scheduled task to run the batch file.  Make sure the start time is sufficently delayed to allow the sql backup script to complete.

@Echo Off
NET USE T: \\backupserver\backupshare\
@For /F “tokens=2,3,4 delims=/ ” %%A in (‘Date /t’) do @(
Set Day=%%A
Set Month=%%B
Set Year=%%C
Set All=%%C_%%B_%%A
)
@For %%a in (“C:\Sql_backup\*.bak”) do copy %%a “T:\Sql_backups\%%~na_%All%.bak”
NET USE T: /delete
 

NOTE: When I run the date /t command from the server this runs on I get ‘Tue 12/13/2011′ so the tokens 2,3,4 represent the month, day, year and I’m leaving out the Tue.  If running date /t returns a different result you may need to modify the tokens in the script.