Microsoft’s SQL express is great for most applications. One thing it lacks is a good way of backing up the database using a graphical user interface. Or does it? A lot of people don’t realize that backing up a SQLEXPRESS database isn’t as hard as everyone makes it out to be.
You will need to familiarize your self with the word “instance”. An instance allows you to completely separate different databases. If you take down one instance normally the other instances can stay up and running. I mention this because when you are creating the backup script, you are going need to know what instance you are going to be backing up.
Once you know your instance, you will need to take note of all the databases inside of the instance that need to be backed up. Each script you will need to create will be for one instance and one database in that same instance.
Go to Explorer and create a a folder structure as follows:
Inside that folder, I will make the folders “backups” and “scripts”. Because I know you are smart I won’t explain to you what I am going to put in each of those directories
Now that we have created the folders we will load SQL studio manager. It’s important to note a few things when first loading the SQL Studio Manager.
When you click on the “server name” drop down box you will notice what the names of the instances are on the server. In this case I have two. The first one is called “SQLEXPRESS” and the other one is called “BKUPEXEC”. Notice I didn’t include “DC01” in the instance name?
Hit connect button and log in. Right click the database you want to make a backup of. Select tasks and then “Back up..”
Make sure that you’ve filled out the window in a similar fashion. Notice in the destination section I’ve selected “C:\SQL-Backup\backups\databasename.bak” as the file. Make sure you have the “.bak” extension on it.
Next we need to go to the options section just below General. Select “Overwrite all existing backups sets” otherwise your backup file will grow and grow.
Once you have those options selected you can get SQL Studio Manager to create a .sql script. This script will backup the database just as you have selected. To create the script click the “script” drop down button and select: “script action to file”. You could also just hit “CTRL+SHIFT+F”.
Select the folder – “C:\SQL-Scripts\scripts”. Save the backup as “databasename.sql”
Click start – type “cmd” and hit enter. A black command prompt will open.
Now to run the .sql file we will need to run a command from the command prompt. The command will look like:
SQLCMD.EXE -S .\instance-name -i “C:\SQL-Backup\scripts\databasename.sql”
In my example I would run:
SQLCMD.EXE -S .\SQLEXPRESS -i “C:\SQL-Backup\scripts\opas.sql”
To schedule a backup all you would need to do is to throw that command in a batch file and run it in Task Scheduler.