If you are a Dedicated Server customer running SQL Server on your Server, this article will help you a lot to backup your any database & also make a copy of that database using SQL Server Management Studio Express.
Here it is how you can do this step by step. So lets get started with Backup database
First of all connect to your database server using SQL Server Management Studio & open it.
Now Right Click on your database and under “Tasks” option, Choose “Back Up” as shown in below image
Next a new window will open where we have to configure where to save our database backup file (.bak)
You have to ensure that the “Backup type” is set to “Full” to get full backup. Now highlight the destination file that is already in the list by clicking on it and click “Remove” option.
Now click “Add” to specify where to save the backup file. A window will pop up, asking you where to save the backup file. Enter a location you want. For example, we are saving it to “C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup\medishop.bak”.
Now click on “Ok” to begin the database backup. If the database backed up successfully, you should receive a message as shown in below image.
After backing up the database, now we need to restore that database somewhere else or wherever you want. Here are the steps to restore it
Step 1: After you connect to the appropriate instance of Microsoft SQL Server Database Engine, in Object Explorer, Click the server name to expand the server tree.
Step 2: Right Click the database, point to Restore which opens the Restore Database dialog box.
Step 3: On the General Page, use the Source section to specify the source & location of the backup sets to restore. Select Device to manually specify the file or device to restore.
In Device option, click the browse (…..) button to open the Select backup devices dialog box. In the Backup media type box, select one of the listed device types. To select one or more devices for the Backup media box, click Add. After you add the devices, click ok to return to the General page.
Step 4: In the Source: Device: Database list box, select the name of the database which should be restored and click on “Ok”. Then your database will be restored.