How To Backup and Restore MSSQL on a Windows VPS or Dedicated Server

Database backups crucial for any application and files to safeguard against any eventuality that may lead to data loss.Microsoft has an inbuilt feature that is used to easily backup and restore data.

 

Overview

The Microsoft SQL Server Management Studio Express (SSMSE) is an open source graphical management tool that is used to manage, configure and administer your SQL server. This tutorial will cover how to back up and restore our SQL database. The backup is saved in the form of a .bak file either locally or externally depending on your choice of backup location.

Creating a backup

2012 MSSQL database is used for this demonstration. The backup file created is a .bak file extension. Download the SSMSE suitable for your system.

Open  MSSMS and Log in to your database using your Database username and password.

Expand databases and select the database to backup then right-click tasks then click back up.

A backup window appears. On the database option, make sure that it is the correct database that you want to backup. Select Destination (where backup file will be stored).

Then you are ready to choose the backup type.

Choose Backup Type; Full, Name of the backup file ;

For our backup, we will select full and specify the location where we want to store the backup.

On the backup database window, click on add to select the storage location and a name for the database backup.

Click on OK to finish the backup. A confirmation window will appear with a “completed successfully” message.

You have successfully created a backup of your MSSQL database.

Restoring the Backup

Open your Microsoft management studio and connect to your database. Then select databases and right click it. Click restore database.

A restore database window appears.Select the source as Devices. Browse to the location of the backup created above.

Click add, and navigate to the folder where you saved the backup.

Click OK when done. Specify the destination, and by default, it is going to keep the database name. If you leave it as it is, it is going to wipe the original database and restore it, so for this tutorial, the backup will be restored as database testDB2.

Change the data and log file for testDB2. So click on files and navigate to restore as. Change the name to testDB2.mdf. Do the same for Log file and name it testdb2.log

Click on OK to begin the restore.

You have successfully restored your backup to the database to TestDB2.

If you go to the management studio and refresh databases, you will see both databases there.

Conclusion

We have successfully restored a database thanks to the Microsoft SQL Server Management Studio Express. This tool combines a broad group of graphical tools to provide one of the most trusted backup software’s. Any developer of any skill level can use it as it is quite simple to use. You can also use some third-party apps, but this is the most comfortable method yet.

Check out the top 3 VPS services:

Was this article helpful?