How to Backup a SQL Server Database
Backing up a database is relatively straight forward using SQL Server Management Studio.
1. First open management studio and connect to the Database Server that contains the database you want to backup.
2. Expand the “Databases” folder and right-click on the database you want to make a backup of.
3. Choose Tasks -> Back Up… (now the Backup database dialog appears)
From here, if you simply hit the “OK” button, your database will be backed up in the default location. However, let’s specify a location so we can find it.
4. Select the “Remove” button to remove the current backup location
5. Select the “Add” button and add a new location to backup the database to. Now choose a location on disk and give it a file name. It is good practice to include the date of the backup in the filename. You may also want to save the file name with a .BAK extension
6. You can select “OK” and the backup will begin. You can optionally at this point, select the “Script” button at the top of the dialog in order to retrieve the script that SSMS uses to perform the backup. A new window will open and you will have your script. You can then use this script in a job, or run it yourself.