Here are the steps I am doing to backup a SQL express database. In this example, I am going to demonstrate how to backup a Skype for Business SQL express database.
Step 1: Connect to your SQL Express Database Engine instance using SSMS. Make sure to put the instance name.
Step 2: Once you are connected, find the database you want to backup, right-click > Tasks > Backup
Step 3: On the Backup Database window, make the changes you would like to make (backup type, destination, etc). Click the drop-down for Script and select ‘Script Action to New Query Window’
Step 4: On the new Query window, make the final changes. You can change the path to a UNC path if you wish. Run the script to make sure it works.
BACKUP DATABASE [lyss] TO DISK = N’\\path_to_backup_directory\lyss.bak’ WITH NOFORMAT, NOINIT, NAME = N’lyss-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
If you wish to backup more databases on 1 script, repeat step 2 and select ‘Script action to Clipboard’ and put all scripts into one query window and then save it as one SQL file.
Step 5: Save the script to a .SQL file
Step 6: Create a scheduled task if you wish and under Actions, browse to the location of the SQLCMD file.
“C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE”
And add the arguments as: -S \<instance name> -i <path to the SQL file you saved in step 5>
If you are not using a scheduled task. Simply run the command:
SQLCMD -S \<instance name> -i <path of the SQL file you saved in step 5>