How To - Setting up an automatic backup with Express version of SQL Server

Because SQL Server express versions don’t have the possibility to run Scheduled tasks themselves, we need to do it in another way with Scripts and the Windows Task Scheduler:

1. Add a new windows login to the SQL server that has enough access to take backup and restore databases
   1. This can be done in the SQL studio management console
      
2. Download the zip file attached and extract the files to a place where the windows user has access on the server where TM Master is installed and do changes in the files so they correspond with the file paths and databases for the installations
3. Install sqlcmd Utility if you have not already installed it. This can be download from the page below if you don’t already have it:
   https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-2017
   Follow the instructions so the Utility gets installed with default preferences if you don’t have other preferences
4. Create a new Scheduled task
   1. Search for Task Scheduler on your server and open it
   2. Create a new task and give it a descriptive name
      
   3. Create a trigger, usually time based
      
   4. Add an “Start a program” action and define “powershell.exe” as the starting program
      in the “Add arguments” field you need to add: -noprofile -executionpolicy bypass -File C:\TEMP\Script\BackupPS.ps1
      
   5. Remove all the checkboxes in the Conditions tab if you do not have other preferences
   6. Keep the default settings in the Settings tab if you do not have other preferences
   7. When you are finished, you can press OK and the task should ask for Credentials that you need to fill in.

If you want an automatic backup and restore to a test environment and you can do the same, but use the BackupandRestore.sql instead of the Backup.sql.