Backup MS SQL Server data

This article explains how to backup MS SQL Server data with Backup4all using a scheduled task. The database files are locked by the MS SQL Server. In order to backup these files and avoid data corruption, it is necessary to perform some actions.

On this page: How to configure the backup in Backup4all 4 How to configure the backup in Backup4all 3 Video Tutorial MS SQL Server data backup

The database files are locked by the MS SQL Server. In order to backup these files and avoid data corruption, it is necessary to perform these actions:

  1. Stop the MS SQL Server.
  2. Perform the backup.
  3. Restart the MS SQL Server.

To automate the process described above, a backup needs to be configured in Backup4all that will automatically stop the MS SQL Server, perform the backup and restart the MS SQL Server. The MS SQL Server will be stopped/restarted using specific commands in the Run action before/after backup sections of Backup Properties->Advanced page.

How to configure the backup in Backup4all 4

In Backup4all 4, you can specify when the action after backup will be executed, on: success, warnings or errors.

Note: If you don't have a Windows login password, before configuring the backup you must set (check) the "Allow blank passwords when scheduling" option from Backup4all's Tools->Options->Preferences window.

Steps needed to configure the backup:

  1. Create a new backup job (i.e. Backup_SQL) :
    • Open Backup4all and select File->New Backup (Ctrl+N).
    • On the first page enter a name for the backup (Backup_SQL) in the Name field.
    • In the Where do you want to save the backup? section, you can select the backup destination. Click Next.
    • On the What do you want to backup page, add the MS SQL Server data to be backed up. You will have to select  both the .mdf and .ldf files from the Data folder. Click Next.
    • On the third page, you can choose the backup type and the encryption. Mirror is recommended. Click Next.
    • Press Advanced mode button.
    • Optionally on the Filter page you can add some include/exclude filters. Click Next.
    • On the Advanced page
      • In the Run action before backup section set (check) the Run program or action option and type: net stop MSSQLSERVER.
      • In the Run action after backup section set (check) the Run program or action option and type net start MSSQLSERVER..
      • Check the "Success", "Warnings" and "Errors" options.
    • Click Save.
  2. Add a scheduled task for this newly created group.
    • Press the Scheduler (F11) button from Backup4all's toolbar.
    • Select the backup job you want to scheduler (Backup_SQL).
      • Click on Set password and enter your windows login password (if you don't have one, skip this step).
      • After pressing OK the Task Scheduler window will open and you can set the time and frequency for the scheduled task (recommended is to set a time and date when the server is less utilized).
    • Click OK.
    • Click Close.

How to configure the backup in Backup4all 3

If you use Backup4all 3 the after action is not executed in case of backup failure, so we recommend creating two backup jobs (in the same group). First job does the actual backup of the MS SQL Server data while the second job is a caution measure that ensures the MS SQL Server will be restarted (in case the first job fails and the batch file that restarts the server is not executed).

Note: If you don't have a Windows login password, before configuring the backup you must set (check) the "Allow blank passwords when scheduling" option from Backup4all's Tools->Options->Preferences window.

Steps needed to configure the backup:

  1. Create a new backup group from File->New Group. Name the group: Backup_SQL
  2. Create the first backup job (i.e. Backup_SQL1) in Backup_SQL group:
    • Open Backup4all and select File->New Backup (Ctrl+N).
    • On the first page enter a name for the backup (Backup_SQL1) in the Name field.
    • In the Where do you want to save the backup? section, you can select the backup destination. Click Next.
    • On the What do you want to backup page, add the MS SQL Server data to be backed up. You will have to select  both the .mdf and .ldf files from the Data folder. Click Next.
    • On the third page, you can choose the backup type and the encryption. Mirror is recommended. Click Next.
    • Press Advanced mode button.
    • Optionally on the Filter page you can add some include/exclude filters. Click Next.
    • On the Advanced page
      • In the Run action before backup section set (check) the Run program or action option and type: net stop MSSQLSERVER
      • In the Run action after backup section set (check) the Run program or action option and type: net start MSSQLSERVER
    • Click Save.
  3. In the same backup group create the second backup job (i.e. Backup_SQL2) that will ensure the MS SQL Server is restarted even if the first backup job fails
    • Select File->New Backup (Ctrl+N).
    • On the first page enter a name for the backup (Backup_SQL2) in the Name field
    • In the Where do you want to save the backup? section, you can select the backup destination. Click Next.
    • On the What do you want to backup page, add a less important file to be backed up each time. Click Next.
    • On the Type page, select the Full backup type (or differential backup of sql server). Click Next.
    • On the Advanced page
      • On the Run action before backup section set (check) the Run program or action option and type: net start MSSQLSERVER
    • Click Finish.
  4. Add a scheduled task for this newly created group.
    • Press the Scheduler (F11) button from Backup4all's toolbar.
    • Press Add and in the Run section set (check) the Backup group option. Select Backup group and choose the new group name defined in step 1 (Start_stop_SQL).
      • Click on Set password and enter your windows login password (if you don't have one, skip this step).
      • After pressing OK the Task Scheduler window will open and you can set the time and frequency for the scheduled task (recommended is to set a time and date when the server is less utilized).
    • Click OK.
    • Click Close.

Video Tutorial MS SQL Server data backup:

Note: The jobs will run sequentially in the same order as they appear in the backup list (from the left side of Backup4all's window). It is important to name the jobs in such a manner that they are in the correct order (first backup job in the list is the one that actually backs up the MS SQL data, second job is the one that ensures the server gets restarted.

Here is what happens when the scheduled task runs:

  • The commands from the batch file that stops the MS SQL Server will be executed.
  • The MS SQL Server data added as source in the first backup job will be backed up.
  • The commands from the batch file that starts the SQL Server will be executed.
  • (For Backup4all 3 only) The commands from the batch file that starts the SQL Server will be executed (again) ensuring that the MS SQL Server is restarted (if the server was restarted by the previous backup job nothing will happen).
  • (For Backup4all 3 only) The second backup job will be executed.

Applies to:

  • Backup4all 3 and Backup4all 4
  • MS SQL Server 2000-2005

Comments


NEW: Backup4All 5.1.541