Backup MS SQL Server data

Summary
This article explains how to backup MS SQL Server data with Backup4all using a scheduled task.

On this page:

How to create a batch file that stops the MS SQL Server
How to create a batch file that restarts the MS SQL Server
How to configure a backup for the MS SQL Server data using Backup4all

Details
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 some commands from a batch file loaded by Backup4all before/after the backup.

MS SQL Server can be stopped using the sc.exe command line program. It is recommended to stop the MS SQL Server during off-hours or in week-ends. Here is the syntax for calling sc.exe:

Syntax
sc [ServerName] stop [ServiceName] [ServiceArguments]

Parameters
ServerName: Specifies the name of the remote server on which the service is located. The name must use the Universal Naming Convention (UNC) format ("\\myserver"). To run SC.exe locally, ignore this parameter.
ServiceName: Specifies the service name to be stopped.

To automate the startup sequence of MS SQL Server a batch file that calls sc.exe needs to be created and added as a “before action” in the backup job scheduled in Backup4all. Below is an example of commands that can be used to stop  the MSSQLServer and SQLServerAgent services on a local computer (code also contains a sequence that sets a delay between each commands, to ensure the services were stopped):

sc stop sqlserveragent
@ECHO OFF
SET NUM=1
:LOOP
IF %NUM% == 6000 GOTO END1
SET /A NUM=NUM+1
GOTO LOOP
:END1
@ECHO ON

sc stop mssqlserver
@ECHO OFF
SET NUM=1
:LOOP2
IF %NUM% == 6000 GOTO END2
SET /A NUM=NUM+1
GOTO LOOP2
:END2
@ECHO ON

These commands will be saved in a batch file (stop_sql.bat) that will be executed by Backup4all before starting the backup.
To create the batch file:

1. Go to Start->Run type notepad and press Ok. In the window that opens select File->Save as and name the file stop_sql.bat
   
2. Add the commands needed to stop the MS SQL service following the example above (if is a local computer simply copy/paste the code above).
   

When the scheduled backup will start, the commands from the batch file will be executed, stopping the MS SQL Server. Only after that, the backup process will start. When the backup process has finished, the MS SQL Server must be restarted. You can set a second batch file to be automatically executed after the backup in order to restart the MS SQL Server.

How to create a batch file that restarts the MS SQL Server

MS SQL Server can be restarted using the sc.exe command line program.
Here is the syntax for calling sc.exe:

Parameters
ServerName: Specifies the name of the remote server on which the service is located. The name must use the Universal Naming Convention (UNC) format ("\\myserver"). To run sc.exe locally, ignore this parameter.
ServiceName: Specifies the service name to be started.
ServiceArguments: Specifies service arguments to pass to the service to be stopped. Note that this option is not used when the command “stop” is issued.

To automate the restart sequence of MS SQL Server, a batch file that calls sc.exe needs to be created and added as an “after action” in the scheduled backup job in Backup4all. Below is an example of commands that can be used to restart the MSSQLServer and SQLServerAgent on a local computer:

sc.exe start mssqlserver
@ECHO OFF
SET NUM=1
:LOOP
IF %NUM% == 6000 GOTO END
SET /A NUM=NUM+1
GOTO LOOP
:END
@ECHO ON

sc.exe start sqlserveragent

These commands will be saved in a batch file (start_sql.bat) that will be executed by Backup4all after the backup process.
To create the batch file:

1. Go to Start->Run type notepad and press Ok. In the window that opens select File->Save as and name the file start_sql.bat
   
2. Add the commands needed to start the MS SQL service following the example above (if is a local computer simply copy/paste the code above).

If the backup is finished without errors, the MS SQL Server will be restarted.

Configure the backup in Backup4all

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 add the batch file that stops the MS SQL Server (stop_sql.bat).
     • In the Run action after backup section set (check) the Run program or action option and add the batch file that restarts the MS SQL Server (start_sql.bat).
   • 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 add the batch file that restarts the MS SQL Server start_sql.bat.
   • 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.

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.

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.
• 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).
• The second backup job will be executed.
  

Applies to:

• Backup4all 4
• MS SQL Server 2000-2005