Automate backups of databases in SQL Server Express Editions

You need to take the following 3 steps to backup your SQL Server databases using Windows Task Scheduler

Step 1: Using SQL Server Management Studio express or Sqlcmd create the following stored procedure in your master database:       Stored Procedure:- Download

 

Step 2: In a text editor create a batch file that is named sqlbackup.bat and then copy the text from one of the following examples depending on your scenario into that file:

Example1: Full Backups of ALL databases in local named instance of SQLEXPRESS using Windows Authentication
// sqlbackup.bat
sqlcmd -S .\EXPRESS –E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @backupType='F'" 
 Example2: Differential Backups of ALL databases in local named instance of SQLEXPRESS using SQLLogin and its password
// sqlbackup.bat
sqlcmd -U SQLLogin -P password -S .\SQLEXPRESS -Q "EXEC sp_BackupDatabases  @backupLocation ='D:\SQLBackups', @BackupType=’D’"
 Note: The SQLLogin should at least have Backup Operator role at SQL Server.


Example3: Log Backups of ALL databases in local named instance of SQLEXPRESS using Windows Authentication
// sqlbackup.bat
sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\',@backupType='L'"


Example4: Full Backups of database USERDB in local named instance of SQLEXPRESS using Windows Authentication
// sqlbackup.bat
sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @databaseName=’USERDB’, @backupType='F'"

Similarly you can take differential and log backup of USERDB by passing in ‘D’ and ‘L’ respectively for the @backupType parameter.

Step 3: Schedule a job using Windows Task Scheduler to execute the batch file created in Step 2. To do this use the following procedure:

1. On the computer that is running Microsoft SQL Server Express, click Start , point to All Programs , point to Accessories , point to System Tools , and then click Scheduled Tasks. 
2. Double-click Add Scheduled Task . 
3. In the Scheduled Task Wizard, click Next . 
4. Click Browse , click the sqlbackup.bat file that you created in step 2, and then click Open . 
5. Type SQLBACKUP for the name of the task, and then click Daily . Then, click Next . 
6. Specify information for a schedule to run the Task. We recommend that you run this at least once every day. Then, click Next .
7. In the Enter the user name field and in the Enter the password field, type a username and a password. Then, click Next .  Please note that this user should at least be assigned the BackupOperator role at SQL Server level if you are using one of the batch files in example 1, 3 and 4.
8. Click Finish . 
9. Execute the scheduled task at least once to ensure that the backup is created successfully.

Note: The folder for SQLCMD executable is generally in the Path variables for the server after SQL Server is installed, but in cases where the Path variable does not list this folder, you can find it under \90\Tools\Binn (For example: C:\Program Files\Microsoft SQL Server\90\Tools\Binn).
 Please note the following when using the procedure documented in this article:

• Windows Task Scheduler service must be running at the time when the job is scheduled to run. It is recommended that you set the startup type for this service as Automatic. This ensures that the service will be running even on a restart.
• There should be enough space on the drive where the backups are being written to. It is recommended that you clean the old files in the backup folder on a regular basis to ensure that you do not run out of disk space. The script does not contain the logic to cleanup old files.