Create an automatic backup process for SQL Server Express version (4292452)

Backups require installing the SQL Server Management Studio Express.

If the database instance is stored in a SQL Server 2008 Express then the SQL Server 2008 Management Studio Express must be used. Newer versions use SQL Server Management Studio SMSS.

The following screenshots are from SQL Server Management Studio Express 2014, which is similar to earlier versions of SQL Server Express.

To automate the backup process the following phases are needed:

1. To create the back up job run SQL Server Management Studio Express.
   1. In the tree view, expand Server Objects | New Backup Device.
   2. The Backup Device dialog opens:
      
      1. For Device Name, type in a name for the new backup job (DABackup in this case).
         
      2. For Destination path, select the path to store the backups. It is best to use a location on another device.
      3. Click OK.
   3. Right click on the new backup device that created and select the option called "Backup Database".
      
   4. Select the database (DAConfiguration) and backup type (Select "Full").
      
     
2. On the left side, select Backup Options and set the following:
   
   1. Name: Create a name for the backup job.
   2. Description (optional).
   3. Press OK.
3. SQL Server runs the backup job to test it. If successful, it confirms this with the following message:
   
4. To verify the backup output, check the destination folder to ensure that the backup file exists. The backup of the database is DABackup.bak file.
5. The setup of the backup job is now complete. Repeat these steps for the DAReporting database.

• Create a batch program to run the job. A batch file must be used to run the SQL Server backup job created.
  1. Batch file will contain lines below and must have a .bat extension
     sqlcmd -S DASERVER\DESKTHOPAUTHORITY -E -Q "BACKUP DATABASE DAConfiguration TO DABackup"
     (To backup the DAReporting database a new line can be added and included).
  2. Replace "DASERVER" with the SQL Server physical machine name.
     Replace "DESKTOPAUTHORITY" with the instance name of the SQL Server where database are stored.
     Replace "DABackup" with the name of the backup job.
     Replace "DAConfiguration" with the name of the database.
  3. These values may vary according to the SQL Server configuration, the values used in this case are valid for a default installation.
• Create a scheduled job.
  1. To create a scheduled job, the built in Microsoft Windows Scheduler may be used to assign the batch file created above to a scheduled event.
     1. Open the Task Scheduler in the SQL Server computer.
     2. Select Action | Create Basic Task.
     3. Enter a name for the new task | Click Next.
     4. Select when the task will start and click Next.
     5. Complete the timing configuration and click Next.
     6. Select Start a program and click Next.
     7. In Program/Script enter the path/name of the batch file created to run the backup and click Next.
     8. Check the summary information and then click Finish.
     9. The new job will be available in the Task Scheduler Library.